MYSQL设计优化生成列

举报
Ustinian_2022 发表于 2022/07/27 18:00:38 2022/07/27
【摘要】 19.2 生成列MySQL中生成列的值是根据数据表中定义列时指定的表达式计算得出的,主要包含两种类型:VIRSUAL生成列和SORTED生成列,其中VIRSUAL生成列是从数据表中查询记录时,计算该列的值;SORTED生成列是向数据表中写入记录时,计算该列的值并将计算的结果数据作为常规列存储在数据表中。通常,使用的比较多的是VIRSUAL生成列,原因是VIRSUAL生成列不占用存储空间。19...

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已经被成功删除。

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200