解锁 Mysql 中的 JSON 数据类型，怎一个爽字了得

2024-02-21
福建
本文字数：20577 字
阅读完需：约 68 分钟

引言

在实际业务开发中，随着业务的变化，数据的复杂性和多样性不断增加。传统的关系型数据库模型在这种情况下会显得受限，因为它们需要预先定义严格的数据模式，并且通常只能存储具有相同结构的数据。而面对非结构化或半结构化数据的存储和处理需求，选择使用非关系型数据库或者创建子表存储这些变化的结构可能会变得复杂。在这种情况下，我们可以利用MySQL的JSON字段类型来解决这个问题。JSON字段提供了灵活的数据存储方式，能够轻松应对数据结构的变化和动态性，从而更好地满足业务需求。

MySQL5.7.8版本引入了 JSON 数据类型，允许在数据库表中存储和操作符合JSON格式的数据。这种原生支持JSON（JavaScript对象表示法）的数据类型遵循RFC 7159标准，提供了有效存储复杂、半结构化数据的方式。MySQL8.0版本全面支持JSON数据类型，标志着MySQL在处理非结构化数据方面取得了显著进展。除了能够高效存储JSON文档外，MySQL8.0的JSON数据类型还引入了强大的内置函数，极大地提升了操作和查询JSON数据的效率和便捷性。本文我们以MYSQL 8.0环境研究一下它的JSON类型。

JSON 数据类型的特性

二进制存储 MySQL 8.0 采用了一种优化的二进制格式存储 JSON 数据，相较于传统的字符串存储方式，这种新格式能够更迅速地读取和解析 JSON 文档。该格式允许服务器通过键或数组索引直接查找子对象或嵌套值，无需事先读取或解析文档中的所有值。这不仅降低了存储空间占用，还提升了查询性能。JSON 列存储的 JSON 文档所需的空间大致与 LONGBLOB 或 LONGTEXT 相同。但要注意，存储在 JSON 列中的 JSON 文档大小受max_allowed_packet系统变量的限制。

自动验证当插入或更新包含 JSON 字段的记录时，MySQL 会自动验证所插入的内容是否符合 JSON 格式规范，确保数据的一致性和完整性。

索引支持 JSON 列不直接索引，但可以在 JSON 字段中特定路径上创建索引，例如通过JSON_EXTRACT()函数提取出用于索引的值。此外，MySQL 优化器还会寻找与 JSON 表达式匹配的虚拟列上的兼容索引。

部分内容更新从 MySQL 8.0 开始，优化器可以执行 JSON 列的部分、原地更新，而不是完全删除旧文档并将新文档完全写入列。这种优化可以通过使用JSON_SET()、JSON_REPLACE()或JSON_REMOVE()等函数进行更新。

丰富的 JSON 函数 MySQL 提供了一组用于操作 JSON 值的 SQL 函数，包括创建、操作和搜索。此外，还有一组用于操作 GeoJSON 值的空间函数。

JSON 操作函数

MySQL 提供了一组用于操作 JSON 值的 SQL 函数，包括创建、操作和搜索。

1、创建 JSON 值

JSON_ARRAY

用于创建 JSON 数组。语法格式：

JSON_ARRAY([val[, val] ...])

复制代码

mysql> SELECT JSON_ARRAY(1, "abc", NULL, TRUE, NOW());+------------------------------------------------------+| JSON_ARRAY(1, "abc", NULL, TRUE, NOW())              |+------------------------------------------------------+| [1, "abc", null, true, "2024-02-05 03:29:56.000000"] |+------------------------------------------------------+1 row in set (0.00 sec)

复制代码

JSON_OBJECT

用于创建 JSON 对象。语法格式：

JSON_OBJECT([key, val[, key, val] ...])

复制代码

 SELECT JSON_OBJECT('name', 'CoderAcademy', 'age', 30) AS person;+-----------------------------+| person                      |+-----------------------------+| {"age": 30, "name": "CoderAcademy"} |+-----------------------------+1 row in set (0.00 sec)

复制代码

注意，如果传入的不是合法的 JSON 格式，则会报错。

JSON_QUOTE

用于去除 JSON 字符串中的引号，将一个 JSON 格式化的字符串转换为常规的数据库内可直接使用的字符串。当从 JSON 文档中提取出一个原本被双引号包围的字符串时，此函数会移除这些引号，从而便于后续对提取出的数据进行进一步的 SQL 操作或者与其他非 JSON 字段进行比较。

