Generated Columns

本文介绍了 generated columns 的概念和用法。

Basic concepts

与普通列不同，generated column 的值由列定义中的表达式计算得出。在插入或更新 generated column 时，不能为其赋值，只能使用 DEFAULT。

有两种类型的 generated columns：virtual 和 stored。virtual generated column 不占用存储空间，读取时会计算得出。stored generated column 在写入（插入或更新）时计算，并占用存储空间。与 virtual generated columns 相比，stored generated columns 具有更好的读取性能，但占用更多磁盘空间。

无论是 virtual 还是 stored，都可以在 generated column 上创建索引。

Usage

generated columns 的主要用途之一是从 JSON 数据类型中提取数据并对其建立索引。

在 MySQL 8.0 和 TiDB 中，JSON 类型的列不能直接建立索引。也就是说，以下表结构 不支持：

CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address_info JSON,
    KEY (address_info)
);

为了对 JSON 列建立索引，必须先将其提取为 generated column。

以 address_info 中的 city 字段为例，可以创建一个 virtual generated column 并为其添加索引：

CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address_info JSON,
    city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))), -- virtual generated column
    -- city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) VIRTUAL, -- virtual generated column
    -- city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED, -- stored generated column
    KEY (city)
);

在此表中，city 列为 virtual generated column，并且已建立索引。以下查询可以利用索引加快执行速度：

SELECT name, id FROM person WHERE city = 'Beijing';

EXPLAIN SELECT name, id FROM person WHERE city = 'Beijing';

+---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+
| id                              | estRows | task      | access object                  | operator info                                               |
+---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+
| Projection_4                    | 10.00   | root      |                                | test.person.name, test.person.id                            |
| └─IndexLookUp_10                | 10.00   | root      |                                |                                                             |
|   ├─IndexRangeScan_8(Build)     | 10.00   | cop[tikv] | table:person, index:city(city) | range:["Beijing","Beijing"], keep order:false, stats:pseudo |
|   └─TableRowIDScan_9(Probe)     | 10.00   | cop[tikv] | table:person                   | keep order:false, stats:pseudo                              |
+---------------------------------+---------+-----------+--------------------------------+-------------------------------------------------------------+

从执行计划可以看出，索引 city 被用来读取满足条件 city ='Beijing' 的行的 HANDLE，然后利用该 HANDLE 读取对应行的数据。

如果路径 $.city 中没有数据，JSON_EXTRACT 会返回 NULL。如果你希望强制 city 不为 NULL，可以将 virtual generated column 定义为：

CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address_info JSON,
    city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) NOT NULL,
    KEY (city)
);

Validation of generated columns

在 INSERT 和 UPDATE 语句中，都会检查 virtual column 的定义。未通过验证的行会返回错误：

mysql> INSERT INTO person (name, address_info) VALUES ('Morgan', JSON_OBJECT('Country', 'Canada'));
ERROR 1048 (23000): Column 'city' cannot be null