简单理解Mysql json数据类型

点击查看原文

MySQL支持RFC 7159JSON定义的本机数据类型，该类型可有效访问JSON（JavaScript对象表示法）文档中的数据。该 数据类型提供了这些优点超过存储在字符串列JSON格式的字符串： JSON

• 自动验证存储在JSON列中的JSON文档 。无效的文档会产生错误。
• 优化的存储格式。JSON列中存储的JSON文档将 转换为内部格式，以允许快速读取文档元素。当服务器稍后必须读取以该二进制格式存储的JSON值时，无需从文本表示形式解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需读取文档中它们之前或之后的所有值。

MySQL 8.0还使用该 功能支持RFC 7396中定义的JSON Merge Patch格式 。有关示例和更多信息，请参见此函数的描述以及 JSON值的规范化，合并和自动包装。 JSON_MERGE_PATCH()

本讨论使用JSONmonotype专门表示JSON数据类型，而 使用常规字体的“ JSON ”通常表示JSON数据。

存储JSON文档所需的空间与LONGBLOB或 大致相同LONGTEXT。有关更多信息，请参见 第11.7节“数据类型存储要求”。重要的是要记住，存储在JSON列中的任何JSON文档的大小都限于max_allowed_packet系统变量的值。（当服务器内部在内存中操作JSON值时，该值可以大于此值；该限制在服务器存储它时适用。）您可以使用JSON_STORAGE_SIZE()函数获取存储JSON文档所需的空间量 ；请注意，对于JSON 在列中，存储大小（以及由此函数返回的值）是该列在可能对其执行的任何部分更新之前使用的大小（请参阅本节后面有关JSON部分更新优化的讨论）。

在MySQL 8.0.13之前，JSON列不能具有非NULL默认值。

除JSON数据类型外，还有一组SQL函数可用于启用对JSON值的操作，例如创建，操作和搜索。以下讨论显示了这些操作的示例。有关各个函数的详细信息，请参见第12.18节“ JSON函数”。

还提供了一组用于处理GeoJSON值的空间函数。请参见第12.17.11节“空间GeoJSON函数”。

JSON像其他二进制类型的列一样，列也不直接建立索引；相反，您可以在生成的列上创建索引，以从该JSON列中提取标量值 。有关详细示例，请参见 索引生成的列以提供JSON列索引。

MySQL优化器还在与JSON表达式匹配的虚拟列上寻找兼容的索引。

在MySQL 8.0.17及更高版本中，InnoDB 存储引擎支持JSON数组上的多值索引。请参阅 多值索引。

MySQL NDB Cluster 8.0支持JSON列和MySQL JSON函数，包括在从列生成的JSON列上创建索引，以作为无法索引JSON列的解决方法。JSON每个NDB表最多支持3列 。

JSON值的部分更新

在MySQL 8.0中，优化器可以对列执行部分就地更新，JSON而不是删除旧文档并将新文档全部写入该列。可以对满足以下条件的更新执行此优化：

• 正在更新的列声明为 JSON。

• 该UPDATE语句使用任何的三个功能 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列。列值的直接分配（例如 UPDATE mytable SET jcol = '{"a": 10, "b": 25}'）不能作为部分更新执行。

  可以通过这种方式优化JSON单个UPDATE语句 中多个列的更新。MySQL只能对使用刚刚列出的三个函数更新其值的那些列执行部分更新。

• 输入列和目标列必须是同一列；这样的语句UPDATE mytable SET jcol1 = JSON_SET(jcol2, '$.a', 100)不能作为部分更新执行。

  只要输入和目标列相同，此更新就可以以任何组合方式使用对上一项列出的任何函数的嵌套调用。

• 所有更改都会用新值替换现有的数组或对象值，并且不会将任何新元素添加到父对象或数组。

• 要替换的值必须至少与替换值一样大。换句话说，新值不能大于旧值。

  当先前的部分更新为较大的值留出了足够的空间时，可能会发生此要求的例外情况。您可以使用该函数 JSON_STORAGE_FREE()查看任何部分JSON列更新已释放了多少空间 。

可以使用节省空间的紧凑格式将此类部分更新写入二进制日志。可以通过将binlog_row_value_options 系统变量设置为启用此功能PARTIAL_JSON。有关更多信息，请参见此变量的描述。