mysql> SELECT JSON_QUOTE('CoderAcademy'), JSON_QUOTE('"CoderAcademy"');+----------------------------+------------------------------+| JSON_QUOTE('CoderAcademy') | JSON_QUOTE('"CoderAcademy"') |+----------------------------+------------------------------+| "CoderAcademy"             | "\"CoderAcademy\""           |+----------------------------+------------------------------+1 row in set (0.00 sec)
mysql> SELECT JSON_QUOTE('[1, 2, 3]') AS json1 ,JSON_QUOTE('["a", "b","c"]')  AS json2, JSON_QUOTE('{"name":"CoderAcademy", "age": 30}')  AS json3;+-------------+------------------------+--------------------------------------------+| json1       | json2                  | json3                                      |+-------------+------------------------+--------------------------------------------+| "[1, 2, 3]" | "[\"a\", \"b\",\"c\"]" | "{\"name\":\"CoderAcademy\", \"age\": 30}" |+-------------+------------------------+--------------------------------------------+1 row in set (0.00 sec)

复制代码

CAST(value AS JSON)

在 MySQL8.0 中，CAST(value AS JSON)函数会尝试将给定的值转化为一个有效的 JSON 格式字符串。语法结构如下：

CAST(value AS JSON)

复制代码

mysql> SELECT CAST('["apple", "banana", "cherry"]' AS JSON) AS json1, CAST('{"name":"CoderAcademy", "age": 30}' AS JSON ) AS json2;+-------------------------------+-------------------------------------+| json1                         | json2                               |+-------------------------------+-------------------------------------+| ["apple", "banana", "cherry"] | {"age": 30, "name": "CoderAcademy"} |+-------------------------------+-------------------------------------+1 row in set (0.00 sec)

复制代码

需要注意的是，如果要转换的值不符合 JSON 格式规范，则会抛出错误。例如：NULL、不合法的 JSON 字符串或其他非转换类型的值。

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.
mysql> SELECT CAST('{"name":"CoderAcademy", "age"}' AS JSON );ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Missing a colon after a name of object member." at position 29.

复制代码

JSON_TYPE

用于查询 JSON 值类型的内置函数。该函数返回一个表示给定 JSON 值基本类型的字符串。

语法结构：

JSON_TYPE(value)

复制代码

"NULL"：如果路径指向的值是 JSON null。
"OBJECT"：如果路径指向的是一个 JSON 对象（键值对集合）。
"ARRAY"：如果路径指向的是一个 JSON 数组。
"STRING"：如果路径指向的是一个 JSON 字符串。
"NUMBER"：如果路径指向的是一个 JSON 数字（包括整数和浮点数）。
"TRUE" 或 "FALSE"：如果路径指向的是布尔值 true 或 false。

mysql> SELECT JSON_TYPE(NULL) AS json_null, JSON_TYPE('["a", "b","c"]') AS json_array, JSON_TYPE('{"name":"CoderAcademy", "age": 30}') AS json_object;+-----------+------------+-------------+| json_null | json_array | json_object |+-----------+------------+-------------+| NULL      | ARRAY      | OBJECT      |+-----------+------------+-------------+1 row in set (0.00 sec)

复制代码

2、合并 JSON

JSON_MERGE_PRESERVE

用于合并两个或多个 JSON 文档的函数。并保留所有键值对。

语法结构：

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

复制代码

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') AS json_value;+----------------------------+| json_value                 |+----------------------------+| ["a", 1, {"key": "value"}] |+----------------------------+1 row in set (0.00 sec)

复制代码

在处理重复键时，如果是合并对象(JOSN_OBJECT)，将 value 自动包装为数组，并通过组合值的方式合并数组。

mysql> SELECT JSON_MERGE_PRESERVE('{"name":"CoderAcademy", "age": 30}', '{"name":"CoderAcademy", "age": 35}') AS json_value;+-------------------------------------------------------------+| json_value                                                  |+-------------------------------------------------------------+| {"age": [30, 35], "name": ["CoderAcademy", "CoderAcademy"]} |+-------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

如果是合并数组(JSON_ARRAY)，将它们的值组合成一个数组，作为结果中该键的值。

mysql> SELECT JSON_MERGE_PRESERVE('{"hobbies":["Java", "Mysql"]}', '{"hobbies":["Python", "Mysql"]}') AS json_value;+---------------------------------------------------+| json_value                                        |+---------------------------------------------------+| {"hobbies": ["Java", "Mysql", "Python", "Mysql"]} |+---------------------------------------------------+1 row in set (0.00 sec)

复制代码

MySQL 8.0.3 以后支持

JSON_MERGE_PATCH

用于合并两个或多个 JSON 文档的函数。仅保留最后一个值。

语法格式如下：

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

复制代码

mysql> SELECT JSON_MERGE_PATCH('["a", 1]', '{"key": "value"}') AS json_value;+------------------+| json_value       |+------------------+| {"key": "value"} |+------------------+1 row in set (0.00 sec)
mysql> SELECT JSON_MERGE_PATCH('{"name":"CoderAcademy", "age": 30}', '{"name":"CoderAcademy", "age": 35}') AS json_value;+-------------------------------------+| json_value                          |+-------------------------------------+| {"age": 35, "name": "CoderAcademy"} |+-------------------------------------+1 row in set (0.00 sec)

