如何在MySQL 5.7.10中更新JSON数据类型列? [英] How to update JSON data type column in MySQL 5.7.10?

问题描述

我最近开始使用MySQL 5.7.10，我非常喜欢本机JSON数据类型.

I have started using MySQL 5.7.10 recently and I am liking the native JSON Data type a lot.

但是在更新JSON类型值时遇到了一个问题.

But I ran into a problem when it comes to updating a JSON type value.

问题:

以下是表格格式，在这里我想在t1表格的JSON data列中再添加1个键.现在，我必须获取值来修改它并更新表.因此，它涉及一个额外的SELECT语句.

Below is the table format, here I want to add 1 more key in JSON data column for t1 table. Right now I have to fetch the value modify it and Update the table. So it involves an extra SELECT statement.

我可以这样插入

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)

是否可以解决此问题?

推荐答案

感谢@wchiquito为我指出正确的方向.我解决了问题.这是我的方法.

Thanks @wchiquito for pointing me right direction. I solved the problem. Here is how I did it.

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)

这篇关于如何在MySQL 5.7.10中更新JSON数据类型列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！