Skip to content

Latest commit

 

History

History
121 lines (87 loc) · 7.41 KB

json-functions-generated-column.md

File metadata and controls

121 lines (87 loc) · 7.41 KB
Raw
title category
JSON 函数及 Generated Column
user guide

JSON 函数及 Generated Column

概述

为了在功能上兼容 MySQL 5.7 及以上，同时更好地支持文档类型存储，我们在最新版本的 TiDB 中加入了 JSON 的支持。TiDB 所支持的文档是指以 JSON 为编码类型的键值对的组合。用户可以在 TiDB 的表中使用 JSON 类型的字段，同时以生成列（generated column）的方式为 JSON 文档内部的字段建立索引。基于此，用户可以很灵活地处理那些 schema 不确定的业务，同时不必受限于传统文档数据库糟糕的读性能及匮乏的事务支持。

JSON 功能介绍

TiDB 的 JSON 主要参考了 MySQL 5.7 的用户接口。例如，可以创建一个表，包含一个 JSON 字段来存储那些复杂的信息：

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

当我们向表中插入数据时，便可以这样处理那些模式不确定的数据了：

INSERT INTO person (name, address_info) VALUES ("John", '{"city": "Beijing"}');

就这么简单！直接在 JSON 字段对应的位置上，放一个合法的 JSON 字符串，就可以向表中插入 JSON 了。TiDB 会解析这个文本，然后以一种更加紧凑、易于访问的二进制形式来保存。

当然，你也可以将其他类型的数据用 CAST 转换为 JSON：

INSERT INTO person (name, address_info) VALUES ("John", CAST('{"city": "Beijing"}' AS JSON));
INSERT INTO person (name, address_info) VALUES ("John", CAST('123' AS JSON));
INSERT INTO person (name, address_info) VALUES ("John", CAST(123 AS JSON));

现在，如果我们想查询表中所有居住在北京的用户，该怎么做呢？需要把数据全拉回来，然后在业务层进行过滤吗？不需要，和 MongoDB 等文档数据库相同，我们有在服务端支持用户各种复杂组合查询条件的能力。你可以这样写 SQL：

SELECT id, name FROM person WHERE JSON_EXTRACT(address_info, '$.city') = 'Beijing';

TiDB 支持 JSON_EXTRACT 函数，该函数与 MySQL 5.7 中 JSON_EXTRACT 的用法完全相同。这个函数的意思就是，从 address_info 这个文档中取出名为 city 这个字段。它的第二个参数是一个“路径表达式”，我们由此可以指定到底要取出哪个字段。关于路径表达式的完整语法描述比较复杂，我们还是通过几个简单的例子来了解其用法：

SET @person = '{"name":"John","friends":[{"name":"Forest","age":16},{"name":"Zhang San","gender":"male"}]}';

SELECT JSON_EXTRACT(@person,  '$.name'); -- gets "John"
SELECT JSON_EXTRACT(@person,  '$.friends[0].age'); -- gets 16
SELECT JSON_EXTRACT(@person,  '$.friends[1].gender'); -- gets "male"
SELECT JSON_EXTRACT(@person,  '$.friends[2].name'); -- gets NULL

除了插入、查询外，对 JSON 的修改也是支持的。总的来说，目前我们支持的 MySQL 5.7 的 JSON 函数如下表所示：

直接从名字上，我们便能得出这些函数的大致用途，而且它们的语义也与 MySQL 5.7 完全一致，因此，想要查询它们具体的用法，我们可以直接查阅 MySQL 5.7 的相关文档。MySQL 5.7 的用户可以无缝迁移至 TiDB。

熟悉 MySQL 5.7 的用户会发现，TiDB 尚未完全支持 MySQL 5.7 中所有的 JSON 函数。通过 TiDB #7546 可查看 TiDB 中添加新函数的进度。

使用生成列对 JSON 建索引

在有了上述的知识铺垫后，您可能会发现我们在查询 JSON 中的一个字段时，走的是全表扫描。使用 TiDB 的 EXPLAIN 语句时，一个比 MySQL 完备得多的结果会告诉我们，的确是全表扫描。那么，我们能否对 JSON 字段进行索引呢？

首先，这种索引是错误的：

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

这并非是因为技术上无法支持，而是因为对 JSON 的直接比较，本身就是没有意义的 —— 尽管我们可以人为地约定一些比较规则，比如 ARRAY 比所有的 OBJECT 都大 —— 但是这并没有什么用处。因此，正如 MySQL 5.7 所做的那样，我们禁止了直接在 JSON 字段上创建索引，而是通过生成列的方式，支持了对 JSON 文档内的某一字段建立索引：

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,
    KEY (city)
);

这个表中，city 列就是一个 生成列。顾名思义，该列由表中其他的列生成，而不能显式地在插入或更新时为它赋一个值。对于生成列，用户还可以指定其为 VIRTUAL 来避免它被显式地保存在记录中，而是在需要地时候再由其他列来生成，这对于列比较宽且需要节约存储空间地情况尤为有用。有了这个生成列，我们就可以在它上面建立索引了，在用户看来与常规的列便没什么两样，是不是很简单呢？而查询的时候，我们可以：

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

这样，便可以走索引了！

另外，需要注意的是，如果 JSON 文档中指定路径下的字段不存在，那么 JSON_EXTRACT 的结果会是 NULL ，这时，带有索引的生成列的值也就为 NULL 了。因此，如果这是用户不希望看到的，那也可以在生成列上增加 NOT NULL 约束，这样，当插入新的纪录算出来的 city 字段为 NULL 时，便可以检查出来了。

目前的一些限制

目前 JSON 及生成列仍然有一些限制：

  • 不能 ALTER TABLE 增加 STORED 存储方式的生成列；
  • 不能 ALTER TABLE 在生成列上增加索引；

这些功能，包括其他一些 JSON 函数的实现尚在开发过程中。