mysql 数据库逗号字段分割

原创

mob64ca12f86e32 2024-04-16 04:27:34 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12f86e32的原创作品，请联系作者获取转载授权，否则将追究法律责任

实现mysql数据库逗号字段分割

1. 整个流程

下面是实现mysql数据库逗号字段分割的整个流程：

erDiagram
    CUSTOMER ||--o| ORDERS : has
    ORDERS ||--|{ ORDER_DETAILS : contains
    ORDER_DETAILS ||--|{ PRODUCT : contains

2. 每一步具体操作

步骤1：创建数据库表

首先，我们需要创建数据库表，包括客户表（CUSTOMER）、订单表（ORDERS）、订单详情表（ORDER_DETAILS）和产品表（PRODUCT）。以下是创建表的代码：

CREATE TABLE CUSTOMER (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE ORDERS (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES CUSTOMER(customer_id)
);

CREATE TABLE ORDER_DETAILS (
    order_detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES ORDERS(order_id),
    FOREIGN KEY (product_id) REFERENCES PRODUCT(product_id)
);

CREATE TABLE PRODUCT (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

步骤2：插入数据

接下来，我们需要向这些表中插入一些数据，以便进行后续操作。以下是插入数据的代码：

INSERT INTO CUSTOMER (customer_id, customer_name) VALUES (1, 'Alice');
INSERT INTO CUSTOMER (customer_id, customer_name) VALUES (2, 'Bob');

INSERT INTO ORDERS (order_id, customer_id, order_date) VALUES (1, 1, '2022-01-01');
INSERT INTO ORDERS (order_id, customer_id, order_date) VALUES (2, 2, '2022-01-02');

INSERT INTO PRODUCT (product_id, product_name, price) VALUES (1, 'Apple', 1.99);
INSERT INTO PRODUCT (product_id, product_name, price) VALUES (2, 'Banana', 0.99);

INSERT INTO ORDER_DETAILS (order_detail_id, order_id, product_id, quantity) VALUES (1, 1, 1, 2);
INSERT INTO ORDER_DETAILS (order_detail_id, order_id, product_id, quantity) VALUES (2, 1, 2, 3);
INSERT INTO ORDER_DETAILS (order_detail_id, order_id, product_id, quantity) VALUES (3, 2, 1, 1);

步骤3：查询逗号字段分割结果

最后，我们可以使用如下代码查询每个订单的产品信息，并将产品名用逗号分隔展示：

SELECT o.order_id, c.customer_name, GROUP_CONCAT(p.product_name SEPARATOR ', ') AS products
FROM ORDERS o
JOIN CUSTOMER c ON o.customer_id = c.customer_id
JOIN ORDER_DETAILS od ON o.order_id = od.order_id
JOIN PRODUCT p ON od.product_id = p.product_id
GROUP BY o.order_id;