MySQL JSON类型详解（附带实例）

MySQL 是一个关系数据库，在 MySQL 8.0 之前，没有提供对非结构化数据的支持，但是如果用户有这样的需求，也可以通过 MySQL 的 BLOB 来存储非结构化的数据。

【实例 1】在 MySQL 8.0 及之前，通过 BLOB 来存储非结构化的数据。

mysql> create table tp(json_data blob);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tp values('{"key1":"data1", "key2":2,
"key3":{"sub_key1":"sub_val1"}}');
Query OK, 1 row affected (0.01 sec)

SELECT * FROM tp;
+------------------------------------------------------------+
| json_data                                                  |
+------------------------------------------------------------+
| {"key1":"data1", "key2":2, "key3":{"sub_key1":"sub_val1"}} |
+------------------------------------------------------------+
1 row in set (0.01 sec)

在本例中，使用 BLOB 来存储 JSON 数据，使用这种方法，需要用户保证插入的数据是一个能够转换成 JSON 格式的字符串，MySQL 并不保证任何正确性。

在 MySQL 看来，这就是一个普通的字符串，并不会进行任何有效性检查。此外，提取 JSON 中的字段，也需要在用户的代码中完成。例如，在 Python 语言中提取 JSON 中的字段，代码如下：

#!/usr/bin/python
import pymysql
import json

try:
    conn = pymysql.connect(host="127.0.0.1", db="test", user="root", passwd="123456", port=3306)
    sql = "select * from tp"
    cur = conn.cursor()
    cur.execute(sql)
    rows =  cur.fetchall()
    print json.dumps(json.loads(rows[0][0]), indent=4)
except:
    conn.close()

这种方式虽然也能够实现 JSON 的存储，但是有诸多缺点，最为显著的缺点有：

需要用户保证 JSON 的正确性，如果用户插入的数据并不是一个有效的 JSON 字符串，MySQL 并不会报错；
所有对 JSON 的操作，都需要在用户的代码里进行处理，不够友好；
即使只提取 JSON 中的某一个字段，也需要读出整个 BLOB，效率不高；
无法在 JSON 字段上创建索引。

在 MySQL 9.0 中，实现了对 JSON 类型的支持。MySQL 本身已经是一个比较完备的数据库系统，其底层存储并不适合有太大的改动，那么 MySQL 是如何支持 JSON 格式的呢？

MySQL 9.0 支持 JSON 的做法是在服务层提供一些便于操作 JSON 的函数，简单地将 JSON 编码成 BLOB，然后交由存储引擎层进行处理。MySQL 9.0 的 JSON 支持与存储引擎没有关系，MyISAM 存储引擎也支持 JSON 格式。下面举例说明。

【实例 2】InnoDB 和 MyISAM 都支持 JSON 格式。

mysql> create table tb(data json)engine=innodb;
Query OK, 0 rows affected (0.18 sec)

mysql> insert into tb values('{"key":"val"}');
Query OK, 1 row affected (0.03 sec)

mysql> create table ts(data json)engine=myisam;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ts values('{"key":"val"}');
Query OK, 1 row affected (0.00 sec)

MySQL 9.0 中的一些 JSON 函数包括：

JSON_EXTRACT()：从 JSON 数据中提取值；
JSON_UNQUOTE()：从 JSON 数据中去除引号；
JSON_OBJECT()：创建一个 JSON 对象；
JSON_ARRAY()：创建一个 JSON 数组；
JSON_MERGE()：合并两个 JSON 数据；
JSON_SEPARATE()：将 JSON 据分解为多个部分。

这些函数使用户能够更轻松地处理 JSON 数据，提高数据库操作的性能。

当将 JSON 数据存储在 MySQL 中的 BLOB 对象中时，数据会被组织成以下格式：

首先存放 JSON 元素的个数；
然后存放转换成 BLOB 以后的字节数；
接下来存放 key pointers 和 value pointers。

为了加快查找速度，MySQL 内部会对 key 进行排序，以提高处理速度。这样，在查询JSON数据时，可以更快地定位到相应的key和value。

在 MySQL 9.0 中，key 的长度只用 2 个字节（65535）保存，如果超过这个长度，MySQL 将报错，如下所示：

mysql> insert into tb values(JSON_OBJECT(repeat('a', 65535), 'val'));
Query OK, 1 row affected (0.37 sec)

mysql> insert into tb values(JSON_OBJECT(repeat('a', 65536), 'val'));
ERROR 3151 (22032): The JSON object contains a key name that is too long.

在 MySQL 的源码中，与 JSON 相关的文件有：

json_binary.cc；
json_binary.h；
json_dom.cc；
json_dom.h；
json_path.cc；
json_path.h。

其中，json_binary.cc 处理 JSON 的编码、解码，json_dom.cc 是 JSON 的内存表示，json_path.cc 用以将字符串解析成JSON。

对于 JSON 的编码，入口是 json_binary.cc 文件中的 serialize 函数；对于 JSON 的解码，即将 BLOB 解析成 JSON 对象，入口是 json_binary.cc 文件中的 parse_binary 函数。只要搞清楚了 JSON 的存储格式，这两个函数就很好理解了。

MySQL JSON类型详解（附带实例）

相关文章