MYSQL设计优化生成列

19.2　生成列

MySQL中生成列的值是根据数据表中定义列时指定的表达式计算得出的，主要包含两种类型：VIRSUAL生成列和SORTED生成列，其中VIRSUAL生成列是从数据表中查询记录时，计算该列的值；SORTED生成列是向数据表中写入记录时，计算该列的值并将计算的结果数据作为常规列存储在数据表中。

通常，使用的比较多的是VIRSUAL生成列，原因是VIRSUAL生成列不占用存储空间。

19.2.1　创建表时指定生成列

例如，创建数据表t_genearted_column，数据表中包含DOUBLE类型的字段a、b和c，其中c字段是由a字段和b字段计算得出的。

mysql> CREATE TABLE t_genearted_column(
    -> a DOUBLE,
    -> b DOUBLE,
    -> c DOUBLE AS (a * a + b * b)
    -> );
Query OK, 0 rows affected (0.01 sec)

向t_genearted_column数据表中插入数据。

mysql> INSERT INTO t_genearted_column
    -> (a, b)
    -> VALUES
    -> (1, 1),
    -> (2, 2),
    -> (3, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查询t_genearted_column数据表中的数据。

mysql> SELECT * FROM t_genearted_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    8 |
|    3 |    3 |   18 |
+------+------+------+
3 rows in set (0.00 sec)

结果显示，在向t_genearted_column数据表中插入数据时，并没有向c字段中插入数据，c字段的值是由a字段的值和b字段的值计算得出的。

如果在向t_genearted_column数据表插入数据时包含c字段，则向c字段插入数据时，必须使用DEFAULT，否则MySQL会报错。

mysql> INSERT INTO t_genearted_column
    -> (a, b, c)
    -> VALUES
    -> (4, 4, 32);
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't_genearted_column' is not 
allowed.

MySQL报错，报错信息为不能为生成的列手动赋值。

使用DEFAULT关键字代替具体的值。

mysql> INSERT INTO t_genearted_column
    -> (a, b, c)
    -> VALUES
    -> (4, 4, DEFAULT);
Query OK, 1 row affected (0.00 sec)

SQL语句执行成功，查询t_genearted_column数据表中的数据。

mysql> SELECT * FROM t_genearted_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
|    2 |    2 |    8 |
|    3 |    3 |   18 |
|    4 |    4 |   32 |
+------+------+------+
4 rows in set (0.00 sec)

已经成功为c字段赋值。

也可以在创建表时明确指定VIRSUAL生成列。

mysql> CREATE TABLE t_column_virsual (
    -> a DOUBLE,
    -> b DOUBLE,
    -> c DOUBLE GENERATED ALWAYS AS (a + b) VIRTUAL);
Query OK, 0 rows affected (0.02 sec)

向t_column_virsual数据表中插入数据并查询结果。

mysql> INSERT INTO t_column_virsual
    -> (a, b)
    -> VALUES
    -> (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t_column_virsual;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
+------+------+------+
1 row in set (0.00 sec)

19.2.2　为已有表添加生成列

可以使用ALTER TABLE ADD COLUMN语句为已有的数据表添加生成列。例如，创建数据表t_add_column。

mysql> CREATE TABLE t_add_column(
    -> a DOUBLE,
    -> b DOUBLE
    -> );
Query OK, 0 rows affected (0.01 sec)

向数据表中插入数据。

mysql> INSERT INTO t_add_column
    -> (a, b)
    -> VALUES
    -> (2, 2);
Query OK, 1 row affected (0.01 sec)

为t_add_column数据表添加生成列。

mysql> ALTER TABLE t_add_column ADD COLUMN c  DOUBLE GENERATED ALWAYS AS(a * a + b * b) STORED;
Query OK, 1 row affected (0.11 sec)
Records: 1  Duplicates: 0  Warnings: 0

SQL语句执行成功，查询t_add_column数据表中的数据。

mysql> SELECT * FROM t_add_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    8 |
+------+------+------+
1 row in set (0.00 sec)

结果显示，当数据表中存在数据时，为数据表添加生成列，会自动根据已有的数据计算该列的值，并存储到该列中。

19.2.3　修改已有的生成列

例如，修改t_add_column数据表的生成列c，将其计算规则修改为a * b。

mysql> ALTER TABLE t_add_column
    -> MODIFY COLUMN c DOUBLE
    -> GENERATED ALWAYS AS (a * b)
    -> STORED;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

查询t_add_column数据表中的数据。

mysql> SELECT * FROM t_add_column;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    2 |    2 |    4 |
|    1 |    1 |    1 |
+------+------+------+
2 rows in set (0.00 sec)

c列的值此时已经被修改为a列的值乘以b列的值的结果数据。

19.2.4　删除生成列

删除生成列可以使用ALTER TABLE DROP COLUMN语句实现。例如，删除t_add_column数据表中的生成列c。

mysql> ALTER TABLE t_add_column DROP COLUMN c;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

SQL语句执行成功，再次查看t_add_column数据表中的数据。

mysql> SELECT * FROM t_add_column;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
|    1 |    1 |
+------+------+
2 rows in set (0.00 sec)

结果显示，生成列c已经被成功删除。