骑驴找蚂蚁

全干工程师

2023/11/19 10:26

mysql JSON更新、插入、查询语法的最佳实践

MySQL 从 5.7.8 版本开始支持 JSON 类型。我看到很多关于如何选择的文章，但是有一些关于实际更新和插入、查询的内容很短，所以我就在这里总结一下。

开始前

创建一张带有json类型字段的表.

create  table  example  (id  int,  a json);

插入一条a列为null的测试数据.

mysql> insert into example values (1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from example;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

插入

JSON_OBJECT方法

使用json_object生成json对象.

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

mysql> insert into example values (2, JSON_OBJECT('key1','val1', 'key2', 'val2'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from example where id=2;
+------+----------------------------------+
| id   | a                                |
+------+----------------------------------+
|    2 | {"key1": "val1", "key2": "val2"} |
+------+----------------------------------+
1 row in set (0.01 sec)

如果你的键值为奇数个，你会得到一个错误。

mysql> insert into example values (3, JSON_OBJECT('key1','val1', 'key2', 'val2', 'aaa'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

JSON_ARRAY方法

使用json_array来放置一个数组。数值、字符串和 mysql 函数可一起使用。

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

mysql> insert into example values (4, JSON_ARRAY('aaa','bbb', 1, now()));
Query OK, 1 row affected (0.00 sec)
mysql> select * from example where id=4;
+------+-------------------------------------------------+
| id   | a                                               |
+------+-------------------------------------------------+
|    4 | ["aaa", "bbb", 1, "2020-01-22 14:40:42.000000"] |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

可以把json_object放入json_array一起使用.

mysql> insert into example values 
(5, JSON_ARRAY('aaa','bbb', 1, now(), JSON_OBJECT('key1','val1', 'key2', 'val2')));
Query OK, 1 row affected (0.00 sec)

mysql> select * from example where id=5;
+------+-----------------------------------------------------------------------------------+
| id   | a                                                                                 |
+------+-----------------------------------------------------------------------------------+
|    5 | ["aaa", "bbb", 1, "2020-01-22 14:42:25.000000", {"key1": "val1", "key2": "val2"}] |
+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

更新

JSON_MERGE方法

如果你想将键值添加到JSON对象中使用json_merge.

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

-- 两者都可以。
update example set a=json_merge(a,  json_object('key3', 'val3')) where id=2;
update example set a=json_merge(a, '{"key4": "val4"}') where id=2;

mysql> select * from example where id=2;
+------+------------------------------------------------------------------+
| id   | a                                                                |
+------+------------------------------------------------------------------+
|    2 | {"key1": "val1", "key2": "val2", "key3": "val3", "key4": "val4"} |
+------+------------------------------------------------------------------+

JSON_ARRAY_APPEND

如果你想将值添加到JSON数组中使用json_array_append.

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

对于路径，指定要从开始放置的层次结构等。[点击此处](https://dev.mysql.com/doc/refman/8.0/en/json.html#json−path−syntax)了解详情。这次，它会被放在第一层，所以只有开始放置的层次结构等。 [点击此处](https://dev.mysql.com/doc/refman/8.0/en/json.html\#json-path-syntax)了解详情。这次，它会被放在第一层，所以只有开始放置的层次结构等。[点击此处](https://dev.mysql.com/doc/refman/8.0/en/json.html#json−path−syntax)了解详情。这次，它会被放在第一层，所以只有。

mysql> update example set a=json_array_append(a, '$', 'hyoo') where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from example where id=4;
+------+---------------------------------------------------------+
| id   | a                                                       |
+------+---------------------------------------------------------+
|    4 | ["aaa", "bbb", 1, "2020-01-22 15:00:39.000000", "hyoo"] |
+------+---------------------------------------------------------+
1 row in set (0.00 sec)

您也可以通过更改路径来执行类似的操作.

mysql> update example set a=json_array_append(a, '$[3]', 'ttt') where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from example where id=4;
+------+------------------------------------------------------------------+
| id   | a                                                                |
+------+------------------------------------------------------------------+
|    4 | ["aaa", "bbb", 1, ["2020-01-22 15:00:39.000000", "ttt"], "hyoo"] |
+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

原数据为NULL

mysql> select * from example where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> update example set a= json_array_append(a, '$', 'hyo') where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from example where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

可以看到直接更新是不生效的. 我们需要使用ifnull来解决这个问题.

json_array示例

mysql> select * from example where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> update example set 
    -> a=ifnull(json_array_append(a, '$', 'hyo'), json_array('hyo'))
    -> where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from example where id=1;
+------+---------+
| id   | a       |
+------+---------+
|    1 | ["hyo"] |
+------+---------+
1 row in set (0.00 sec)

json_object示例


mysql> select * from example where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

mysql> update example set 
a=ifnull(
  json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), 
  json_array(json_object('comment','hogefuga','created_at', now()))
) where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from example where id=1;                                                                                                                                           +------+-----------------------------------------------------------------------+
| id   | a                                                                     |
+------+-----------------------------------------------------------------------+
|    1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}] |
+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> update example set 
a=ifnull(
  json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), 
  json_array(json_object('comment','hogefuga','created_at', now()))
) where id=1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from example where id=1;                                                                                                                                           +------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| id   | a                                                                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}, {"comment": "munya_munya", "created_at": "2020-01-22 15:08:48.000000"}] |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

