Saya baru-baru ini mula menggunakan MySQL 5.7.10 dan saya sangat menyukai jenis data JSON asli.
Tetapi saya menghadapi masalah semasa mengemas kini nilai jenis JSON.
Soalan:
Berikut adalah format jadual, di sini saya ingin menggunakan data 列中为 t1 表添加 1 个键。现在我必须获取值修改它并更新表。所以就涉及到一个额外的SELECTpenyata.
Boleh selit macam ni
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)
Adakah penyelesaiannya?
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Kini dengan MySQL 5.7.22+, sangat mudah dan mudah untuk mengemas kini keseluruhan serpihan json (berbilang nilai kunci, malah bersarang) dalam satu pertanyaan, seperti ini:
update t1 set data = JSON_MERGE_PATCH(`data`, '{"key2": "I am ID2", "key3": "I am ID3"}') where id = 2;Semoga ia membantu melawat halaman ini dan mencari yang "lebih baik"
JSON_SET:) Maklumat lanjut tentangJSON_MERGE_PATCHdi sini: https://dev.mysql .com/doc/refman/5.7/en/json-modification-functions.html#function_json-merge-patchTerima kasih kepada @wchiquito kerana menunjukkan saya ke arah yang betul. Saya menyelesaikan masalah ini. Ini adalah cara saya melakukannya.
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: Jika anda ingin menambah tatasusunan, gunakan
JSON_ARRAYsepertiupdate t1 set data = JSON_SET(data, "$.key4", JSON_ARRAY('Hello','World!')) where id = 2;