复制代码

MySQL 8.0.3 以后支持

3、JSON 搜索

JSON_CONTAINS

MySQL8.0 中引入的用于检查 JSON 数组或对象是否包含特定值或键值对的函数。语法格式如下：

JSON_CONTAINS(json_doc, candidate[, path])

复制代码

其中json_doc是要检查的 JSON 文档，通常是一个列名或者 JSON 字符串表达式。candidate是要查找的值。而path（可选）指定在 JSON 文档中的路径，用于更精确地定位要检查的元素，如果没有指定路径，则在整个 JSON 文档中搜索给定的候选值。JSON_CONTAINS函数返回1表示包含，返回0表示不包含。

mysql> SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a') AS contains_value;+----------------+| contains_value |+----------------+|              1 |+----------------+1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('[ "apple", "banana", "cherry" ]', '"apple"') AS contains_apple;+----------------+| contains_apple |+----------------+|              1 |+----------------+1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS('{"fruits": ["apple", "banana", "cherry"]}', '"apple"', '$.fruits') AS contains_apple_in_path;+------------------------+| contains_apple_in_path |+------------------------+|                      1 |+------------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM test_json WHERE JSON_CONTAINS(json_data, '"apple"', '$.fruits');+----+-------------------------------------------+---------------------+| id | json_data                                 | create_time         |+----+-------------------------------------------+---------------------+|  3 | {"fruits": ["apple", "banana", "cherry"]} | 2024-02-05 07:28:40 |+----+-------------------------------------------+---------------------+1 row in set (0.00 sec)

复制代码

特别需要注意的是：在使用JSON_CONTAINS时，注意数据类型的匹配，确保值的类型与 JSON 中的类型一致。 JSON_CONTAINS 参数传递匹配值是''并不代表他是一个字符串，例如上述第一个例子：我们搜索1，使用'1'，搜索apple时，使用'"apple"'。

同时JSON_CONTAINS()不支持对嵌套 JSON 文档进行递归搜索，它只针对 JSON 路径指定的位置进行比较。

JSON_CONTAINS_PATH

用于检查 JSON 文档中是否存在指定路径的函数。与JSON_CONTAINS()函数不同，JSON_CONTAINS_PATH() 不检查路径对应的值是否匹配某个特定值，而是仅判断 JSON 文档内是否存在指定的路径结构。语法结构：

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

复制代码

json_doc是待检查的目标 JSON 文档。one_or_all是一个字符串参数，可以是 'one' 或 'all'，'one'表示只要存在任意一个提供的路径就返回 1（真），'all' 表示所有提供的路径都必须存在才返回 1（真）。path：一个或多个 JSON 路径表达式，用于在 JSON 文档内部定位要检查的路径。

mysql> SET @json_data = '{"name":"John","age":30,"hobbies":["reading","coding"],"address":{"city":"New York","country":"USA"}}';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_CONTAINS_PATH(@json_data, 'one', '$.name') AS has_name, JSON_CONTAINS_PATH(@json_data, 'one', '$.address.country') AS has_country, JSON_CONTAINS_PATH(@json_data, 'all', '$.hobbies[0]', '$.hobbies[1]') AS has_both_hobbies;+----------+-------------+------------------+| has_name | has_country | has_both_hobbies |+----------+-------------+------------------+|        1 |           1 |                1 |+----------+-------------+------------------+1 row in set (0.00 sec)
mysql> SELECT * FROM test_json WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.address.country');+----+------------------------------------------------------------------------------------------------------------------+---------------------+| id | json_data                                                                                                        | create_time         |+----+------------------------------------------------------------------------------------------------------------------+---------------------+|  2 | {"age": 30, "name": "John", "address": {"city": "New York", "country": "USA"}, "hobbies": ["reading", "coding"]} | 2024-02-05 07:25:47 |+----+------------------------------------------------------------------------------------------------------------------+---------------------+1 row in set (0.00 sec)

复制代码

JSON_EXTRACT

从 JSON 字段中提取指定路径的值。语法格式：

JSON_EXTRACT(json_doc, path[, path] ...)

复制代码

json_doc是包含 JSON 数据的列名或者直接的 JSON 字符串。path是一个或多个以逗号分隔的 JSON 路径表达式，用于指定要提取的值的位置。路径可以使用点.和方括号[]来表示对象属性和数组索引。

