生成列是一种特殊的列，其值是表达式计算的结果。本文介绍了生成列的创建方法，以及在生成列上创建索引的方法。
创建生成列
语法
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED | LOGICAL] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']生成列有以下三种类型：
VITRUAL：生成列的值不存储，每次读取该列时由存储节点DN计算，不占用存储空间。
说明如果不指定关键字，默认创建VITRUAL类型的生成列。
STORED：生成列的值在数据行插入或更新时由存储节点DN计算，并将结果储存下来，需要占用存储空间。
LOGICAL：与STORED类型相似，生成列的值在数据行插入或更新时计算，区别是生成列的值在计算节点CN计算，随后以普通列的形式存储到DN中。该类型的生成列可以作为分区键使用。
注意事项
5.4.17及以上版本的企业版实例支持此功能。
与MySQL类似，相比普通列，使用生成列有如下限制条件：
与MySQL相同的限制
不支持为生成列指定默认值。
不支持为生成列设置AUTO_INCREMENT属性，或引用包含AUTO_INCREMENT属性的列。
不支持在生成列表达式中使用非确定性（deterministic）函数，例如UUID()、CONNECTION_ID()、NOW()等。
不支持在生成列表达式中使用变量。
不支持在生成列表达式中使用子查询。
不支持在INSERT/UPDATE语句中显式指定生成列的值，生成列值的计算只能由数据库自动完成。
与MySQL不同的限制
不支持在开启冷数据归档功能的表中添加生成列。
不支持VIRTUAL/STORED类型的生成列作为分区键、主键或者唯一键。
不支持在VIRTUAL/STORED类型的生成列中引用存储函数。
如果一个全局二级索引中包含了VIRTUAL/STORED类型的生成列，那么该全局二级索引也需要包含生成列表达式中引用的所有列。
不支持在LOGICAL类型的生成列表达式中引用VIRTUAL/STORED类型的生成列。
不支持修改LOGICAL类型的生成列的类型，以及生成列的表达式中引用列的类型。
仅支持以下类型作为LOGICAL类型的生成列和其表达式中引用的列的类型：
整数类型（BIGINT、INT、MEDUMINT、SMALLINT、TINYINT）；
日期类型（DATETIME、DATE、TIMESTAMP），不支持包含ON UPDATE CURRENT_TIMESTAMP属性的日期类型列；
字符串类型（CHAR、VARCHAR）。
示例——VIRTUAL/STORED类型生成列
示例1
使用生成列自动计算直角三角形的斜边（斜边等于两个直角边平方和的平方根）：
CREATE TABLE triangle (
sidea DOUBLE,
sideb DOUBLE,
sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);插入数据：
INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
select * from triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec |
+-------+-------+--------------------+
| 1.0 | 1.0 | 1.4142135623730951 |
| 3.0 | 4.0 | 5.0 |
| 6.0 | 8.0 | 10.0 |
+-------+-------+--------------------+示例2
创建分区表t1，其中含有生成列b：
CREATE TABLE `t1` (
`a` int(11) NOT NULL,
`b` int(11) GENERATED ALWAYS AS (`a` + 1),
PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`)插入数据：
INSERT INTO t1(a) VALUES (1);
SELECT * FROM t1;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+示例——LOGICAL类型生成列示例
LOGICAL类型的生成列可以作为分区键使用，可以利用生成列实现更加灵活的分区策略。此外 LOGICAL 类型的生成列也支持引用自定义函数。
示例1：选取字符串字段的最后两个字符作为分区键。
CREATE TABLE `t2` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
`c` varchar(2) GENERATED ALWAYS AS (SUBSTR(`b`, -2)) LOGICAL,
PRIMARY KEY (`a`),
KEY `auto_shard_key_c` USING BTREE (`c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`c`)示例2：引用自定义函数的LOGICAL类型的生成列。
创建自定义函数my_abs：
DELIMITER &&
CREATE FUNCTION my_abs (
a INT
)
RETURNS INT
BEGIN
IF a < 0 THEN
RETURN -a;
ELSE
RETURN a;
END IF;
END&&
DELIMITER ;创建表t3，该表中b列是一个生成列，表达式中含有自定义函数my_abs：
CREATE TABLE `t3` (
`a` int(11) NOT NULL,
`b` int(11) GENERATED ALWAYS AS (MY_ABS(`a`)) LOGICAL,
PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`b`);
INSERT INTO t3 (a) VALUES(1),(-1);
EXPLAIN SELECT * FROM t3 WHERE b = 1;
+-----------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+-----------------------------------------------------------------------------------------------------------+
| LogicalView(tables="TEST_000002_GROUP.t3_WHHZ", sql="SELECT `a`, `b` FROM `t3` AS `t3` WHERE (`b` = ?)") |
+-----------------------------------------------------------------------------------------------------------+
SELECT * FROM t3 WHERE b = 1;
+----+------+
| a | b |
+----+------+
| -1 | 1 |
| 1 | 1 |
+----+------+在生成列上创建索引
PolarDB-X支持在生成列上创建索引。
注意事项
5.4.17及以上版本的企业版实例支持此功能。
支持在所有类型的生成列上创建局部索引。
支持在LOGICAL类型的生成列上创建全局索引。
不支持在VIRTUAL/STORED类型的生成列上创建全局索引。
示例
示例1：在VIRTUAL/STORED类型的生成列上创建局部索引。
CREATE TABLE t4 (
a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c JSON,
g INT AS (c->"$.id") VIRTUAL
) DBPARTITION BY HASH(a);
CREATE INDEX `i` ON `t4`(`g`);
INSERT INTO t4 (c) VALUES
('{"id": "1", "name": "Fred"}'),
('{"id": "2", "name": "Wilma"}'),
('{"id": "3", "name": "Barney"}'),
('{"id": "4", "name": "Betty"}');
EXPLAIN EXECUTE SELECT c->>"$.name" AS name FROM t4 WHERE g > 2;
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t4 | NULL | range | i | i | 5 | NULL | 1 | 100 | Using where |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+示例2：在LOGICAL类型的生成列上创建局部索引。
CREATE TABLE t5 (
a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c varchar(32),
g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);
CREATE INDEX `i` ON `t5`(`g`);
INSERT INTO t5 (c) VALUES
('1111'),
('1112'),
('1211'),
('1311');
EXPLAIN EXECUTE SELECT c AS name FROM t5 WHERE g = '11';
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | t5 | NULL | ref | i | i | 8 | NULL | 4 | 100.00 | Using XPlan, Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+示例3：在LOGICAL类型的生成列上创建全局索引。
CREATE TABLE t6 (
a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c varchar(32),
g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);
CREATE GLOBAL INDEX `g_i` ON `t6`(`g`) COVERING(`c`) DBPARTITION BY HASH(`g`);
INSERT INTO t6 (c) VALUES
('1111'),
('1112'),
('1211'),
('1311');
EXPLAIN SELECT c AS name FROM t6 WHERE g = '11';
+---------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN |
+---------------------------------------------------------------------------------------------------------------------+
| IndexScan(tables="TEST_DRDS_000000_GROUP.g_i_J1MT", sql="SELECT `c` AS `name` FROM `g_i` AS `g_i` WHERE (`g` = ?)") |
+---------------------------------------------------------------------------------------------------------------------+表达式索引
创建索引时，如果PolarDB-X发现某个索引项不是表中的一个列，而是一个表达式，此时PolarDB-X会自动将该表达式转换为VIRTUAL类型的生成列并添加到表中。所有索引项处理完成后，PolarDB-X会按照用户定义继续创建索引，索引定义中的表达式索引项将被替换成对应的生成列。
注意事项
5.4.17及以上版本的企业版实例支持此功能。
创建表达式索引的功能默认关闭，需要打开ENABLE_CREATE_EXPRESSION_INDEX开关后才能使用。
SET GLOBAL ENABLE_CREATE_EXPRESSION_INDEX=TRUE;不支持全局索引。
不支持创建唯一索引。
不支持在创建表语句中创建表达式索引，需要建表后通过ALTER TABLE或者CREATE INDEX语句添加表达式索引。
使用DROP INDEX删除表达式索引时，默认不会删除创建表达式索引时自动创建的生成列，需要通过ALTER TABLE（DRDS模式）/ALTER TABLE（AUTO模式）手动删除。
示例
示例1：创建一个表达式索引。
创建表t7。
CREATE TABLE t7 ( a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, c varchar(32) ) DBPARTITION BY HASH(a);创建表达式索引i。
CREATE INDEX `i` ON `t7`(substr(`c`, 2));完成表达式索引创建之后的表结构如下：
CREATE TABLE `t7` ( `a` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP, `c` varchar(32) DEFAULT NULL, `i$0` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL, PRIMARY KEY (`a`), KEY `i` (`i$0`) ) ENGINE = InnoDB dbpartition by hash(`a`)
由于索引i的索引项是表达式，因此在表中添加了一个生成列i$0，这个生成列的表达式就索引项的表达式。最后创建索引i，其中索引项被替换成为对应的生成列。
创建完表达式索引之后，以下SQL就可以利用表达式索引加快查询速度：
EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr(`c`, 2) = '11';
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t7 | NULL | ref | i | i | 131 | const | 1 | 100 | NULL |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+执行EXPLAIN EXECUTE命令获取在DN上的执行计划，可以发现确实选择了索引i：
EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr(`c`, 2) = '11';
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t7 | NULL | ref | i | i | 131 | const | 1 | 100 | NULL |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+示例2：在索引中使用多个表达式。
创建表t8：
CREATE INDEX idx ON t8(
a + 1,
b,
SUBSTR(c, 2)
);完成表达式索引创建之后的表结构：
CREATE TABLE `t8` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` varchar(32) DEFAULT NULL,
`idx$0` bigint(20) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
`idx$2` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
PRIMARY KEY (`a`),
KEY `idx` (`idx$0`, `b`, `idx$2`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`)对于索引idx，第一个和第三个索引项是表达式，因此在表中添加了两个生成列idx$0和idx$2，这两个生成列的表达式就是第一个和第三个索引项的表达式。最后创建索引idx，其中第一个和第三个索引项被替换成为对应的生成列。
创建完表达式索引之后，以下SQL就可以利用表达式索引加快查询速度：
SELECT * FROM t8 WHERE a+1=10 AND b=20 AND SUBSTR(c,2)='ab';使用EXPLAIN EXECUTE获取在DN上的执行计划，可以发现确实选择了索引idx：
EXPLAIN EXECUTE SELECT * FROM t4 WHERE a+1=10 AND b=20 AND SUBSTR(c,2)='ab';
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t8 | NULL | ref | idx | idx | 145 | const,const,const | 1 | 100 | NULL |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+- 本页导读 （0）