本文共 14902 字，大约阅读时间需要 49 分钟。
MySQL 从 5.7.8 版本开始引入了 JSON 数据类型，为开发者提供了对 JSON 文档进行操作的强大功能。这些函数涵盖了增删改查（CRUD）操作，能够帮助开发者更高效地处理 JSON 数据。本文将深入探讨 MySQL 提供的 JSON 函数，包括创建 JSON、查询 JSON、修改 JSON 等操作。
可以使用 json_array 函数创建一个 JSON 数组。该函数接受多个参数，每个参数将被转换为数组中的元素。
mysql> select json_array(1, 'json', true, null, now());+-------------------------------------------------------+| json_array(1, 'json', true, null, now()) |+-------------------------------------------------------+| [1, "json", true, null, "2021-02-21 22:19:57.000000"] |+-------------------------------------------------------+1 row in set (0.01 sec)
json_object 函数用于创建一个 JSON 对象。该函数接受多个 key-value 对，key 和 value 将被转换为 JSON 对象。
mysql> select json_object('name', '张三', 'age', 24);+---------------------------------------+| json_object('name', '张三', 'age', 24) |+---------------------------------------+| { "age": 24, "name": "张三"} |+---------------------------------------+1 row in set (0.00 sec) json_contains 函数json_contains 函数用于判断 target 是否存在于 source 中。path 参数可选，默认为整个 JSON 文档。如果 path 不存在或为 null，则返回 null，否则返回 1 或 0。
## 创建一个 JSON 数组对象mysql> select json_array(1, 2, 'abc', true, null);+---------------------------------+| json_array(1, 2, 'abc', true, null) |+---------------------------------+| [1, 2, "abc", true, null] |+---------------------------------+1 row in set (0.00 sec)
mysql> select json_contains('[1, 2, "abc", true, null]', '"abc"]');+----------------------------------------------------+| json_contains('[1, 2, "abc", true, null]', '"abc"') |+----------------------------------------------------+| 1 |+----------------------------------------------------+1 row in set (0.00 sec) mysql> select json_contains('[1, 2, "abc", true, null]', 'true');+---------------------------------------------------+| json_contains('[1, 2, "abc", true, null]', 'true') |+---------------------------------------------------+| 1 |+---------------------------------------------------+1 row in set (0.00 sec) mysql> select json_contains('[1, 2, "abc", true, null]', 'null');+---------------------------------------------------+| json_contains('[1, 2, "abc", true, null]', 'null') |+---------------------------------------------------+| 1 |+---------------------------------------------------+1 row in set (0.00 sec) mysql> select json_contains('[1, 2, "abc", true, null]', '520');+--------------------------------------------------+ | json_contains('[1, 2, "abc", true, null]', '520') |+--------------------------------------------------+ | 0 |+--------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_contains('[1, 2, "abc", true, null]', '[1, 2, "abc"]');+----------------------------------------------------------+| json_contains('[1, 2, "abc", true, null]', '[1, 2, "abc"]') |+----------------------------------------------------------+| 1 |+----------------------------------------------------------+1 row in set (0.00 sec) path 参数## 创建一个 JSON 对象并存储在 Mysql 变量中mysql> set @name1 = '{"age": 23, "name": "张三"}';Query OK, 0 rows affected (0.00 sec)## 判断路径 $.name 是否存在值 "张三"mysql> select json_contains(@name1, '"张三"', '$.name');+-------------------------------------------+| json_contains(@name1, '"张三"', '$.name') |+-------------------------------------------+| 1 |+-------------------------------------------+1 row in set (0.00 sec) json_contains_path 函数json_contains_path 函数用于查询指定路径下是否存在匹配项。第二个参数可以是 'one' 或 'all'，分别表示匹配一个或多个路径。
## 创建并查询 JSON 对象mysql> set @name1 = '{"age": 23, "name": "张三"}';Query OK, 0 rows affected (0.00 sec)## 匹配 name 或 xx 路径mysql> select json_contains_path(@name1, 'one', '$.name', '$.xx');+--------------------------------------------------+ | json_contains_path(@name1, 'one', '$.name', '$.xx') |+--------------------------------------------------+ | 1 |+--------------------------------------------------+ 1 row in set (0.00 sec) ## 匹配 name 和 age 路径mysql> select json_contains_path(@name1, 'all', '$.name', '$.age');+---------------------------------------------------+| json_contains_path(@name1, 'all', '$.name', '$.age') |+---------------------------------------------------+| 1 |+---------------------------------------------------+1 row in set (0.00 sec)
json_extract 函数json_extract 函数用于从 JSON 文档中提取特定路径的值。路径可以使用索引或 key 来指定。
## 创建一个嵌套 JSON 数组mysql> select json_array('1', '2', json_array('3', '4'));+-----------------------------------------+| json_array('1', '2', json_array('3', '4')) |+-----------------------------------------+| ["1", "2", ["3", "4"]] |+-----------------------------------------+1 row in set (0.00 sec) ## 提取数组中的第 0 个和第 2 个元素mysql> select json_extract('["1", "2", ["3", "4"]', '$[0]', '$[2]');+------------------------------------------------------+ | json_extract('["1", "2", ["3", "4"]', '$[0]', '$[2]') |+------------------------------------------------------+ | ["1", ["3", "4"]] |+------------------------------------------------------+ 1 row in set (0.00 sec) MySQL 5.7.9 及以上版本支持使用简化语法：
## 创建包含 JSON 类型的表mysql> create table json_demo(content json);Query OK, 0 rows affected (0.01 sec)## 插入 JSON 数据mysql> insert into json_demo values(@name1);Query OK, 1 row affected (0.01 sec)## 查询数据mysql> select * from json_demo;+-------------------------------+| content |+-------------------------------+| { "age": 23, "name": "张三"} |+-------------------------------+1 row in set (0.00 sec) ## 提取特定路径的值mysql> select content, json_extract(content, '$.name') from json_demo where json_extract(content, '$.name') = '张三';+-------------------------------+--------------------------------+| content | json_extract(content, '$.name') |+-------------------------------+--------------------------------+| { "age": 23, "name": "张三"} | "张三" |+-------------------------------+--------------------------------+1 row in set (0.00 sec) json_keys 函数json_keys 函数用于获取指定路径下所有的键。
## 创建一个嵌套 JSON 对象mysql> select json_object('name', '张三', 'age', '23', 'account', json_object('accountId', '123456', 'accountBank', '招商银行'));+------------------------------------------------------------------------------------------------------------------+ | json_object('name', '张三', 'age', '23', 'account', json_object('accountId', '123456', 'accountBank', '招商银行')) |+------------------------------------------------------------------------------------------------------------------+ | { "age": "23", "name": "张三", "account": { "accountId": "123456", "accountBank": "招商银行"}} |+------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ## 获取所有键mysql> select json_keys(@name2);+----------------------------+| json_keys(@name2) |+----------------------------+| ["age", "name", "account"] |+----------------------------+1 row in set (0.00 sec)
## 获取指定路径下的键mysql> select json_keys(@name2, '$.account');+-------------------------------+| json_keys(@name2, '$.account') |+-------------------------------+| ["accountId", "accountBank"] |+-------------------------------+1 row in set (0.00 sec)
json_search 函数json_search 函数用于在 JSON 文档中搜索特定模式的键。search_str 可以使用类似 SQL 的 like 模糊匹配。
## 查询 JSON 中至少一个内容是 23 的键mysql> select json_search(@name2, 'one', '23');+--------------------------------+ | json_search(@name2, 'one', '23') |+--------------------------------+ | "$.age" |+--------------------------------+ 1 row in set (0.00 sec)
## 查询 JSON 中至少一个内容是以 "张" 开头的键mysql> select json_search(@name2, 'one', '张%');+----------------------------------+ | json_search(@name2, 'one', '张%') |+----------------------------------+ | "$.name" |+----------------------------------+ 1 row in set (0.00 sec)
## 查询 JSON 中包含 2 的所有键mysql> select json_search(@name2, 'all', '%2%');+----------------------------------+ | json_search(@name2, 'all', '%2%') |+----------------------------------+ | ["$.age", "$.account.accountId"] |+----------------------------------+ 1 row in set (0.00 sec)
## 创建一个 JSON 数组mysql> select json_array('1', '2', true, null, 'abc');+-------------------------------------+| json_array('1', '2', true, null, 'abc') |+-------------------------------------+| ["1", "2", true, null, "abc"] |+-------------------------------------+1 row in set (0.00 sec) ## 在 JSON 数组中追加元素mysql> set @v1 = '[ "1", "2", true, null, "abc"]';Query OK, 0 rows affected (0.00 sec)## 追加到数组的第一个元素mysql> select json_array_append(@v1, '$[0]', 'haha');+---------------------------------------------------------+| json_array_append(@v1, '$[0]', 'haha') |+---------------------------------------------------------+| ["1", "haha", "2", true, null, "abc"] |+---------------------------------------------------------+1 row in set (0.00 sec)
json_array_append 函数json_array_append 函数用于在指定路径的 JSON 数组尾部追加元素。如果路径指向一个 JSON 对象，则会将其封装在一个新数组中。
## 在路径 $[0] 追加元素mysql> select json_array_append(@v1, '$[0]', 'haha');+---------------------------------------------------------+| json_array_append(@v1, '$[0]', 'haha') |+---------------------------------------------------------+| ["1", "haha", "2", true, null, "abc"] |+---------------------------------------------------------+1 row in set (0.00 sec)
## 在嵌套路径 $[0][1] 追加元素mysql> set @v2 = '[ ["1", "haha", "xixi"], "2", true, null, "abc"]';Query OK, 0 rows affected (0.00 sec)## 追加到路径 $[0][1]mysql> select json_array_append(@v2, '$[0][1]', 'ai');+---------------------------------------------------------+| json_array_append(@v2, '$[0][1]', 'ai') |+---------------------------------------------------------+| [["1", ["haha", "ai"], "xixi"], "2", true, null, "abc"] |+---------------------------------------------------------+1 row in set (0.00 sec)
json_array_insert 函数json_array_insert 函数用于在指定路径的 JSON 数组中插入元素。插入位置从后往前移动。
## 在路径 $[0] 插入元素mysql> select json_array_insert('[ "1", "2", ["3", "4"]]', '$[0]', 'x');+--------------------------------------------------------+| json_array_insert('[ "1", "2", ["3", "4"]]', '$[0]', 'x') |+--------------------------------------------------------+| ["x", "1", "2", ["3", "4"]] |+--------------------------------------------------------+1 row in set (0.00 sec) json_replace 函数json_replace 函数用于替换指定路径的值。如果路径不存在，则忽略。
## 替换数组中的元素mysql> select json_replace('[ "1", "2", ["3", "4"]]', '$[0]', 'y');+------------------------------+| json_replace('[ "1", "2", ["3", "4"]]', '$[0]', 'y') |+------------------------------+| ["y", "1", "2", ["3", "4"]] |+------------------------------+1 row in set (0.00 sec) ## 替换 JSON 对象中的值mysql> select json_replace('{"age": "23", "name": "张三"}', '$.age', '24');+----------------------------------------------------+| json_replace('{"age": "23", "name": "张三"}', '$.age', '24') |+----------------------------------------------------+| {"age": "24", "name": "张三"} |+----------------------------------------------------+1 row in set (0.00 sec) json_set 函数json_set 函数用于设置指定路径的值。如果路径不存在，则在尾部添加新的 key-value 对。
## 设置路径 $.age11 的值mysql> select json_set('{"age": "23"}', '$.age11', '24');+-------------------------------------------------------------------------------------------------------------------+| json_set('{"age": "23"}', '$.age11', '24') |+-------------------------------------------------------------------------------------------------------------------+| {"age": "23", "age11": "24"} |+-------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) json_merge_preserve 函数json_merge_preserve 函数用于合并两个 JSON 文档。合并规则如下：
## 合并两个 JSON 数组mysql> select json_merge_preserve('[ "a", "b", "c"]', '[ "x", "1", "2", ["3", "4"]]');+--------------------------------------------------+ | json_merge_preserve('[ "a", "b", "c"]', '[ "x", "1", "2", ["3", "4"]]') |+--------------------------------------------------+ | ["a", "b", "c", "x", "1", "2", ["3", "4"]] |+--------------------------------------------------+ 1 row in set (0.00 sec) ## 合并两个 JSON 对象mysql> select json_merge_preserve('{"name": "张三"}', '{"company": "神码"}');+-------------------------------------------------------------------------------------------------------------------------+ | json_merge_preserve('{"name": "张三"}', '{"company": "神码"}') |+-------------------------------------------------------------------------------------------------------------------------+ | {"name": "张三", "company": "神码"} |+-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ## 合并嵌套结构mysql> select json_merge_preserve('{"x": "value"}', '{"name": "张三"}');+---------------------------------------------------------------------------------------------------------------------------------+ | json_merge_preserve('{"x": "value"}', '{"name": "张三"}') |+---------------------------------------------------------------------------------------------------------------------------------+ | {"x": "value", "name": "张三"} |+---------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) json_remove 函数json_remove 函数用于移除指定路径的值。如果路径不存在，则忽略。
## 移除数组中的元素mysql> select json_remove('[ "1", ["3", "4"], "2"]', '$[0]', '$[1]');+--------------------------------+ | json_remove('[ "1", ["3", "4"], "2"]', '$[0]', '$[1]') |+--------------------------------+ | ["1", ["3", "4"]] |+--------------------------------+ 1 row in set (0.00 sec) 通过以上内容，开发者可以更高效地使用 MySQL 的 JSON 函数来处理 JSON 数据。这些函数涵盖了从创建到查询、修改的多种操作，能够满足复杂的 JSON 处理需求。
转载地址：http://lenwz.baihongyu.com/