是否有用于連接多行資料的MySQL方法？

我正在學習如何使用 MySQL 將我擁有的書籍的資訊資料庫放在一起。有時書籍可以由多個作者撰寫，成為多個系列的一部分，并按多種型別分類。據我了解，在關系資料庫中處理類陣列資料的最佳方法是使用單獨的表。

我的資料庫如下所示：

圖書

[ 流派 | 系列 | 作者 ] 三個單獨的表格。

[ 流派串列 | 系列串列 | 作者串列] 三個單獨的表格。一本書的每個型別/系列/作者都存盤在單獨的行中。

我正在構建一個用于從資料庫中檢索圖書資料的 API。用于檢索書籍的代碼如下：

const express = require('express');

const Router = express.Router();

const mysqlConnection = require('../connection');

Router.get('/all', (req, res) => {
  let results = {};

  mysqlConnection.query('SELECT * FROM book', (bookErr, bookRows, bookFields) => {
    if (bookErr) { console.log(bookErr); return; }

    results = bookRows;

    mysqlConnection.query('SELECT genre.name FROM book_genre JOIN genre ON genre.id = book_genre.genre_id WHERE book_genre.book_id = 1', (genreErr, genreRows, genreFields) => {
      if (genreErr) { console.log(genreErr); return; }

      results[0].genres = genreRows;

      mysqlConnection.query('SELECT author.name FROM book_author JOIN author ON author.id = book_author.author_id WHERE book_author.book_id = 1', (authorErr, authorRows, authorFields) => {
        if (authorErr) { console.log(authorErr); return; }
  
        results[0].authors = authorRows;

        mysqlConnection.query('SELECT series.title FROM book_series JOIN series ON series.id = book_series.series_id WHERE book_series.book_id = 1', (seriesErr, seriesRows, seriesFields) => {
          if (seriesErr) { console.log(seriesErr); return; }
    
          results[0].series = seriesRows;
    
          res.send(results);
        });
      });
    });
  });
});

module.exports = Router;

這段代碼給出了我想要的輸出：

[
    {"id":1,
    "title":"Harry Potter and the Sorcerer's Stone",
    "summary":"When mysterious letters start arriving on his doorstep, Harry Potter has never heard of Hogwarts School of Witchcraft and Wizardry.\n\nThey are swiftly confiscated by his aunt and uncle.\n\nThen, on Harrys eleventh birthday, a strange man bursts in with some important news: Harry Potter is a wizard and has been awarded a place to study at Hogwarts.\n\nAnd so the first of the Harry Potter adventures is set to begin.",
    "pages":223,
    "published_date":"1997-06-26T04:00:00.000Z",
    "ddc":"823.914",
    "age_rating":12,
    "reviews_rating":89,
    "genres":[
        {"name":"Fantasy"},
        {"name":"Fiction"}
    ],
    "authors":[
        {"name":"J.K. Rowling"}
    ],
    "series":[
        {"title":"Harry Potter"}
    ]
}
]

是否有一種簡化的方法可以檢索一本書及其型別、系列和作者？

我使用 GROUP_CONCAT 和建議的查詢將多行結果合并為一個：

SELECT book.*,
GROUP_CONCAT(author.name) as 'authors',
GROUP_CONCAT(series.title) as 'series',
GROUP_CONCAT(genre.name) as 'genres'
FROM book
LEFT OUTER JOIN book_author ON book.id=book_author.book_id
JOIN author ON book_author.author_id = author.id
LEFT OUTER JOIN book_series ON book.id=book_series.book_id
JOIN series ON book_series.series_id = series.id
LEFT OUTER JOIN book_genre ON book.id=book_genre.book_id
JOIN genre ON book_genre.genre_id = genre.id;

給定 3 個流派、1 個作者和 1 個系列......結果輸出是：

[
    {"id":1,
    "title":"Harry Potter and the Sorcerer's Stone",
    "summary":"When mysterious letters start arriving on his doorstep, Harry Potter has never heard of Hogwarts School of Witchcraft and Wizardry.\n\nThey are swiftly confiscated by his aunt and uncle.\n\nThen, on Harrys eleventh birthday, a strange man bursts in with some important news: Harry Potter is a wizard and has been awarded a place to study at Hogwarts.\n\nAnd so the first of the Harry Potter adventures is set to begin.",
    "pages":223,
    "published_date":"1997-06-26T04:00:00.000Z",
    "ddc":"823.914",
    "age_rating":12,
    "reviews_rating":89,
    "genres": "Young Adult,Fiction,Fantasy",
    "authors": "J.K. Rowling,J.K. Rowling,J.K. Rowling",
    "series": "Harry Potter,Harry Potter,Harry Potter"
}
]

我想我明白為什么它會重復結果。4 種型別會導致 1 個作者和 1 個系列重復 4 次。有沒有辦法阻止這種情況發生？

uj5u.com熱心網友回復：

使用 MySQL GROUP_CONCAT()函式及其DISTINCT屬性將從查詢中回傳單行，并將請求的列值連接在一起。

此答案回傳包含所有型別、作者和系列的單行，而不是多行冗余。

代碼解決方案：

const express = require('express');

const Router = express.Router();

const mysqlConnection = require('../connection');

Router.get('/all', (req, res) => {
  mysqlConnection.query(`
    SELECT book.*,
    GROUP_CONCAT(DISTINCT author.name) as 'authors',
    GROUP_CONCAT(DISTINCT series.title) as 'series',
    GROUP_CONCAT(DISTINCT genre.name) as 'genres'
    FROM book
    LEFT OUTER JOIN book_author ON book.id = book_author.book_id
      JOIN author ON book_author.author_id = author.id
    LEFT OUTER JOIN book_series ON book.id = book_series.book_id
      JOIN series ON book_series.series_id = series.id
    LEFT OUTER JOIN book_genre ON book.id = book_genre.book_id
      JOIN genre ON book_genre.genre_id = genre.id
    GROUP BY book.title
  `, (err, rows, fields) => {
    if (err) { console.log(err); return; }
    res.send(rows);
  });
});

module.exports = Router;

GROUP BY book.title 將回傳所有書籍，而不僅僅是最后一本。

uj5u.com熱心網友回復：

您可以將四個資料庫查詢合二為一：

SELECT book.id, ..., book.reviews_rating,
  group_concat(distinct genre.name) as genre_name,
  group_concat(distinct author.name) as author_name,
  group_concat(distinct series.title) as series_title
FROM book
LEFT OUTER JOIN (book_genre ON book.id = book_genre.book_id
JOIN genre ON genre.id = book_genre.genre_id)
LEFT OUTER JOIN (book_author ON book.id = book_author.book_id
JOIN author ON author.id = book_author.author_id)
LEFT OUTER JOIN (book_series ON book.id = book_series.book_id
JOIN series ON series.id = book_series.series_id)
GROUP BY book.id, ..., book.reviews_rating

如果沒有group_concatand GROUP BY，這將為您提供一個results包含兩行（由于兩種型別）和其他列中的冗余條目的表。如果這本書有兩個作者和三個系列，你會得到 2*2*3=12 行。將其轉換為您期望的 JSON 格式需要一些作業，但由于資料庫呼叫較少，因此總體上應該更快。

（左外連接確保即使一本書沒有流派、作者或系列，您也至少獲得一行，流派或作者姓名或系列標題為null。）

標籤：

上一篇：在nodejs和localhost上連接的Mongodb不斷重新加載

下一篇：從json獲取資料，其中JSON密鑰后綴隨著i增加