最近看到资料,对建表有了进一步认识,原来建表还可以这样建立。写下来，做个记录。
CREATE TABLE payroll
( employee_id INT NOT NULL
, base_salary DOUBLE
, bonus DOUBLE
, commission DOUBLE
, total_pay DOUBLE GENERATED ALWAYS AS
(base_salary*(1+bonus) + commission)
)INSERT INTO payroll VALUES (1, 100, 0.1, 20, DEFAULT); 或者
INSERT INTO payroll (employee_id, base_salary, bonus, commission) VALUES (1, 100, 0.1, 20); 这样就是建表的时候加入了计算了。省好多事情。很方便。
复杂的如下：
CREATE TABLE payroll2 ( employee_id INT NOT NULL , employee_type CHAR(1) NOT NULL , base_salary DOUBLE , bonus DOUBLE , commission DOUBLE , total_pay DOUBLE GENERATED ALWAYS AS ( CASE employee_type WHEN 'B' THEN base_salary*(1+bonus) WHEN 'C' THEN (base_salary + commission) ELSE 0 END ) )