MySQL——JSON_REPLACE()函数修改JSON属性值

古城微笑少年丶 2022-10-17 10:51 451阅读 0赞

由于对mysql的函数并不了解，之前遇到了一个场景：

mysql表中有一个字段res\_content 是一个由longtext类型（可以理解为一个更长的varchar）保存的巨大的JSON对象，但是，由于录入的疏忽，导致这个json对象中的有一个属性值错误，需要重新修改这个json对象的属性值，于是，我将整个json对象，也就是res\_content字段重新替换了一遍。=。= |||

一个json中小小的属性需要更新，却要替换整个json对象，仅仅因为它在数据库中是以一个字段存储的！！？这也太傻吊了！

这样做虽然简单粗暴，但很明显不符合我追求完美的性格，既然只有一个属性出错，那为什么不能直接替换这个JSON中具体的属性呢？而且这样的解决办法，如果处理上千万条JSON还可以减小mysql的性能开销，岂不美哉！？

当时并没有及时想到mysql函数，实际上mysql现在已经支持了大量的JSON字符串的操作函数，可以支持基本的需求。这也是我偶然间浏览我的关注里面的博文一个大神提供给我的思路：《mysql替换某字段中的部分值》，所以非常感谢这位博主。

不过，这个博主的文章只是替换已知字符串的值，比如 ：http 改成 https，但对于json字符串，我们要想替换key所对应的value就涉及到两步：查询和替换，很明显，他这篇博客并不符合我的需求，不过确实给我提供了一个查阅mysql函数的思路。下面就来简单说说MySQL中用于替换JSON属性值的这个函数：JSON\_REPLACE(json\_doc, path, val\[, path, val\] ...)。

官方描述  
JSON\_REPLACE()：

官方的第一句解释是：Replaces existing values in a JSON document and returns the result。就冲这句话，基本可以锁定它就是我要找的解决方案。我们来看一下官方的示例：

mysql> SET @j = '\{ "a": 1, "b": \[2, 3\]\}';  
mysql> SELECT JSON\_REPLACE(@j, '$.a', 10, '$.c', '\[true, false\]');  
\+-----------------------------------------------------+  
| JSON\_REPLACE(@j, '$.a', 10, '$.c', '\[true, false\]') |  
\+-----------------------------------------------------+  
| \{"a": 10, "b": \[2, 3\]\}                              |  
\+-----------------------------------------------------+  
上面的例子很好理解，首先，它定义了一个JSON字符串变量，然后通过JSON\_REPLACE函数将这个变量中的属性a的值由1 改为了10，然后将属性c的值改为了‘\[true, false\]’，但由于json字符串中并没有属性 c 因此只有属性 a 修改成功了。（这里需要注意，mysql函数仅仅是做数据转换，并不涉及到真正的增删改查，因此还需要配合具体的UPDATE才能够真正更新数据）

看过这个例子之后，我思考了一下我的应用场景，只需要在更新的sql语句中调用JSON\_REPLACE函数，将json对象所对应的字段比作上面的JSON字符串变量，然后通过 ‘$xxx’ 匹配到我所希望修改的值，然后就可以成功修改JSON对象中的属性了。

验证测试  
现在我有一张employee表，它的last\_name字段是一个简单的JSON字符串：

现在，我希望修改emp\_id = 1的记录，将这条记录中last\_name中的name改成“Harry”，gender改成 “女”。于是我执行了下面的SQL：

UPDATE employee   
SET last\_name = json\_replace(last\_name, '$.name', "Harry", "$.gender", "女")   
WHERE emp\_id = 1;  
这里注意，我特意将 "$.gender" 写为双引号，目的就是为了测试这样的通配是否可以生效，执行成功后，查询结果如下：

可以看到，last\_name字段中的name属性已经修改成Harry，gender也被修改成了“女”，因此通配符 用双引、单引都是可以的。

除了JSON\_REPLACE()函数，还有很多其他的json函数，如JSON\_REMOVE()等，具体函数用法可以查看官方文档：

https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

里面还贴心的将用法进行了归类：

但是，这些函数应该都对mysql数据库的版本比较敏感，一般对MySQL5.7以上的版本支持比较理想，个别函数可能需要5.7.8以上，我的数据库是5.7.23，因此JSON\_REPLACE()用着还可以，如果你的版本在5.7以下，就得好好看看是否支持这些函数了。

简单查看MySQL数据库的版本：SELECT VERSION();

综上，就是关于如何修改JSON字符串中属性值的函数JSON\_REPLACE()的简单介绍