NeverKnowsTomorrow
0%

MySQL 的 JSON 数据类型（一）

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 8.9k 阅读时长 ≈ 8 分钟

概述

MySQL 从 5.7 版本起支持了 JSON（JavaScript Object Notation，JavaScript 对象表示法）数据类型，在这之前，在 MySQL 中存储 JSON 数据的方式是存储 JSON 格式的字符串。与此相比，新增的 JSON 数据类型具有以下优点：

  • 自动验证存储在 JSON 列中的 JSON 文本格式，错误的 JSON 格式会报错。
  • 优化的存储格式。JSON 文本以二进制格式进行存储，可以快速读取其中的某个元素。我们的程序在读取某个 JSON 值时，不需要从文本形式解析该值，可以通过键或数组索引查找或修改值，而无需读取文本中它们之前或之后的所有值。

本文的内容基于 MySQL 8.0.21 版。

MySQL JSON 数据类型的特点：

  • 存储 JSON 文本所需的空间与 LONGBLOB 或 LONGTEXT 大致相同，最大容量限制为 MySQL 系统变量 max-allowed-packet 的值，在内存中操作 JSON 值时，可以大于此值，该限制在存储时生效；

    • max-allowed-packet 默认是 67108864 字节，即 64 MB，最小 1 KB，最大 1 GB

    • 可以通过以下命令查看 MySQL 的 max-allowed-packet 值：

      1
      show VARIABLES like 'max_allowed_packet';
      1
      2
      3
      4
      5
      +--------------------+----------+
      | Variable_name      | Value    |
      +--------------------+----------+
      | max_allowed_packet | 67108864 |
      +--------------------+----------+
    • 可以通过 JSON_STORAGE_SIZE () 函数获取存储 JSON 文本所需的空间大小：
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      mysql> CREATE TABLE jtable (jcol JSON);
      Query OK, 0 rows affected (0.42 sec)

      mysql> INSERT INTO jtable VALUES
      ->     ('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}');
      Query OK, 1 row affected (0.04 sec)

      mysql> SELECT
          ->     jcol,
          ->     JSON_STORAGE_SIZE (jcol) AS Size,
          ->     JSON_STORAGE_FREE (jcol) AS Free
          -> FROM jtable;
      +-----------------------------------------------+------+------+
      | jcol                                          | Size | Free |
      +-----------------------------------------------+------+------+
      | {"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"} |   47 |    0 |
      +-----------------------------------------------+------+------+
      1 row in set (0.00 sec)
  • 在 MySQL 8.0.13 之前，JSON 列不能具有非 null 的默认值，也就是默认值必须为 null。
  • JSON 列与其他二进制类型的列一样，不会直接建立索引。你可以通过为已存在的其他列创建索引来为 JSON 列提供索引（详见 https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index）
    • MySQL 优化器还会在虚拟列上查找与 JSON 表达式匹配的兼容索引。
    • 在 MySQL 8.0.17 及更高版本中，InnoDB 存储引擎支持 JSON 数组上的多值索引。请参见 多值索引。

在 MySQL 8.0 中，可以对 JSON 列进行局部更新，UPDATE 语句可以通过 JSON_SET ()、JSON_REPLACE () 或 JSON_REMOVE () 中的任何一个来局部更新 JSON 列。

创建 JSON 值

JSON 类型有 JSON 数组和 JSON 对象两种，JSON 数组中的值包含在 [] 中，由逗号分隔：

1
["abc", 10, null, true, false]

JSON 对象由包含在 {} 中的键值对组成：
1
{"k1": "value", "k2": 10}

