首页  >  问答  >  正文

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

我最近开始使用 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粉512363233P粉512363233390 天前700

全部回复(2)我来回复

  • P粉012875927

    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

    回复
    0
  • P粉035600555

    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;

    回复
    0
  • 取消回复