• 生成列
    • 使用 generated stored column 对 JSON 建索引
    • 使用 generated virtual column
    • 局限性

    生成列

    为了在功能上兼容 MySQL 5.7,TiDB 支持生成列 (generated column)。生成列的主要的作用之一:从 JSON 数据类型中解出数据,并为该数据建立索引。

    使用 generated stored column 对 JSON 建索引

    MySQL 5.7 及 TiDB 都不能直接为 JSON 类型的列添加索引,即不支持如下表结构:

    1. CREATE TABLE person (
    2. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. name VARCHAR(255) NOT NULL,
    4. address_info JSON,
    5. KEY (address_info)
    6. );

    为 JSON 列添加索引之前,首先必须抽取该列为 generated stored column。

    注意:

    必须是 generated stored column 上建立的索引才能被优化器使用到,如果在 generated virtual column 上建立索引,优化器目前将无法使用这个索引,会在后续版本中改进(ISSUE #5189)。

    city generated stored column 为例,你可以添加索引:

    1. CREATE TABLE person (
    2. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. name VARCHAR(255) NOT NULL,
    4. address_info JSON,
    5. city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED,
    6. KEY (city)
    7. );

    该表中,city 列是一个 generated stored column。顾名思义,此列由该表的其他列生成,对此列进行插入或更新操作时,并不能对之赋值。此列按其定义的表达式生成,并存储在数据库中,这样在读取此列时,就可以直接读取,不用再读取其依赖的 address_info 列后再计算得到。city 列的索引存储在数据库中,并使用和 varchar(64) 类的其他索引相同的结构。

    可使用 generated stored column 的索引,以提高如下语句的执行速度:

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

    如果 $.city 路径中无数据,则 JSON_EXTRACT 返回 NULL。如果想增加约束,city 列必须是 NOT NULL,则可按照以下方式定义 virtual column:

    1. CREATE TABLE person (
    2. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. name VARCHAR(255) NOT NULL,
    4. address_info JSON,
    5. city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) STORED NOT NULL,
    6. KEY (city)
    7. );

    INSERTUPDATE 语句都会检查 virtual column 的定义。未通过有效性检测的行会返回错误:

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

    使用 generated virtual column

    TiDB 也支持 generated virtual column,和 generated store column 不同的是,此列按需生成,并不存储在数据库中,也不占用内存空间,因而是虚拟的。TiDB 虽然支持在 generated virtual column 上建立索引,优化器目前将无法使用这个索引,所以这个索引将没有意义,会在后续版本中改进(ISSUE #5189)。

    1. CREATE TABLE person (
    2. id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    3. name VARCHAR(255) NOT NULL,
    4. address_info JSON,
    5. city VARCHAR(64) AS (JSON_UNQUOTE(JSON_EXTRACT(address_info, '$.city'))) VIRTUAL
    6. );

    局限性

    目前 JSON and generated column 有以下局限性:

    • 不能通过 ALTER TABLE 增加 STORED 存储方式的 generated column;
    • 不能通过 ALTER TABLE 在 generated column 上增加索引;
    • 并未支持所有的 JSON 函数。