mysql> SET @user_info = '{"name":"John Doe","age":30,"hobbies":["reading","coding"],"address":{"street":"123 Main St","city":"New York"}}';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT    ->   JSON_EXTRACT(@user_info, '$.name') AS name,    ->   JSON_EXTRACT(@user_info, '$.age') AS age,    ->   JSON_EXTRACT(@user_info, '$.hobbies[0]') AS first_hobby,    ->   JSON_EXTRACT(@user_info, '$.address.city') AS city;+------------+------+-------------+------------+| name       | age  | first_hobby | city       |+------------+------+-------------+------------+| "John Doe" | 30   | "reading"   | "New York" |+------------+------+-------------+------------+1 row in set (0.00 sec)
mysql> SELECT JSON_EXTRACT(json_data, '$.name')            AS name,    ->        JSON_EXTRACT(json_data, '$.age')             AS age,    ->        JSON_EXTRACT(json_data, '$.hobbies[0]')      AS first_hobby,    ->        JSON_EXTRACT(json_data, '$.address.city') AS city    -> FROM test_json    -> WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.name');+------------+------+-------------+------------+| name       | age  | first_hobby | city       |+------------+------+-------------+------------+| "John Doe" | 30   | "reading"   | "New York" || "John"     | 30   | "reading"   | "New York" |+------------+------+-------------+------------+2 rows in set (0.00 sec)

复制代码

我们介绍一下 path 中的一些规则：

点.运算符

用于访问嵌套的对象属性。例如：$.name 表示访问顶级对象的 "name" 属性。

方括号[]运算符

用于访问数组元素。对于数组索引，使用数字表示位置，从 0 开始。例如：$.hobbies[0] 表示访问顶级对象 "hobbies" 数组的第一个元素。

多路径查询

在一个函数调用中可以指定多个路径，每个路径之间用逗号分隔。例如：JSON_EXTRACT(json_column, '$.path1', '$.path2')。

JSON_KEYS

用于从 JSON 文档中提取所有键（key）的一个函数，它返回一个包含 JSON 对象所有键名的数组。这对于需要获取或操作 JSON 对象内部属性名称时非常有用。语法结构：

JSON_KEYS(json_doc[, path])

复制代码

json_doc是包含 JSON 数据的列名或者直接的 JSON 字符串。path（可选参数）如果提供了路径，则只返回该路径指向的对象内的键。例如，若要提取嵌套在 JSON 文档内某个对象的所有键，可以指定到该对象的路径。

mysql> SELECT JSON_KEYS(json_data) AS top_level_keys FROM test_json;+---------------------------------------+| top_level_keys                        |+---------------------------------------+| ["age", "name", "address", "hobbies"] || ["age", "name", "address", "hobbies"] || ["fruits"]                            |+---------------------------------------+3 rows in set (0.00 sec)
mysql> SELECT JSON_KEYS(json_data, '$.address') AS address_keys FROM test_json WHERE JSON_CONTAINS_PATH(json_data, 'one', '$.address');+---------------------+| address_keys        |+---------------------+| ["city", "street"]  || ["city", "country"] |+---------------------+2 rows in set (0.00 sec)

复制代码

JSON_OVERLAPS

该函数会检查两个 JSON 对象，并返回布尔值。如果至少有一个键存在于两个对象中且对应值相等，则返回真（1），否则返回假（0）。这个函数并不关注两个 JSON 对象的所有内容，仅针对有交集的键值对进行比较。语法结构如下：

JSON_OVERLAPS(json_doc1, json_doc2)

复制代码

mysql> SET @doc1 = '{"name": "John", "age": 30}';Query OK, 0 rows affected (0.00 sec)
mysql> SET @doc2 = '{"name": "John", "address": "New York"}';Query OK, 0 rows affected (0.00 sec)
mysql> SELECT JSON_OVERLAPS(@doc1, @doc2) AS do_overlap;+------------+| do_overlap |+------------+|          1 |+------------+1 row in set (0.00 sec)

复制代码

需要注意的是，如果两个 JSON 对象没有共享的键，或者共享的键对应的值不相等，则此函数返回 0。此外，它只适用于 JSON 对象，对于 JSON 数组则不会进行这样的比较。

MySQL 8.0.17 以后才有这个函数

JSON_SEARCH

用于在 JSON 文档中搜索指定的字符串值，并返回找到该值的第一个匹配路径。这个函数对于从 JSON 数据结构中检索特定值非常有用。语法结构如下：

JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

复制代码

json_doc包含要搜索的 JSON 数据的列名或 JSON 字符串。one_or_all表示搜索模式，可以是 'one' 或 'all',如果是'one'（默认）则只要找到一个匹配就返回第一个匹配的路径，如果没有找到则返回 NULL，如果是'all'则返回所有匹配路径的 JSON 数组，如果没有找到则返回 NULL。search_str是要在 JSON 文档中查找的字符串值。escape_char（可选）代表如果搜索字符串中有特殊字符需要转义，可以提供一个转义字符。path（可选）可以指定一个或多个 JSON 路径，限制搜索范围到这些路径所指向的对象或数组元素。

mysql> SELECT    ->   JSON_SEARCH(json_data, 'one', 'John') AS name_path,    ->   JSON_SEARCH(json_data, 'all', 'New York') AS main_street_path    -> FROM test_json;+-----------+------------------+| name_path | main_street_path |+-----------+------------------+| NULL      | "$.address.city" || "$.name"  | "$.address.city" || NULL      | NULL             |+-----------+------------------+3 rows in set (0.00 sec)

