如何更新 MySQL 5.7.10 中的 JSON 資料類型列？

Question

我最近開始使用 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)

有解決辦法嗎？

Answer 1

現在使用 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

Answer 2

感謝@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;