生成列(Generated Column)
什么叫计算列呢,简单来说就是某一列的值是通过别的列计算得来,例如a列值为1,b列值为2,c列不需要操作手动插入,定义为a + b的结果为c的值,那么c就是计算列,是通过别的列计算得来,下面我们进行例子说明:
在MySQL 5.7之前的版本中,要实现这样的效果要利用触发器来完成,以下是以前的实现过程:
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec) |
创建一个插入的触发器:
1 2 |
mysql> create trigger insert_tb1 before insert on tb1 for each row set new.c3=new.c1+new.c2; Query OK, 0 rows affected (0.01 sec) |
我们插入数据:
1 2 3 4 5 6 7 8 9 10 |
mysql> insert into tb1(c1,c2) values (1,2); Query OK, 1 row affected (0.00 sec) mysql> select * from tb1; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | 2 | 3 | +----+------+------+------+ 1 row in set (0.00 sec) |
但如果我们更新了c1或者c2的值,c3是不会变动的,所以我们要加一个update的触发器:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> select * from tb1; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | 2 | 3 | +----+------+------+------+ 1 row in set (0.00 sec) mysql> create trigger update_tb1 before update on tb1 for each row set new.c3=new.c1+new.c2; Query OK, 0 rows affected (0.01 sec) mysql> update tb1 set c1 = 3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb1; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 3 | 2 | 5 | +----+------+------+------+ 1 row in set (0.00 sec) |
可以看到,如果想实现计算列,那么就要借助触发器或者视图来实现,但在生产环境中,是不太建议使用触发器或者视图的。
下面用MySQL 5.7来实现就变得很简单了。
MySQL 5.7 生成列实现
在 CREAE TABLE 和 ALTER TABLE 中都支持增加计算列的方式:
1 2 3 |
col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [NOT NULL | NULL] [[PRIMARY] KEY] |
下面我们创建测试表tb2:
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show create table tb2\G *************************** 1. row *************************** Table: tb2 Create Table: CREATE TABLE `tb2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) GENERATED ALWAYS AS ((`c1` + `c2`)) VIRTUAL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) |
插入数据看效果:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from tb2; Empty set (0.00 sec) mysql> insert into tb2(c1,c2) values (1,2); Query OK, 1 row affected (0.00 sec) mysql> select * from tb2; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 1 | 2 | 3 | +----+------+------+------+ 1 row in set (0.00 sec) |
更新数据看效果:
1 2 3 4 5 6 7 8 9 10 11 |
mysql> update tb2 set c1=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb2; +----+------+------+------+ | id | c1 | c2 | c3 | +----+------+------+------+ | 1 | 10 | 2 | 12 | +----+------+------+------+ 1 row in set (0.00 sec) |
可以看到相当的方便,MySQL 5.7的很多新特性都给我们带来了便利和惊喜^.^。
在MySQL 5.7中,支持两种generated column,即virtual generated column和stored generated column,前者只将generated column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将generated column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与virtual column相比并没有优势。因此,在不指定generated column的类型时,默认是virtual column。
如果读者觉得generate column提供的功能,也可以在用户代码里面实现,并没有什么了不起的地方,那么,或许还有一个功能能够吸引挑剔的你,那就是为generate column创建索引。在这个例子中,如果我们需要根据面积创建索引以加快查询,就无法在用户代码里面实现,使用generate column就变得非常简单:
1 |
mysql> alter table tb2 add index ix_area(area); |
未完。