php - MySQL插入数据时自动更新字段

Question

我正在制作一个网络应用程序，让客户订购任何商品。为此，我制作了一个包含下表的 MySQL 数据库:

• 客户
• 订单
• 订单商品
• 产品

在 customers 表中是有关此人的所有信息，例如:

• 客户ID，用于主键和自增(id)
• 名字 (first_name)
• 姓氏(last_name)
• 电子邮件地址 (email_address)
• 客户信息 (customer_info)

示例:

在 orders 表中是关于它的所有具体信息，例如:

• 订单ID，用于主键和自增(id)
• 它订购了哪个客户，与 customers 表 (customer_id) 中的 id 字段相关联
• 订单信息(order_info)
• 订单需要去的地点(location)
• 客户必须支付的总价 (total_price)
• 订单创建(created)的时间

示例:

在 orders-items 表中是每个客户订购的所有商品，这是由上表中的 order-id 链接的。

• ID，用于主键和自增，不用于任何关系(id)
• 订单 ID，用于哪个产品对应哪个订单。这与 orders 表 (order_id)
中的 id 字段相关联
• 产品 ID，用于他们订购的产品，它与 products 表中的 id 字段相关联。 (product_id)
• 他们订购该产品的数量(数量)

示例:

在products表中是关于产品的所有信息:

• ID，用于主键和自动递增，这与 order_items 表 (id) 中的 product_id 字段相关联
• 产品名称(名称)
• 产品描述(description)
• 产品的价格(价格)

示例:

问题:

我有这个查询:

SELECT `orders-items`.`order_id` , SUM(`orders-items`.`quantity`* `products`.`price`) total
FROM  `orders-items` 
INNER JOIN  `Products` ON  `orders-items`.`products_id` =  `products`.`id` 

它向我显示了每个 order_id 必须支付的所有总价的列表。

但是我该怎么做才能使每个 order_id 必须支付的 total_price 值自动插入到 orders 表中将产品插入我的 orders-list 表时，右侧 order_id 的 total_price 字段？

还是不跟踪 客户 必须支付的 total_prices 是否更好？

Answer 1

有几件事需要考虑。

为自己设置一个total_price 是多余的。您可以随时通过汇总此订单商品的价格来了解此总数。出于性能原因使用它可能会很有趣，但这对您的场景真的有必要吗？它很少是。

另一方面，为每个 order_item 设置一个 price 会很有用。原因是这些产品的价格将来可能会发生变化，而您不想丢失在特定销售时的销售量信息。

在任何情况下，您都可以使用如下触发器更新您的total_price:

DELIMITER $$

CREATE TRIGGER order_items_insert AFTER INSERT ON `orders-items` FOR EACH ROW
BEGIN
    UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$

CREATE TRIGGER order_items_update AFTER UPDATE ON `orders-items` FOR EACH ROW
BEGIN
    UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = new.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$

CREATE TRIGGER order_items_delete AFTER DELETE ON `orders-items` FOR EACH ROW
BEGIN
    UPDATE orders o INNER JOIN (SELECT i.order_id id, SUM(i.quantity * p.price) total_price FROM `orders-items` i INNER JOIN products p ON p.id = i.products_id AND i.order_id = old.order_id) t ON t.id = o.id SET o.total_price = t.total_price;
END$$

DELIMITER ;