主页 >> 程序猿的东西 >> MySQL 中 JSON 相关的三类常用操作

MySQL 中 JSON 相关的三类常用操作

前言

因为 MySQL 不是分析型数据库,因此对 json 和数组类型的支持不是很好,所以许多东西的实现就很有奇技淫巧的味道。本文整理了 json 相关的一些操作,以备不时之需。

json 字段在 MySQL是最近十年才被支持,目前还有许多未完善的特性。josn 字段还有一个最大的问题就是它在某种意义上违反了第一范式,即字段不具备原子性。至于是否的确违反了第一反思,也要考虑到业务场景,如果这个字段在业务上总是整存整取,那就可以认为具备原子性。但是现在已经到了 4202 年,是否还要严格遵循 1971 年提出的数据库规范也是见仁见智。

常用操作

读写

JSON 字段可以直接按照字符串来整存整取,还可以通过 JSON Path局部读取:

示例代码

mysql > CREATE TABLE facts (sentence JSON);

mysql > INSERT INTO facts VALUES
       (JSON_OBJECT("mascot", "Our mascot is a dolphin named "Sakila"."));

mysql > INSERT INTO facts VALUES
        ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');

mysql > SELECT JSON_EXTRACT(sentence, "$.mascot") AS json_str, JSON_UNQUOTE(JSON_EXTRACT(sentence, "$.mascot")) as str FROM facts;

mysql > SELEC sentence->"$.mascot" AS json_str ,sentence->>"$.mascot" as str FROM facts;

+----------------------------------------------+-----------------------------------------+
| json_str                                     | str                                     |
+----------------------------------------------+-----------------------------------------+
| "Our mascot is a dolphin named "Sakila"."  | Our mascot is a dolphin named "Sakila". |
+----------------------------------------------+-----------------------------------------+

上面两条 SELECT 语句是等效的。

JSON_EXTRACT函数,指定 json 字段和 json path,返回指定 path 的 json字符串

-> 操作,与上面的函数等效,不同的写法

->> 操作,等于JSON_UNQUOTE(JSON_EXTRACT(sentence, "$.mascot"))。本质上,上面两种方式返回的还是 json,因此对于字符串,会被双引号包起来,里面的值也就带转义字符了。而这种方式返回的是对应的值,如果是字符串,直接返回字符串的原始值。但是对于其他复杂类型,没有什么区别。

在使用 json path 的时候,有一个问题,如果 key 是数字或者数字开头的,会出现错误 json本身允许这种情况,但是 json path 在解析的时候会有歧义,可以用双引号包裹起来,比如 sentence->'$."0day"'

构造 JSON 数据

MySQL 提供了三个函数,用来构造三种类型的 JSON

修改 JSON 值

MySQL 提供了以下函数,对 JSON 进行局部修改:

数组作为表/Lateral view

MySQL 没有数组类型,但是 JSON 有数组。数组好比一个小型的表,因此在处理起来有许多特别之处。

JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

JSON_TABLE函数的意思大概就是把 JSON 处理成表来使用。

  • expr 是获得 JSON 的表达式。
  • path 指定了 expr 里面一个数组的位置。
  • COLUMNS 定义了如何处理数组中的每个元素,提取哪些列,并设置别名。

下面是一个范例

mysql> SELECT *
    -> FROM
    ->   JSON_TABLE(
    ->     '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
    ->     "$[*]" COLUMNS(
    ->       xval VARCHAR(100) PATH "$.x",
    ->       yval VARCHAR(100) PATH "$.y"
    ->     )
    ->   ) AS  jt1;

+------+------+
| xval | yval |
+------+------+
| 2    | 8    |
| 3    | 7    |
| 4    | 6    |
+------+------+

columns 里面也有一个 path,这个 path 是针对每个元素的,如果是简单元素的数组,可以直接用$表示整个元素作为一个列。

上面的例子用了一个常量的 json 字符串,我们可以跟表字段结合起来用。如下:

