我最近开始使用 MySQL 5.7.10,我非常喜欢原生 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)
有解决办法吗?
P粉0128759272023-10-17 13:48:20
现在使用 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
P粉0356005552023-10-17 09:16:04
感谢@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;