mysql - 如何创建具有一列作为其他列总和的表？

Question

Create Table Billing3 
(
billingId int primary key,
FoodCharge float DEFAULT 0,
DoctorCharge float DEFAULT 0,
TestCharge float DEFAULT 0,
OperationCharge float DEFAULT 0,
RoomCharge float DEFAULT 0,
Total float DEFAULT (FoodCharge + DoctorCharge + TestCharge + OperationCharge + RoomCharge)
)

Answer 1

或者，您可以设置一个 MySQL insert trigger .但通常，您希望在节省存储空间的同时在查询中保留计算并避免对编程对象进行维护。另外，如果其中一项费用更新了值，那么您将需要一个更新触发器。

USE `databasename`;
DELIMITER 
$$
CREATE TRIGGER `TotalCalculation` 
BEFORE INSERT 
ON `Billing3` FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN
SET NEW.Total = NEW.FoodCharge + NEW.DoctorCharge + NEW.TestCharge +
                NEW.OperationCharge + NEW.RoomCharge;
END
$$