MySQL一对多查询多条合并为数组
在MySQL数据库中，有时我们需要一对多查询多条数据并将其合并为数组。这种情况经常出现在数据分析、报表生成以及后端接口开发等场景中。本文将介绍如何使用MySQL进行一对多查询，并将结果合并为数组。
什么是一对多查询
一对多查询是指在数据库中有两个表，这两个表之间有关联关系，一个表中的一条数据对应另一个表中的多条数据。例如，一个订单对应多个商品，或者一个部门对应多个员工等。
示例数据库
为了方便演示，我们创建一个示例数据库。假设我们有两个表：orders和products。orders表记录了订单的信息，products表记录了订单中的商品信息。
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_number VARCHAR(20) NOT NULL
);
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id)
);
在orders表中，我们有两个订单记录：
INSERT INTO orders (order_number) VALUES ('20210101');
INSERT INTO orders (order_number) VALUES ('20210102');
在products表中，我们有四个商品记录，分别属于这两个订单：
INSERT INTO products (order_id, name, price) VALUES (1, 'Product A', 10.99);
INSERT INTO products (order_id, name, price) VALUES (1, 'Product B', 20.99);
INSERT INTO products (order_id, name, price) VALUES (2, 'Product C', 15.99);
INSERT INTO products (order_id, name, price) VALUES (2, 'Product D', 25.99);
使用GROUP_CONCAT函数
MySQL提供了GROUP_CONCAT函数，可以将多行数据合并为一个字符串。我们可以利用这个函数实现一对多查询并将结果合并为数组。
SELECT
orders.id,
orders.order_number,
GROUP_CONCAT(products.name ORDER BY products.id SEPARATOR '|') AS product_names,
GROUP_CONCAT(products.price ORDER BY products.id SEPARATOR '|') AS product_prices
FROM orders
JOIN products ON orders.id = products.order_id
GROUP BY orders.id;
在上面的查询中，我们使用JOIN语句将orders表和products表关联起来。然后使用GROUP BY按订单分组。在SELECT语句中，使用GROUP_CONCAT函数分别将商品名称和价格合并为字符串，并使用'|'作为分隔符。
执行上述查询，将得到以下结果：
| id | order_number | product_names | product_prices |
|---|---|---|---|
| 1 | 20210101 | Product A | Product B |
| 2 | 20210102 | Product C | Product D |
将结果合并为数组
现在我们已经得到了一对多查询的结果，接下来需要将结果合并为数组。在MySQL中，我们可以使用脚本语言（如PHP、Python等）来处理这个任务。下面是一个使用PHP处理的示例代码：
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'test';
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$query = "
SELECT
orders.id,
orders.order_number,
GROUP_CONCAT(products.name ORDER BY products.id SEPARATOR '|') AS product_names,
GROUP_CONCAT(products.price ORDER BY products.id SEPARATOR '|') AS product_prices
FROM orders
JOIN products ON orders.id = products.order_id
GROUP BY orders.id;
";
$result = $conn->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$order = [
'id' => $row['id'],
'order_number' => $row['order_number'],
'product_names' => explode('|', $row['product_names']),
'product_prices' => explode('|', $row['product_prices'])
];
print_r($order);
}
} else {
echo "No orders found";
}
$conn->close();
?>