复制代码

注意，JSON_SEARCH()主要适用于搜索字符串类型的值，在 MySQL 8.0 及以前版本中，它不支持直接搜索数值型或其他非字符串类型的内容。此外，该函数可能无法处理嵌套的 JSON 对象或数组内的复杂搜索场景，因为它只能返回单个键值对路径，而不是深度遍历整个 JSON 结构以寻找匹配项。

JSON_VALUE

用于从 JSON 文档中提取标量值的一个函数。它允许你从 JSON 数据结构中检索出符合 SQL 数据类型的特定键的值，并将其转换为一个可直接在 SQL 语句中使用的标准 SQL 数据类型（如字符串、数字或布尔值）。语法格式如下：

JSON_VALUE(json_doc, path)

复制代码

JSON_VALUE() 返回的是位于给定路径下的 JSON 文档中的标量值（即字符串、数字或布尔值），而不是 JSON 格式的值。这与 JSON_EXTRACT() 函数不同，后者返回的是 JSON 格式的值，即使提取的是标量值也是如此。

mysql> SELECT    ->   JSON_VALUE(json_data, '$.name') AS name,    ->   JSON_VALUE(json_data, '$.age') AS age,    ->   JSON_VALUE(json_data, '$.is_student') AS is_student    -> FROM test_json;+----------+------+------------+| name     | age  | is_student |+----------+------+------------+| John Doe | 30   | NULL       || John     | 30   | NULL       || NULL     | NULL | NULL       || John Doe | 30   | true       |+----------+------+------------+4 rows in set (0.01 sec)

复制代码

函数简化了 JSON 数据在 SQL 查询中的处理，特别是当你需要将 JSON 字段的值作为普通 SQL 数据类型进行比较、聚合或其他操作时。

MySQL 8.0.21 版本开始提供 JSON_VALUE

4、JSON 数据修改

JSON_ARRAY_APPEND

用于向 JSON 数组末尾追加元素的函数。这个函数允许你在现有的 JSON 数组中添加新的元素，无论是标量值还是嵌套的 JSON 对象或数组。语法格式：

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

复制代码

json_doc指包含 JSON 数据的列名或者直接的 JSON 字符串。path代表一个 JSON 路径表达式，指向要追加元素到其后的 JSON 数组。而value就是要追加到指定路径下 JSON 数组中的值，可以是标量值（如字符串、数字、布尔值），也可以是另一个 JSON 对象或数组。

mysql> SELECT json_data FROM test_json WHERE id = 3;+-------------------------------------------+| json_data                                 |+-------------------------------------------+| {"fruits": ["apple", "banana", "cherry"]} |+-------------------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_ARRAY_APPEND(json_data, '$.fruits', 'cherry')    -> WHERE id = 3;Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 3;+-----------------------------------------------------+| json_data                                           |+-----------------------------------------------------+| {"fruits": ["apple", "banana", "cherry", "cherry"]} |+-----------------------------------------------------+1 row in set (0.00 sec)

复制代码

这里有一点需要注意，如果path所指的对象不是一个数组，那么在进行追加操作之前，MySQL 会将该对象转换为一个只有一个元素的新数组。

mysql> SELECT json_data FROM test_json WHERE id = 5;+--------------------------------------------------------------+| json_data                                                    |+--------------------------------------------------------------+| {"fruits": ["apple", "banana", "cherry"], "hobbies": "Java"} |+--------------------------------------------------------------+1 row in set (0.01 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_ARRAY_APPEND(json_data, '$.hobbies', 'Python')    -> WHERE id = 5;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 5;+--------------------------------------------------------------------------+| json_data                                                                |+--------------------------------------------------------------------------+| {"fruits": ["apple", "banana", "cherry"], "hobbies": ["Java", "Python"]} |+--------------------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

此函数适用于需要动态修改和扩展数据库内存储的 JSON 数组的情况，特别是在处理具有可变长度列表的数据时特别有用。

JSON_ARRAY_INSERT

用于向 JSON 数组的特定位置插入元素的函数。这个函数允许你在现有的 JSON 数组的指定索引处插入一个新元素，这个元素可以是单个元素值、JSON 数组、JSON 对象。语法格式：

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

复制代码

json_doc代表包含 JSON 数据的列名或者直接的 JSON 字符串。而path是一个 JSON 路径表达式，指向要插入元素的 JSON 数组，并且可以指定要插入的位置（通过数组索引）。而value就是要插入到指定路径下 JSON 数组中的值，可以是普通值（如字符串、数字、布尔值），也可以是另一个 JSON 对象或数组。

mysql> SELECT json_data FROM test_json WHERE id = 6;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_ARRAY_INSERT(json_data, '$.fruits[0]', 'cherry')    -> WHERE id = 6;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 6;+--------------------------------------------------------------+| json_data                                                    |+--------------------------------------------------------------+| {"fruits": ["cherry", "apple", "banana"], "hobbies": "Java"} |+--------------------------------------------------------------+1 row in set (0.00 sec)
-- 插入一个数组mysql> UPDATE test_json    -> SET json_data = JSON_ARRAY_INSERT(json_data, '$.fruits[0]', CAST('["cherry", "orange"]' AS JSON))    -> WHERE id = 7;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 7;+--------------------------------------------------------------------------+| json_data                                                                |+--------------------------------------------------------------------------+| {"fruits": [["cherry", "orange"], "apple", "banana"], "hobbies": "Java"} |+--------------------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

JSON_INSERT

用于向 JSON 文档插入新键值对或替换已存在键的值的一个函数。语法格式如下：

JSON_INSERT(json_doc, path, val[, path, val] ...)

复制代码

如果在 JSON 文档中，路径已存在，则不会覆盖现有的文档值。

mysql> SELECT json_data FROM test_json WHERE id = 8;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_INSERT(json_data, '$.hobbies', 'Python')    -> WHERE id = 8;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 8;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)

