MySQL如何设计支持单交易多商品的交易表？

不建议在tbl_transaction的product字段中存储多个id_product值，这种做法会带来一系列问题：

• 违反数据库设计的第三范式，数据冗余且维护困难
• 查询特定商品的交易记录时，只能用模糊匹配或FIND_IN_SET函数，效率极低，数据量增大后问题更明显
• 无法直接记录同一件商品的购买数量，也难以统计商品的销售数据
• 若商品ID发生变更，需要批量更新所有包含该ID的交易记录，极易出错

正确的设计方案：新增交易明细表 #

通过创建交易明细表（关联交易表和商品表）来实现多商品交易的存储，这是电商系统的标准设计模式。

调整后的表结构：

1. 交易表（tbl_transaction）：保留交易主体信息，移除原product字段，同时将金额字段改为数值类型（避免字符串存储金额带来的计算问题）：

CREATE TABLE tbl_transaction (
    id_transaction INT AUTO_INCREMENT PRIMARY KEY,
    id_customer INT NOT NULL,
    total_transaction DECIMAL(10,2) NOT NULL,
    transaction_time DATETIME DEFAULT CURRENT_TIMESTAMP -- 新增交易时间，便于后续统计
);

2. 商品表（tbl_product）：同样将价格字段改为数值类型：

CREATE TABLE tbl_product (
    id_product INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(40) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

3. 交易明细表（tbl_transaction_item）：记录每笔交易对应的商品明细，支持多商品、多数量：

CREATE TABLE tbl_transaction_item (
    id_item INT AUTO_INCREMENT PRIMARY KEY,
    id_transaction INT NOT NULL,
    id_product INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1, -- 购买数量，默认1件
    item_subtotal DECIMAL(10,2) NOT NULL, -- 该商品的小计，避免商品价格变动影响历史交易数据
    FOREIGN KEY (id_transaction) REFERENCES tbl_transaction(id_transaction) ON DELETE CASCADE,
    FOREIGN KEY (id_product) REFERENCES tbl_product(id_product)
);

数据插入示例：

假设用户ID为1的顾客购买了2件ID为1的商品（单价100元）和1件ID为2的商品（单价200元），总金额400元：

1. 先插入交易主记录：

INSERT INTO tbl_transaction (id_customer, total_transaction) VALUES (1, 400.00);

假设生成的id_transaction为1。

2. 再插入交易明细记录：

INSERT INTO tbl_transaction_item (id_transaction, id_product, quantity, item_subtotal) VALUES
(1, 1, 2, 200.00),
(1, 2, 1, 200.00);

优势：

• 符合数据库范式，数据冗余少
• 支持灵活查询：比如查询某用户的所有交易及对应商品、统计某商品的总销量等，通过JOIN语句即可高效实现
• 便于维护：修改交易中的商品、调整数量等操作都能精准执行，不会影响其他数据

若坚持存储多个ID（不推荐） #

如果非要在一个字段中存储多个商品ID，可以用VARCHAR或TEXT类型，用逗号分隔（例如"1,2,3"），但需注意：

• 查询时需使用FIND_IN_SET(id_product, product)，性能很差
• 无法存储购买数量等附加信息
• 数据一致性难以保障，比如删除商品后，交易记录中的无效ID无法自动清理

内容的提问来源于stack exchange，提问作者A fish