mysql 重构代码以避免多个SQL查询

ckocjqey  于 2023-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(143)

我正在用Node.js（使用express）和MySQL（mysql2包）创建一个简单的电子商务数据库。
为了得到一个产品，我需要从3个不同的表中获取数据：products、product_images（具有指向产品ID的外键）和reviews（也具有指向产品ID的外键）。
我的3个问题：

const productQuery = `
    SELECT p.name, p.reference AS ref, p.price, p.brand, p.stock_qty, p.product_subcategory_id AS subcategoryId, p.product_promotion_id AS promotionId
    FROM products p
    WHERE id = ?`;

const imagesQuery = `
    SELECT url
    FROM product_images
    WHERE product_id = ?`

const reviewsQuery = `
  SELECT r.content AS reviewContent, r.rate AS reviewRate, r.created_at AS reviewDate, r.reviews_user_id AS reviewUserId
  FROM reviews r
  WHERE reviews_product_id = ?
  ORDER BY reviewDate DESC`;

const [product] = await sql.query(productQuery, id);
const [images] = await sql.query(imagesQuery, id);
const [reviews] = await sql.query(reviewsQuery, id);

return { product, images, reviews };

结果（我的种子中还没有图像，所以数组为空是正常的）：

{
    "product": [
        {
            "name": "FAMILY CARE TRIPLE",
            "ref": "2c85da01",
            "price": "246.21",
            "brand": "Wordpedia",
            "stock_qty": "61",
            "subcategoryId": 21,
            "promotionId": null
        }
    ],
    "images": [],
    "reviews": [
        {
            "reviewContent": "Fusce posuere felis sed lacus. Morbi sem mauris, laoreet ut, rhoncus aliquet, pulvinar sed, nisl. Nunc rhoncus dui vel sem.",
            "reviewRate": 5,
            "reviewDate": "2023-03-04T11:12:13.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Proin eu mi. Nulla ac enim. In tempor, turpis nec euismod scelerisque, quam turpis adipiscing lorem, vitae mattis nibh ligula nec sem.",
            "reviewRate": 4,
            "reviewDate": "2022-08-28T04:39:37.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Praesent blandit. Nam nulla. Integer pede justo, lacinia eget, tincidunt eget, tempus vel, pede.",
            "reviewRate": 4,
            "reviewDate": "2022-08-19T17:05:23.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Praesent id massa id nisl venenatis lacinia. Aenean sit amet justo. Morbi ut odio.",
            "reviewRate": 1,
            "reviewDate": "2022-08-19T16:05:44.000Z",
            "reviewUserId": 1
        },
        {
            "reviewContent": "Aenean lectus. Pellentesque eget nunc. Donec quis orci eget orci vehicula condimentum.",
            "reviewRate": 1,
            "reviewDate": "2022-06-18T00:50:21.000Z",
            "reviewUserId": 2
        }
    ]
}

结果是我想要的，但我不认为对数据库进行3次查询是优化的。
起初我尝试了这个查询（只是为了获得产品信息和相关评论，但不是图片）

SELECT 
    p.name, p.reference AS ref, p.price, p.brand, p.stock_qty, 
    p.product_subcategory_id AS subcategoryId, 
    p.product_promotion_id AS promotionId, r.content AS reviewContent, 
    r.rate AS reviewRate, r.created_at AS reviewDate, 
    r.reviews_user_id AS reviewUserId
FROM 
    products p
JOIN
    reviews r ON p.id = r.reviews_product_id
WHERE 
    p.id = ?

结果：

[
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Proin eu mi. Nulla ac enim. In tempor, turpis nec euismod scelerisque, quam turpis adipiscing lorem, vitae mattis nibh ligula nec sem.",
        "reviewRate": 4,
        "reviewDate": "2022-08-28T04:39:37.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Aenean lectus. Pellentesque eget nunc. Donec quis orci eget orci vehicula condimentum.",
        "reviewRate": 1,
        "reviewDate": "2022-06-18T00:50:21.000Z",
        "reviewUserId": 2
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Fusce posuere felis sed lacus. Morbi sem mauris, laoreet ut, rhoncus aliquet, pulvinar sed, nisl. Nunc rhoncus dui vel sem.",
        "reviewRate": 5,
        "reviewDate": "2023-03-04T11:12:13.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Praesent blandit. Nam nulla. Integer pede justo, lacinia eget, tincidunt eget, tempus vel, pede.",
        "reviewRate": 4,
        "reviewDate": "2022-08-19T17:05:23.000Z",
        "reviewUserId": 1
    },
    {
        "name": "FAMILY CARE TRIPLE",
        "ref": "2c85da01",
        "price": "246.21",
        "brand": "Wordpedia",
        "stock_qty": "61",
        "subcategoryId": 21,
        "promotionId": null,
        "reviewContent": "Praesent id massa id nisl venenatis lacinia. Aenean sit amet justo. Morbi ut odio.",
        "reviewRate": 1,
        "reviewDate": "2022-08-19T16:05:44.000Z",
        "reviewUserId": 1
    }
]

我的问题是，即使结果在技术上是正确的（我得到了我要求的所有数据），使用它（与我当前的代码相比）也很难，我猜它不是很有效，因为它有一些数据多次（关于产品的相同数据在每次不同的审查中重复）。
所以我的问题是有没有一种方法可以用一个查询得到一个与实际结果相同的结果？
如果不是，我应该继续使用我的实际代码，还是应该使用我的第一个代码？

mysql

1条答案

按热度按时间
bxfogqkk

bxfogqkk1#

我将继续使用三个查询。如果您可以用一个查询来完成这项工作，那么查询将非常复杂，并且很难维护。此外，在获取结果后，您还必须编写大量代码，以便将结果分离到所需的结构中。
在上面的评论中，Shadow提到了一个可以生成JSON结构结果的SQL查询。这看起来像这样（例如目的，不打算作为您的案例的解决方案）：

SELECT JSON_OBJECT(
  'product', p.products,
  'images', i.images,
  'reviews', r.reviews
) AS _result
FROM (
  SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
      'name', name,
      'ref', reference,
      'price', price,
      'brand', brand,
      'stock_qty', stock_qty,
      'subcategoryId', product_subcategory_id,
      'promotionId', 'product_promotion_id
    )
  ) AS products
  FROM products
  WHERE id = ?
) AS p
CROSS JOIN (
  ...similar for product_images table...
) AS i
CROSS JOIN (
  ...similar for reviews table...
) AS r;

这更难编写，更难向另一个开发人员解释，如果将来需要添加新元素，也很难添加新元素。
有时候，更简单的方法对代码的生命周期更好。
这让我想起了Brian Kernighan中的一句经典名言：
“每个人都知道，调试是写程序的两倍。因此，如果您在编写它时尽可能聪明，那么您将如何调试它呢？“
另一条评论：你说你猜三个查询是没有效率的。但是这种模糊的猜测并不足以使它成为一个单一的、更复杂的查询。您应该有一个更具体的原因来解释为什么原始实现不够好，您可以通过一些性能度量来支持这个原因。

赞(0）分享  回复(0）举报  2023-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com