复制代码

如果指定的路径不存在于原始 JSON 文档中，则会在该路径处创建新的键值对。

mysql> SELECT json_data FROM test_json WHERE id = 9;+---------------------------------+| json_data                       |+---------------------------------+| {"fruits": ["apple", "banana"]} |+---------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_INSERT(json_data, '$.hobbies', CAST('["Java", "Python"]' AS JSON),    ->     '$.name', 'CoderAcademy',    ->     '$.address', cast('{"city": "New York", "street": "123 Main St"}' AS JSON))    -> WHERE id = 9;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 9;+--------------------------------------------------------------------------------------------------------------------------------------------------+| json_data                                                                                                                                        |+--------------------------------------------------------------------------------------------------------------------------------------------------+| {"name": "CoderAcademy", "fruits": ["apple", "banana"], "address": {"city": "New York", "street": "123 Main St"}, "hobbies": ["Java", "Python"]} |+--------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

JSON_REMOVE

用于从 JSON 文档中删除数据。语法结构如下：

JSON_REMOVE(json_doc, path[, path] ...)

复制代码

json_doc代表包含 JSON 数据的列名或者直接的 JSON 字符串。path代表删除数据的路径。

如果路径存在，则会删除路径对应的元素，否则对数据没有影响，即不会删除，也不会报错。

mysql> SELECT json_data FROM test_json WHERE id = 10;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_REMOVE(json_data, '$.hobbies')    -> WHERE id = 10;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 10;+---------------------------------+| json_data                       |+---------------------------------+| {"fruits": ["apple", "banana"]} |+---------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_REMOVE(json_data, '$.fruits[1]')    -> WHERE id = 10;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 10;+-----------------------+| json_data             |+-----------------------+| {"fruits": ["apple"]} |+-----------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_REMOVE(json_data, '$.fruits[1]')    -> WHERE id = 10;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 10;+-----------------------+| json_data             |+-----------------------+| {"fruits": ["apple"]} |+-----------------------+1 row in set (0.00 sec)

复制代码

JSON_REPLACE

函数用于替换 JSON 文档中的现有值。语法结构如下：

JSON_REPLACE(json_doc, path, val[, path, val] ...)

复制代码

json_doc代表包含 JSON 数据的列名或者直接的 JSON 字符串。path代表替换数据的路径。val代表要替换的值。

如果替换路径在文档中存在，则就用新值覆盖文档中原值，否则不会替换，也不会报错。

mysql> SELECT json_data FROM test_json WHERE id = 11;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.01 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_REPLACE(json_data, '$.name', 'CoderAcademy')    -> WHERE id = 11;Query OK, 0 rows affected (0.00 sec)Rows matched: 1  Changed: 0  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 11;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)

mysql> UPDATE test_json    -> SET json_data = JSON_REPLACE(json_data, '$.fruits[1]', 'orange', '$.hobbies',  CAST('["Java", "Python"]' AS JSON))    -> WHERE id = 11;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 11;+----------------------------------------------------------------+| json_data                                                      |+----------------------------------------------------------------+| {"fruits": ["apple", "orange"], "hobbies": ["Java", "Python"]} |+----------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

JSON_SET

用于在 JSON 文档中插入或更新数据。语法格式如下：

JSON_SET(json_doc, path, val[, path, val] ...)

复制代码

json_doc代表包含 JSON 数据的列名或者直接的 JSON 字符串。path代表替换数据的路径。val代表要插入或更新的新值。

如果路径在文档中已存在，则会覆盖原文档中值，如果不存在，则插入新值。

