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

Question

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

mysqljsonmysql-5.7mysql-json

104

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

但是当涉及到更新JSON类型的值时，我遇到了一个问题。

问题：

下面是表格格式，我想在t1表的JSON data列中添加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)

有没有解决这个问题的方法？

- ʞɹᴉʞ ǝʌɐp

你为什么要这样添加数据？应该为键和值分别创建不同的列。 - Pathik Vejani

@PathikVejani 像我之前提到的一样，我正在尝试利用提供本地 JSON 数据类型的 mysql 5.7。我的 JSON 可能非常庞大。我无法为每个键值对添加列。 - ʞɹᴉʞ ǝʌɐp

9

查看：12.16 JSON 函数。 - wchiquito

1

@wchiquito，感谢您指引我正确的方向。我已经发布了我的解决方案，但所有的功劳都应该归给您。 - ʞɹᴉʞ ǝʌɐp

如果您只想更改一个键，请参阅MySQL Update or Rename a Key in JSON。 - Deepam Gupta

显示剩余2条评论

2个回答

21

现在使用MySQL 5.7.22+，可以非常轻松和直接地通过一个单一查询来更新整个json片段（多个键值对甚至嵌套的情况）：

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

希望这篇文章能够帮助到那些寻找更好的JSON_SET的访问者 :) 关于JSON_MERGE_PATCH的更多信息可以在这里找到： https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patch

- boryn

3

另外值得一提的是，如果当前的“data”为空（在MySQL 8.0.13之前，你不能设置JSON列的默认值），这个更新操作是不起作用的。因此，你可能需要做类似于 update t1 set data = JSON_MERGE_PATCH(COALESCE(\data`,{}'), '{"key2": "I am ID2", "key3": "I am ID3"}') where id = 2;`这样的操作。 - Nimrod Shory

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ʞɹᴉʞ ǝʌɐp · Accepted Answer

感谢 @wchiquito 指导我正确的方向，我已经解决了问题。以下是我的解决方法。

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)

编辑：如果您想添加一个数组，请使用 JSON_ARRAY，例如

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