骑
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)