Mysql8.0 计算列数据

在MySQL 8.0中，“CREATE TABLE”语句和“ALTER TABLE”语句都支持增加计算列。

mysql> create table tb_student(
    -> sid INT,
    -> sname VARCHAR(20),
    -> chinese INT,
    -> math INT,
    -> english INT,
    -> total INT GENERATED ALWAYS AS (chinese+math+english) VIRTUAL
    -> );
Query OK, 0 rows affected (2.68 sec)

mysql> desc tb_student;
+---------+-------------+------+-----+---------+-------------------+
| Field   | Type        | Null | Key | Default | Extra             |
+---------+-------------+------+-----+---------+-------------------+
| sid     | int         | YES  |     | NULL    |                   |
| sname   | varchar(20) | YES  |     | NULL    |                   |
| chinese | int         | YES  |     | NULL    |                   |
| math    | int         | YES  |     | NULL    |                   |
| english | int         | YES  |     | NULL    |                   |
| total   | int         | YES  |     | NULL    | VIRTUAL GENERATED |
+---------+-------------+------+-----+---------+-------------------+
6 rows in set (0.00 sec)

“total”列的数据由“（chinese+math+english）”计算所得。

插入一些测试数据：

mysql> INSERT INTO tb_student(sid, sname, chinese, math, english) VALUES(1, '张三', 90, 90, 90),
    -> (2, '李四',80, 100, 70), (3, '王五', 60, 60, 70);
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tb_student;
+------+-------+---------+------+---------+-------+
| sid  | sname | chinese | math | english | total |
+------+-------+---------+------+---------+-------+
|    1 | 张三  |      90 |   90 |      90 |   270 |
|    2 | 李四  |      80 |  100 |      70 |   250 |
|    3 | 王五  |      60 |   60 |      70 |   190 |
+------+-------+---------+------+---------+-------+
3 rows in set (0.00 sec)

从结果中可以看出，“total”列的数据由“（chinese+math+english）”计算所得。

更新chinese，math, english的值，total都会自动重新计算。

如果更新或者修改total的值，会返回错误。

mysql> INSERT INTO tb_student VALUES(4,'赵六',100,100,100,200);
ERROR 3105 (HY000): The value specified for generated column 'total' in table 'tb_student' is not allowed.