如何更新 MySQL 5.7.10 中的 JSON 数据类型列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34986223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update JSON data type column in MySQL 5.7.10?
提问by ???? ???p
I have started using MySQL 5.7.10 recently and I am liking the native JSON Data type a lot.
我最近开始使用 MySQL 5.7.10，我非常喜欢原生 JSON 数据类型。
But I ran into a problem when it comes to updating a JSON type value.
但是我在更新 JSON 类型值时遇到了问题。
Questions:
问题：
Below is the table format, here I want to add 1 more key in JSON datacolumn for t1table. Right now I have to fetch the value modify it and Update the table. So it involves an extra SELECTstatement.
下面是表格格式，这里我想在表格的JSONdata列中再添加 1 个键t1。现在我必须获取值修改它并更新表。所以它涉及一个额外的SELECT声明。
I can insert like this
我可以这样插入
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)
Is there a work around for this?
有解决办法吗？
回答by ???? ???p
Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it.
感谢@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)