接下来的几节提供有关JSON值的创建和操作的基本信息。

创建JSON值

JSON数组包含一个值列表，这些值用逗号分隔并包含在[和] 字符内：

 

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

JSON对象包含一组键值对，以逗号分隔并包含在{和 }字符内：

 

{"k1": "value", "k2": 10}

如示例所示，JSON数组和对象可以包含字符串或数字的标量值，JSON空文字或JSON布尔值true或false文字。JSON对象中的键必须是字符串。还允许使用时间（日期，时间或日期时间）标量值：

 

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

JSON数组元素和JSON对象键值中允许嵌套：

 

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]

{"k1": "value", "k2": [10, 20]}

为此，您还可以从MySQL提供的许多函数中获取JSON值（请参见 第12.18.2节“创建JSON值的函数”），以及通过JSON使用将其他类型的值强制转换为使用该类型的值（请参见 在JSON之间进行 转换）和非JSON值）。接下来的几段描述MySQL如何处理作为输入提供的JSON值。 CAST(*value* AS JSON)

在MySQL中，JSON值被写为字符串。MySQL会解析在需要JSON值的上下文中使用的任何字符串，如果该字符串作为JSON无效，则会产生错误。这些上下文包括将值插入具有JSON数据类型的列中， 并将参数传递给需要JSON值的函数（通常显示为MySQL JSON函数的文档json_doc或 json_val在文档中显示 ），如以下示例所示：

• JSON 如果值是有效的JSON值，则 尝试将值插入到列中会成功，但如果不是，则尝试失败：

   

mysql> CREATE TABLE t1 (jdoc JSON);

Query OK, 0 rows affected (0.20 sec)

​

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

Query OK, 1 row affected (0.01 sec)

​

mysql> INSERT INTO t1 VALUES('[1, 2,');

ERROR 3140 (22032) at line 2: Invalid JSON text:

mysql> SELECT JSON_TYPE('["a", "b", 1]');

+----------------------------+

| JSON_TYPE('["a", "b", 1]') |

+----------------------------+

| ARRAY                      |

+----------------------------+

​

mysql> SELECT JSON_TYPE('"hello"');

+----------------------+

| JSON_TYPE('"hello"') |

+----------------------+

| STRING               |

+----------------------+

​

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.

MySQL使用utf8mb4字符集和utf8mb4_bin排序规则处理JSON上下文中使用的 字符串 。其他字符集中的字符串将utf8mb4根据需要转换为。（对于ascii或 utf8字符集中的字符串，无需进行转换，因为ascii和utf8是的子集utf8mb4。）

作为使用文字字符串编写JSON值的替代方法，存在用于从组件元素组成JSON值的函数。JSON_ARRAY()接受（可能为空）值列表，并返回包含这些值的JSON数组：

 

mysql> SELECT JSON_ARRAY('a', 1, NOW());

+----------------------------------------+

| JSON_ARRAY('a', 1, NOW())              |

+----------------------------------------+

| ["a", 1, "2015-07-27 09:43:47.000000"] |

+----------------------------------------+

JSON_OBJECT() 接受键值对的列表（可能为空），并返回包含这些对的JSON对象：

 

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');

+---------------------------------------+

| JSON_OBJECT('key1', 1, 'key2', 'abc') |

+---------------------------------------+

| {"key1": 1, "key2": "abc"}            |

+---------------------------------------+

JSON_MERGE_PRESERVE() 接受两个或多个JSON文档并返回合并的结果：

 

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)

有关合并规则的信息，请参见 JSON值的规范化，合并和自动包装。

（MySQL 8.0.3和更高版本也支持 JSON_MERGE_PATCH()，其行为有所不同。有关这两个函数之间的区别，请参见 JSON_MERGE_PATCH（）与JSON_MERGE_PRESERVE（）的比较。）

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

 

mysql> SET @j = JSON_OBJECT('key', 'value');

mysql> SELECT @j;

+------------------+

| @j               |

+------------------+

| {"key": "value"} |

+------------------+

但是，用户定义的变量不能是 JSON数据类型，所以虽然 @j在前面的例子中看起来像一个JSON值，并且具有相同的字符集并归类为JSON值，但它不具有 JSON数据类型。而是将来自的结果 JSON_OBJECT()分配给变量后转换为字符串。