mysql> SELECT json_data FROM test_json WHERE id = 12;+----------------------------------------------------+| json_data                                          |+----------------------------------------------------+| {"fruits": ["apple", "banana"], "hobbies": "Java"} |+----------------------------------------------------+1 row in set (0.00 sec)
mysql> UPDATE test_json    -> SET json_data = JSON_SET(json_data, '$.fruits[1]', 'orange',    ->     '$.hobbies', CAST('["Java", "Python"]' AS JSON),    ->     '$.name', 'CoderAcademy',    ->     '$.address', cast('{"city": "New York", "street": "123 Main St"}' AS JSON))    -> WHERE id = 12;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT json_data FROM test_json WHERE id = 12;+--------------------------------------------------------------------------------------------------------------------------------------------------+| json_data                                                                                                                                        |+--------------------------------------------------------------------------------------------------------------------------------------------------+| {"name": "CoderAcademy", "fruits": ["apple", "orange"], "address": {"city": "New York", "street": "123 Main St"}, "hobbies": ["Java", "Python"]} |+--------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

复制代码

JSON 类型列的索引

在 MySQL 8.0 中，JSON 类型的索引有一些重要的改进和新特性。JSON 类型的索引使得在处理包含 JSON 数据的列时，查询的性能得到了显著提升。

JSON 路径索引

MySQL 8.0 引入了 JSON 路径索引，允许在 JSON 对象的特定路径上创建索引，以便更高效地执行 JSON 路径查询。路径索引允许在 JSON 对象中的特定路径上进行范围查询、排序和过滤。我们以查询地址信息中country等于"US"为例，他有这几中写法：

-- 使用JSON_CONTAINSSELECT * FROM user_info WHERE JSON_CONTAINS(address, '"US"', '$.country');
-- 使用JSON_VALUESELECT * FROM user_info WHERE JSON_VALUE(address, '$.country') = "US";
-- 使用JSON_EXTRACTSELECT * FROM user_info WHERE JSON_EXTRACT(address, '$.country') = 'US';
-- 使用 ->> 运算符SELECT * FROM user_info WHERE address->>"$.country" = "US";-- 或者SELECT * FROM user_info WHERE CAST(address->>"$.country" AS CHAR(30)) = "US";

复制代码

在 JSON 类型字段上创建索引时，要遵守的规则是要确保索引表达式与查询时的条件表达式匹配，这样 MySQL 才能正确地使用索引进行优化查询。

所以针对不同的 sql 查询，我们提供不同的索引。例如：

使用 JSON_EXTRACT

我们可以采取新增一个虚拟列的方式去使用索引，比如我们新增一个country的虚拟列，然后在虚拟列上增加索引

-- 添加生成的虚拟列  ALTER TABLE user_info  ADD COLUMN country VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(address, '$.country'))) STORED;    -- 在生成的列上创建索引  CREATE INDEX idx_json_country ON user_info(country);

复制代码

我们执行一下计划：

mysql> EXPLAIN SELECT * FROM user_info WHERE country = 'US';+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_country | idx_json_country | 1023    | const |    2 |   100.00 | NULL  |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)

复制代码

可以看出使用了索引idx_json_country。

使用 ->> 运算符

在 JSON 类型的索引中，使用 ->> 运算符，会转换为 JSON_UNQUOTE(JSON_EXTRACT(...))，而 JSON_UNQUOTE() 返回的值具有 LONGTEXT 数据类型。MySQL 不能对没有键部分上的前缀长度指定的 LONGTEXT 列建立索引，而在功能性键部分中又不允许指定前缀长度。

我们可以这样创建索引：

CREATE INDEX idx_json_country_cast ON user_info((CAST(address->>"$.country" AS CHAR(30)) COLLATE utf8mb4_bin));

复制代码

然后看一下执行计划：

mysql> EXPLAIN SELECT * FROM user_info WHERE address->>"$.country" = "US";+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_country | idx_json_country_cast | 123    | const |    2 |   100.00 | NULL  |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)

复制代码

多值索引

多值索引是 MySQL 8.0.17 版本引入的新功能，它允许在 InnoDB 存储引擎中创建索引来支持对存储数组值的列进行高效查询。传统的索引是一对一的，而多值索引允许在一个数据记录上拥有多个索引记录。多值索引主要用于索引 JSON 数组。

要创建多值索引，可以在 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 语句中使用 CAST(... AS ... ARRAY) 函数来定义。这将把 JSON 数组中的同类型标量值转换为 SQL 数据类型数组。然后，MySQL 会在这个 SQL 数据类型数组上创建一个虚拟列，并在虚拟列上创建一个功能性索引。最终，这个功能性索引构成了多值索引。

例如，我们在address中增加一个zipcode列用于存储地址邮编，每个地址包含若干个邮编。我们对这个zipcode就可以使用多值索引。

mysql> ALTER TABLE user_info ADD INDEX idx_json_zipcode((CAST(address->'$.zipcode' AS SIGNED ARRAY)));Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

复制代码

