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();
?>