+----+---------------+
| id | name          |
+----+---------------+
| 1  | ["a","b","c"] |  
| 2  | ["b"]         |
+----+---------------+

mysql> select t.id, j.name
from mytable t
join json_table(
  t.name,
  '$[*]' columns (name varchar(50) path '$')
) j;

+----+------+  
| id | name |
+----+------+  
| 1  | a    |
| 1  | b    |
| 1  | c    |
| 2  | b    |
+----+------+  

这个例子中,name 是一个 json 字段,我们把每一行的 name 用 json_table 处理成表的形式,再跟主表 join。就会产生 Lateral view 的效果。

索引

虚拟字段和二级索引

有这样一个表

mysql > CREATE TABLE `activity_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `properties` json NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
)

properties 是一个 json 字段,他的数据结构如下:

{
  "uuid": "e7af5df8-f477-4b9b-b074-ad72fe17f502",
  "request": {
    "email": "[email protected]",
    "firstName": "Little",
    "formType": "vehicle-inquiry",
    "lastName": "Bobby",
    "message": "Hello, can you tell me what the specs are for this vehicle?",
    "postcode": "75016",
    "townCity": "Dallas"
  }
}

假设我们需要针对里面的 email 字段查询,我们可以创建生成字段:

ALTER TABLE activity_log ADD COLUMN email VARCHAR(255)
  GENERATED ALWAYS as (properties->>"$.request.email");

在MySQL中,支持两种生成字段Generated Column,即Virtual Generated Column和Stored Generated Column,前者只将Generated Column保存在数据字典中(表的元数据),并不会将这一列数据持久化到磁盘上;后者会将Generated Column持久化到磁盘上,而不是每次读取的时候计算所得。很明显,后者存放了可以通过已有数据计算而得的数据,需要更多的磁盘空间,与Virtual Column相比并没有优势,因此,MySQL 5.7中,不指定Generated Column的类型,默认是Virtual Column。

我们可以基于生成字段做如下的查询

mysql> SELECT id, email FROM activity_log;
+----+-------------------------+
| id | email                   |
+----+-------------------------+
|  1 | [email protected] |
+----+-------------------------+

进一步,我们可以创建索引来为查询加速

ALTER TABLE activity_log ADD INDEX email (email) USING BTREE;

可以看到使用了索引

mysql> EXPLAIN SELECT * FROM activity_log WHERE email = '[email protected]';
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: activity_log
   partitions: NULL
         type: ref
possible_keys: email
          key: email
      key_len: 768
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

二级索引详情查看官方文档 https://dev.mysql.com/doc/refman/8.4/en/create-table-secondary-indexes.html

多值索引

对于 JSON 数组,我们还可以创建多值索引,查找数组中是否包含某个(多个)值,这是 MySQL8.0.17 之后支持的新特性。

现在有这样一个表:

CREATE TABLE customers (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    custinfo JSON
    );
mysql> INSERT INTO customers VALUES
    (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
    (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
    (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
    (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
    (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');

这个例子中,custinfo是一个 JSON 字段,JSON 中的 zipcode 是一个整型数组。

MySQL 支持用下面三种方式对 JSON 数组做查询

mysql> SELECT * FROM customers
 WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
mysql> SELECT * FROM customers
 WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
mysql> SELECT * FROM customers
 WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

我们用 explain 分析这三个查询都没有用到索引。我们可以用下面的方法创建多值索引试试。

mysql> ALTER TABLE customers
 ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

我们再对其中一条做 explain 看看结果

mysql> EXPLAIN SELECT * FROM customers
  WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ref  | zips          | zips | 9       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+

我们看到使用到了前面创建的 zips 索引。更多详情参考官方文档

但是多值索引的使用还是很局限,只能是 unsigned 之类的值(8.0.33实测),更丰富的数组类型在后续版本有支持。详情参考 cast array 官方文档