我们分别执行以下 sql 进行验证：

mysql> SELECT * FROM user_info WHERE 94507 MEMBER OF(address->'$.zipcode');+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+| id | user_name | address                                                                                          | create_time         |+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+|  2 | lisi      | {"city": "shanghai", "street": "123 Main St", "country": "CN", "zipcode": [94568, 94507, 94582]} | 2024-02-05 11:08:22 ||  3 | wangwu    | {"city": "guangzhou", "street": "123 Main St", "country": "CN", "zipcode": [94477, 94507]}       | 2024-02-05 11:08:22 ||  4 | qianliu   | {"city": "New York", "street": "123 Main St", "country": "US", "zipcode": [94507, 94582]}        | 2024-02-05 11:08:22 |+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+3 rows in set (0.01 sec)
mysql> EXPLAIN    -> SELECT * FROM user_info WHERE 94507 MEMBER OF(address->'$.zipcode');+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra       |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | user_info | NULL       | ref  | idx_json_zipcode | idx_json_zipcode | 9       | const |    3 |   100.00 | Using where |+----+-------------+-----------+------------+------+------------------+------------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

复制代码

可以看出使用了索引。

mysql> SELECT * FROM user_info WHERE JSON_CONTAINS(address->'$.zipcode', CAST('[94507,94582]' AS JSON));+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+| id | user_name | address                                                                                          | create_time         |+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+|  2 | lisi      | {"city": "shanghai", "street": "123 Main St", "country": "CN", "zipcode": [94568, 94507, 94582]} | 2024-02-05 11:08:22 ||  4 | qianliu   | {"city": "New York", "street": "123 Main St", "country": "US", "zipcode": [94507, 94582]}        | 2024-02-05 11:08:22 |+----+-----------+--------------------------------------------------------------------------------------------------+---------------------+2 rows in set (0.01 sec)
mysql> EXPLAIN    -> SELECT * FROM user_info WHERE JSON_CONTAINS(address->'$.zipcode', CAST('[94507,94582]' AS JSON));+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+| id | select_type | table     | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+|  1 | SIMPLE      | user_info | NULL       | range | idx_json_zipcode | idx_json_zipcode | 9       | NULL |    6 |   100.00 | Using where |+----+-------------+-----------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

复制代码

多值索引还可以作为复合索引的一部分进行定义。在复合索引中，只能有一个多值键部分，并且可以与其他单值部分一起使用。多值键部分可以按任意顺序使用。

mysql> ALTER TABLE user_info ADD INDEX idx_name_zipcode(user_name, (CAST(address->'$.zipcode' AS SIGNED ARRAY)));Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0

复制代码

总结

MySQL 提供了丰富的内置函数支持 JSON 数据类型的存储和操作，如检查、提取、插入、更新及搜索 JSON 文档中的内容。此外，为了提高含有 JSON 字段的查询效率，用户可以在满足条件的情况下为 JSON 特定路径创建索引，如使用虚拟生成列或者 MySQL 8.0 以上的原生 JSON 路径索引功能。多值索引特别适用于 JSON 数组元素的检索优化，使得数据库能针对数组内的每个独立值建立索引记录，提升复杂查询场景下的性能表现。

我们可以了解到 JSON 数据类型的灵活性、易用性以及适用性，以及如何在实际项目中充分利用它来应对动态数据模型、半结构化数据存储和查询等方面的挑战。

文中示例表结构与数据

CREATE TABLE `test_json`  (      `id`          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',      `json_data`     JSON COMMENT 'json值',      `create_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',      PRIMARY KEY ( `id` )  ) ENGINE = INNODB    AUTO_INCREMENT = 1    DEFAULT CHARSET = utf8mb4 COMMENT = 'json测试表';

CREATE TABLE `user_info`  (      `id`          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',      `user_name` varchar(64) NOT NULL DEFAULT '' COMMENT '名称',      `address`     JSON COMMENT '地址信息',      `create_time` datetime            NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',      PRIMARY KEY ( `id` )  ) ENGINE = INNODB    AUTO_INCREMENT = 1    DEFAULT CHARSET = utf8mb4 COMMENT = '用户信息';

复制代码

文章转载自：码农Academy
原文链接：https://www.cnblogs.com/coderacademy/p/18020446
体验地址：http://www.jnpfsoft.com/?from=001

发布于: 20 小时前阅读数: 3

不在线第一只蜗牛

关注

还未添加个人签名 2023-06-19 加入

还未添加个人简介

发布

暂无评论

创作场景

解锁 Mysql 中的 JSON 数据类型，怎一个爽字了得

引言

JSON 数据类型的特性

JSON 操作函数

1、创建 JSON 值

2、合并 JSON

3、JSON 搜索

4、JSON 数据修改

JSON 类型列的索引

JSON 路径索引

多值索引

总结

文中示例表结构与数据

不在线第一只蜗牛

评论