如上示例所示，JSON 对象和数组的值可以是：字符串、数字、null、true 和 false。JSON 对象中的 键 必须是 字符串 。JSON 值也可以是时间（日期，时间或日期时间）的标量值：
1
["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON 数组元素和 JSON 对象键值中允许嵌套：
1
2
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

MySQL 会以 JSON 格式解析在需要 JSON 值的上下文中使用的任何字符串，如果该字符串作为 JSON 无效，则会报错。

插入 JSON 格式的字符串：

1
2
3
mysql> CREATE TABLE t1 (jdoc JSON);

Query OK, 0 rows affected (0.20 sec)

1
2
3
mysql> INSERT INTO t1 VALUES ('{"key1": "value1", "key2": "value2"}');

Query OK, 1 row affected (0.01 sec)
1
2
3
4
mysql> INSERT INTO t1 VALUES ('[1, 2,');

ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

JSON_TYPE () 函数可以返回参数对应的 JSON 类型：

1
2
3
4
5
6
mysql> SELECT JSON_TYPE ('["a", "b", 1]');
+----------------------------+
| JSON_TYPE ('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

1
2
3
4
5
6
mysql> SELECT JSON_TYPE ('"hello"');
+----------------------+
| JSON_TYPE ('"hello"') |
+----------------------+
| STRING               |
+----------------------+
1
2
3
mysql> SELECT JSON_TYPE ('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

JSON_ARRAY () 和 JSON_OBJECT () 函数可以将输入的参数组成 JSON 数组和对象：

1
2
3
4
5
6
mysql> SELECT JSON_ARRAY ('a', 1, NOW ());
+----------------------------------------+
| JSON_ARRAY ('a', 1, NOW ())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

1
2
3
4
5
6
mysql> SELECT JSON_OBJECT ('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT ('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

JSON_MERGE_PRESERVE () 函数可以合并两个或多个 JSON 文本：

1
2
3
4
5
6
7
mysql> SELECT JSON_MERGE_PRESERVE ('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE ('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0.3 和更高版本还支持 JSON_MERGE_PATCH () 函数，两者的作用都是合并 JSON 文本，其区别在于：

  • 如果第二个对象的成员具有与第一个对象中的成员匹配的键，则 JSON_MERGE_PATCH () 用第二个对象中的值替换第一个对象中的值，而 JSON_MERGE_PRESERVE () 将第二个值附加到第一个值。简而言之，一个会覆盖，一个会追加。

    示例如下：

    1
    2
    3
    4
    5
    6
    mysql> SET @x = '{ "a": 1, "b": 2 }',
        >     @y = '{ "a": 3, "c": 4 }',
        >     @z = '{ "a": 5, "d": 6 }';

    mysql> SELECT  JSON_MERGE_PATCH (@x, @y, @z)    AS Patch,
        ->         JSON_MERGE_PRESERVE (@x, @y, @z) AS Preserve\G
    1
    2
    3
    *************************** 1. row ***************************
    Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
    Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}

可以将 JSON 值分配给用户定义的变量：

1
2
3
4
5
6
7
mysql> SET @j = JSON_OBJECT ('key', 'value');
mysql> SELECT @j;
+------------------+
| @j               |
+------------------+
| {"key": "value"} |
+------------------+

可以通过 CAST () 函数将符合 JSON 文本格式的字符串转换为 JSON 文本：

1
select CAST('{"key1": "value1", "key2": "value2"}' AS JSON);

1
2
3
4
5
6
7
+------------------------------------------------------+
| CAST ('{"key1": "value1", "key2": "value2"}' AS JSON) |
+------------------------------------------------------+
| {"key1": "value1", "key2": "value2"}                 |
+------------------------------------------------------+
1 row in set
Time: 0.010s

如果你想在 JSON 文本中插入带有引号（'' 或 ""）的内容，比如在 facts 表中插入以下形式的键值对：

1
mascot: The MySQL mascot is a dolphin named "Sakila".

1
mysql> CREATE TABLE facts (sentence JSON);

可以使用 JSON_OBJECT () 插入，并使用反斜杠对引号进行转义：

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

或者交替使用单双引号：
1
2
mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT ("mascot", 'Our mascot is a dolphin named "Sakila".'));

如果你使用 JSON 格式的文本形式插入 JSON 对象，需要使用双反斜杠进行转义：

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

使用 SELECT 语句查看：
1
2
3
4
5
6
mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+

如果使用 mascot 作为 key 查找对应的值，可以使用 column-path 运算符 ->：

1
2
3
4
5
6
7
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

如果想显示不包含外面的引号和转义符的输出，可以使用 inline path 运算符 ->>：
1
2
3
4
5
6
mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+

  • 如果启用了 NO_BACKSLASH_ESCAPES 服务器 SQL 模式，上一个示例将无法正常工作。如果设置了此模式，则可以使用单个反斜杠（而不是双反斜杠）来插入 JSON 对象文本，并且保留反斜杠。如果在执行插入操作时使用 JSON_OBJECT () 函数，并且设置了此模式，则必须用交替使用单引号和双引号的方法。

当使用 JSON_OBJECT () 函数创建 JSON 对象还有向 JSON 列中插入 JSON 数据时，如果有重复的键，其对应的值后者会覆盖前者（从左到右）：

1
2
3
4
5
6
mysql> SELECT JSON_OBJECT ('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT ('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> CREATE TABLE t1 (c1 JSON);

mysql> INSERT INTO t1 VALUES
     >     ('{"x": 17, "x": "red"}'),
     >     ('{"x": 17, "x": "red", "x": [3, 5, 7]}');

mysql> SELECT c1 FROM t1;
+------------------+
| c1               |
+------------------+
| {"x": "red"}     |
| {"x": [3, 5, 7]} |
+------------------+