查询

JSON_EXTRACT

如果需要从json串中提取值就需要使用JSON_EXTRACT函数来操作。

添加一些测试数据

mysql> create table sample(id int  null,text json null);
     > insert into `sample` (`id`, `text`) value(1, '{"id": 1, "label": "sample", "value": "test"}');
     > insert into `sample` (`id`, `text`) value(2, '{"id": 2, "label": "sample2", "value": "test2"}');
     > insert into `sample` (`id`, `text`) value(3, '{"id": 3, "label": "sample3", "value": "test3"}');

找出text字段中id大于等于2的数据.

mysql> select * from sample where JSON_EXTRACT(text, '$.id') >= 2;
+------+-------------------------------------------------+
| id   | text                                            |
+------+-------------------------------------------------+
|    2 | {"id": 2, "label": "sample2", "value": "test2"} |
|    3 | {"id": 3, "label": "sample3", "value": "test3"} |
+------+-------------------------------------------------+
2 rows in set (0.01 sec)

只显示json中指定的字段值

mysql> select json_extract(text, '$.value') from sample;
+-------------------------------+
| json_extract(text, '$.value') |
+-------------------------------+
| "test2"                       |
| "test"                        |
| "test3"                       |
+-------------------------------+
3 rows in set (0.00 sec)

在mysql版本>=5.7.9中有更快捷的方法来代替json_extract函数. 使用json->就可以提取访问值.

mysql> select text->'$.value' from sample;
+-----------------+
| text->'$.value' |
+-----------------+
| "test2"         |
| "test"          |
| "test3"         |
+-----------------+
3 rows in set (0.00 sec)

对提取的值做聚合(SUM, AVG, MIN, MAX)操作

mysql> select SUM(text->'$.id') from sample;
+-------------------+
| SUM(text->'$.id') |
+-------------------+
|                 6 |
+-------------------+
1 row in set (0.00 sec)

JSON_CONTAINS

使用json_contains来代替等于查询，mysql针对此函数有更好的优化。

mysql> select * from sample where JSON_CONTAINS(`text`, '"test2"', '$.value');
+------+-------------------------------------------------+
| id   | text                                            |
+------+-------------------------------------------------+
|    2 | {"id": 2, "label": "sample2", "value": "test2"} |
+------+-------------------------------------------------+
1 row in set (0.01 sec)

请注意查询语句中的'"text2"', 如果你查询的字段值类型是字符串形式需要以’""'来包含字符串. 在测试一次整数类型。

mysql> select * from sample where JSON_CONTAINS(`text`, '1', '$.id');
+------+-----------------------------------------------+
| id   | text                                          |
+------+-----------------------------------------------+
|    1 | {"id": 1, "label": "sample", "value": "test"} |
+------+-----------------------------------------------+
1 row in set (0.00 sec)

JSON_CONTAINS_PATH

json_contains只能查询json中路径的值包含某个值, 如果需要查询json包含某个路径就需要使用json_contains_path.

JSON_CONTAINS_PATH(_json_doc_,  _one_or_all_,  _path_[,  _path_] ...)

第一个参数是json字段，第二参数是one 或者all固定的两个值. 后面的参数就是需要查的路径.

‘one’：如果文档中至少存在一个路径，则为 1，否则为 0。
‘all’：如果文档中存在所有路径，则为 1，否则为 0。

来个示例说明下one和all的作用.

mysql> select * from sample where json_contains_path(`text`, 'one', '$.id', '$.error_field');
+------+-------------------------------------------------+
| id   | text                                            |
+------+-------------------------------------------------+
|    2 | {"id": 2, "label": "sample2", "value": "test2"} |
|    1 | {"id": 1, "label": "sample", "value": "test"}   |
|    3 | {"id": 3, "label": "sample3", "value": "test3"} |
+------+-------------------------------------------------+
3 rows in set (0.00 sec)

 mysql> select * from sample where json_contains_path(`text`, 'all', '$.id', '$.error_field');
Empty set (0.00 sec)

JSON_VALUE

此函数需要mysql版本8.0及以上

从指定定路径处的JSON文档中提取值，并返回提取的值，可以选择将其转换为所需的类型。

mysql> select JSON_VALUE(text,'$.value') from sample;
+----------------------------+
| JSON_VALUE(text,'$.value') |
+----------------------------+
| test2                      |
| test                       |
| test3                      |
+----------------------------+
3 rows in set (0.00 sec)

通过json_value提取的字符串类型的值它会取消". json_value相当于下面sql语句的组合形式.

SELECT  CAST(  JSON_UNQUOTE(  JSON_EXTRACT(_json_doc_,  _path_)  )  AS  _type_  );

如果想快速的取字符串的字面值，可以使用快捷语法. json->>path形式。

mysql> select text->>'$.value' from sample;
+------------------+
| text->>'$.value' |
+------------------+
| test2            |
| test             |
| test3            |
+------------------+
3 rows in set (0.01 sec)

开始前

插入

JSON_OBJECT方法

JSON_ARRAY方法

更新

JSON_MERGE方法

JSON_ARRAY_APPEND

原数据为NULL

查询

JSON_EXTRACT

JSON_CONTAINS

JSON_CONTAINS_PATH

JSON_VALUE

相关阅读

骑驴找蚂蚁最受欢迎程序解答

最受欢迎程序解答

相关程序解答推荐

留言