生成列_我的学习教程

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']
//AS (expr) 表示生成列，并定义了用于计算列值的表达式。GENERATED ALWAYS使生成列的性质更明确

//添加生成的列
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

//修改生成的列的数据类型和表达式
ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;

//没有其他列引用生成的列，则可以重命名或删除它们
ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;
ALTER TABLE t1 DROP COLUMN c3;

//虚拟生成的列不能更改为存储的生成的列，反之亦然。要解决此问题，请删除该列，然后添加新定义。
ALTER TABLE t1 DROP COLUMN c2;
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

//可以将非生成的列更改为存储的生成列，但不能更改为虚拟生成的列。
ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;

//可以将已存储但不是虚拟生成的列更改为非生成的列。存储的生成值成为非生成列的值。
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
ALTER TABLE t1 MODIFY COLUMN c2 INT;

生成列的存储方式

VIRTUAL(默认值)：列值不存储，而是在读取行时计算紧接在任何BEFORE触发器之后。虚拟列不占用任何存储空间。InnoDB支持虚拟列上的二级索引
STORED：插入或更新行时，将评估并存储列值。存储的列确实需要存储空间，并且可以建立索引。

生成的列表达式必须遵守以下规则。否则会发生错误。

允许使用文字、确定性内置函数和运算符。
不允许使用存储的函数和用户定义的函数。
不允许存储过程和函数参数。
不允许使用变量（系统变量，用户定义的变量和存储的程序局部变量）。
不允许子查询。
生成的列定义可以引用其他生成的列，但只能引用表定义中较早出现的列。生成的列定义可以引用表中的任何基础（未生成）列，无论其定义是更早还是更晚。
不能在生成的列定义中使用AUTO_INCREMENT属性。
生成的列定义不能引用一个AUTO_INCREMENT列
生成的列可以进行分区。
存储生成的列的基列上的外键约束不能使用CASCADE、SET NULL或SET DEFAULT。
外键约束不能引用虚拟生成的列
触发器不能使用或用于引用生成的列
对于INSERT， REPLACE和 UPDATE，如果将生成的列显式插入，替换或显式更新，则唯一允许的值为DEFAULT。
视图中生成的列被认为是可更新的，因为可以对其进行分配。但是，如果显式更新了此列，则唯一允许的值为 DEFAULT。

用法

可以用作简化和统一查询的方法。可以将一个复杂的条件定义为一个生成的列，并从表上的多个查询中引用该条件，以确保它们全部使用完全相同的条件。
存储的生成的列可用作复杂条件的物化缓存，用于处理动态计算成本较高的复杂情况
生成的列可以定义索引。这对于处理无法直接索引的类型的列（例如，JSON列）很有用；对于存储的生成列，此方法的缺点是值存储两次。一次作为生成的列的值，一次作为索引
为生成的列建立索引，则优化器将识别与列定义匹配的查询表达式，并在查询执行期间适当地使用该列中的索引，即使查询没有直接按名称引用该列

生成的列定义二级索引

虚拟列上定义的二级索引也称之为“虚拟索引”
可以在一个或多个虚拟列上或在虚拟列和常规列或存储的生成列的组合上创建二级索引,索引可以定义为UNIQUE
在虚拟生成的列上创建二级索引时，生成的列值将物化到索引的记录中。如果索引是覆盖索引，则从索引结构中的物化值检索生成的列值，而不是“即时”计算生成的列值。
在虚拟列上使用二级索引时，需要考虑额外的写成本，这是由于在INSERT和 UPDATE操作期间在二级索引记录中物化虚拟列值时所执行的计算。即使有额外的写开销，虚拟列上的二级索引可能比生成的存储列更好，这些存储列被物化在聚集索引中，导致需要更多磁盘空间和内存的更大的表。如果在虚拟列上没有定义二级索引，则会有额外的读取成本，因为每次检查列的行时都必须计算虚拟列的值。

生成的列为JSON类型列提供索引

-- 不能直接对JSON类型列进行索引，可以定义一个生成的列提取应建立索引的信息，然后在生成的列上创建索引
mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
1 row in set (0.00 sec)

生成列

生成列的存储方式

生成的列表达式必须遵守以下规则。否则会发生错误。

用法

生成的列定义二级索引

生成的列为JSON类型列提供索引

CATALOG

标签