MySQL之虚拟列
2021-10-25 17:46:14
从MySQL 5.7开始，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column;
CREATE TABLE t1 (c1 INT);
ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
1、主键索引不能包含virtual generated column 如：
mysql> create table t(a int, b int , c int as (a / b), primary key(c));
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.
2、Virtual Generated Column不能作为外键
3、不能使用非确定函数，如：
mysql> alter table a ADD p3 DATE GENERATED ALWAYS AS (curtime()) virtual;
ERROR 3763 (HY000): Expression of generated column 'p3' contains a disallowed function: curtime.
4、无法删除源列，如：
mysql> alter table t100w drop k1;
ERROR 3108 (HY000): Column 'k1' has a generated column dependency.
5、非法数据，如：
mysql> create table t( x int, y int, z int as( x / 0));
Query OK, 0 rows affected (0.22 sec)
mysql> insert into t(x,y) values(1,1);
ERROR 1365 (22012): Division by 0
测试sql:
SELECT test_vv FROM t100w limit 10000; #虚拟列无索引
虚拟列函数：
(concat( k1 ,_utf8mb4'-', k2 ))
热门标签
