如何更新 MySQL 5.7.10中的 JSON 数据类型列？

我最近开始使用 MySQL 5.7.10，并且非常喜欢原生 JSON 数据类型。

但是在更新 JSON 类型值时，我遇到了一个问题。

问题:

下面是表格格式，这里我想在 JSON data列中为 t1表增加1个键。现在我必须获取值，修改它并更新表。因此它涉及到一个额外的 SELECT语句。

我可以像这样插入

INSERT INTO t1 values ('{"key2":"value2"}', 1);


mysql> select * from t1;
+--------------------+------+
| data               | id   |
+--------------------+------+
| {"key1": "value1"} |    1 |
| {"key2": "value2"} |    2 |
| {"key2": "value2"} |    1 |
+--------------------+------+
3 rows in set (0.00 sec)


mysql>Show create table t1;




+-------+-------------------------------------------------------------


-------------------------------------------------------+
| Table | Create Table                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
`data` json DEFAULT NULL,
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

有什么办法吗？

123870

小开

最佳答案

Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it.

mysql> select * from t1;
+----------------------------------------+------+
| data                                   | id   |
+----------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}   |    1 |
| {"key2": "VALUE2"}                     |    2 |
| {"key2": "VALUE2"}                     |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"} |    1 |
+----------------------------------------+------+
4 rows in set (0.00 sec)


mysql> update t1 set data = JSON_SET(data, "$.key2", "I am ID2") where id = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+----------------------------------------+------+
| data                                   | id   |
+----------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}   |    1 |
| {"key2": "I am ID2"}                   |    2 |
| {"key2": "VALUE2"}                     |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"} |    1 |
+----------------------------------------+------+
4 rows in set (0.00 sec)


mysql> update t1 set data = JSON_SET(data, "$.key3", "I am ID3") where id = 2;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from t1;
+------------------------------------------+------+
| data                                     | id   |
+------------------------------------------+------+
| {"key1": "value1", "key2": "VALUE2"}     |    1 |
| {"key2": "I am ID2", "key3": "I am ID3"} |    2 |
| {"key2": "VALUE2"}                       |    1 |
| {"a": "x", "b": "y", "key2": "VALUE2"}   |    1 |
+------------------------------------------+------+
4 rows in set (0.00 sec)

EDIT: If you want to add an array, use JSON_ARRAY like

update t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2;

Now with MySQL 5.7.22+ it is very easy and straightforward to update the whole fragment of json (multiple key values, or even nested) in a single query like this:

update t1 set data =
JSON_MERGE_PATCH(`data`, '{"key2": "I am ID2", "key3": "I am ID3"}') where id = 2;

Hope it helps someone visiting this page and looking for a "better" JSON_SET :) More about JSON_MERGE_PATCH here: https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch