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] ...)

对于路径，指定要从$开始放置的层次结构等。点击此处了解详情。这次它会被放在第一层，所以只有$。

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)