通过转换JSON值产生的字符串的字符集为utf8mb4，排序规则为 utf8mb4_bin：

 

mysql> SELECT CHARSET(@j), COLLATION(@j);

+-------------+---------------+

| CHARSET(@j) | COLLATION(@j) |

+-------------+---------------+

| utf8mb4     | utf8mb4_bin   |

+-------------+---------------+

因为utf8mb4_bin是二进制排序规则，所以JSON值的比较区分大小写。

 

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');

+-----------------------------------+

| JSON_ARRAY('x') = JSON_ARRAY('X') |

+-----------------------------------+

|                                 0 |

+-----------------------------------+

区分大小写也适用于JSON null，true和 false文字，它们必须始终以小写形式编写：

 

mysql> SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');

+--------------------+--------------------+--------------------+

| JSON_VALID('null') | JSON_VALID('Null') | JSON_VALID('NULL') |

+--------------------+--------------------+--------------------+

|                  1 |                  0 |                  0 |

+--------------------+--------------------+--------------------+

​

mysql> SELECT CAST('null' AS JSON);

+----------------------+

| CAST('null' AS JSON) |

+----------------------+

| null                 |

+----------------------+

1 row in set (0.00 sec)

​

mysql> SELECT CAST('NULL' AS JSON);

ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json:

"Invalid value." at position 0 in 'NULL'.

从该SQL的JSON文字不同的情况下的灵敏度NULL，TRUE和 FALSE文字，它可以在任何大小写被写成：

 

mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);

+--------------+--------------+--------------+

| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |

+--------------+--------------+--------------+

|            1 |            1 |            1 |

+--------------+--------------+--------------+

有时可能需要或希望在JSON文档中插入引号字符（"或'）。假设在此示例中，您想要在表中插入使用以下语句创建的表中的JSON对象，这些JSON对象包含表示有关MySQL的某些事实的句子，每个句子都声明了有关MySQL的一些事实，每个事实与适当的关键字配对。

 

mysql> CREATE TABLE facts (sentence JSON);

这些关键字句子对中的一个是：

 

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

将其作为JSON对象插入facts表中的一种方法 是使用MySQL JSON_OBJECT()函数。在这种情况下，必须使用反斜杠对每个引号字符进行转义，如下所示：

 

mysql> INSERT INTO facts VALUES

     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named "Sakila"."));

如果您将值作为JSON对象文字插入，则此方法无法以相同的方式工作，在这种情况下，必须使用双反斜杠转义序列，如下所示：

 

mysql> INSERT INTO facts VALUES

     >   ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');

使用双反斜杠使MySQL无法执行转义序列处理，而是使它将字符串文字传递给存储引擎进行处理。以上述两种方式插入JSON对象后，您可以通过执行以下简单操作看到JSON列值中存在反斜杠SELECT：

 

mysql> SELECT sentence FROM facts;

+---------------------------------------------------------+

| sentence                                                |

+---------------------------------------------------------+

| {"mascot": "Our mascot is a dolphin named "Sakila"."} |

+---------------------------------------------------------+

要查找使用mascot关键字作为关键字的特定句子 ，可以使用column-path运算符 ->，如下所示：

 

mysql> SELECT col->"$.mascot" FROM qtest;

+---------------------------------------------+

| col->"$.mascot"                             |

+---------------------------------------------+

| "Our mascot is a dolphin named "Sakila"." |

+---------------------------------------------+

1 row in set (0.00 sec)

这使反斜杠以及周围的引号保持完整。要使用mascot键显示所需的值 ，但不包括周围的引号或任何转义符->>，请使用内联路径运算符 ，如下所示：

 

mysql> SELECT sentence->>"$.mascot" FROM facts;

+-----------------------------------------+

| sentence->>"$.mascot"                   |

+-----------------------------------------+

| Our mascot is a dolphin named "Sakila". |

+-----------------------------------------+

注意

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

 

mysql> INSERT INTO facts VALUES

     > (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));

有关JSON_UNQUOTE()此模式对JSON值中的转义字符的影响的更多信息，请参见函数的描述 。

JSON值的规范化，合并和自动包装 等其他更多内容请查看原文