SQL中批量替换数据：REPLACE()、JSON_REPLACE()

目录

前言

文本编辑器中有批量替换的功能，那么SQL中也可以实现批量替换数据，以下两个函数：

• REPLACE()
• JSON_REPLACE()

REPLACE()

定义：

REPLACE(str,from_str,to_str)

释义：
在字符串str中查找子字符串from_str并用另一个字符串to_str替换它
下面是一个示例：
假设我们有一个名为mytable的表，其中包含一个名为content的字段，其中包含一些文本数据。我们想要将其中所有出现的字符串old_string替换为new_string。我们可以使用以下SQL语句：

UPDATE mytable
SET content =REPLACE(content,'old_string','new_string');# 使用UPDATE语句，后一定要跟WHERE限制条件，不然就更新整个数据库的内容了，慎重！！UPDATE mytable
SET content =REPLACE(content,'old_string','new_string');WHERE 条件1AND 条件2;

如下数据：

执行SQL后数据被替换为：

JSON_REPLACE()

在简单的字符串的替换中可以使用REPLACE()函数，若数据库中某个字段是JSON中的值，那么这个时候使用REPLACE()替换数据，数据量就会很大，而且很容易导致错误，若是个JSON对象，数据库也不会允许用REPLACE()方法直接替换
因此，这时候我们可以用JSON_REPLACE()函数做到对JSON对象的某个值的替换。

定义：

JSON_REPLACE(json_doc, path, val[, path, val]...)

释义：
在json_doc中，path则为JSON中需要替换的key，val则为需要替换的目标值
下面是一个示例：
假设我们有一个名为mytable的表，其中包含一个名为content的JSON类型的字段，其中包含以下数据：

{"name": "Alice","age": 30,"address": {"city": "Shanghai","country": "China"}}

现在我们想要将其中的age键的值替换为31，city键的值替换为Beijing。我们可以使用以下SQL语句：

UPDATE mytable
SET content = JSON_REPLACE(
  content,'$.age',31,'$.address.city','Beijing');# 使用UPDATE语句，后一定要跟WHERE限制条件，不然就更新整个数据库的内容了，慎重！！UPDATE mytable
SET content =REPLACE(content,'old_string','new_string');WHERE 条件1AND 条件2;

注意：在使用JSON_REPLACE函数时，路径必须以$开头，并且路径中的每个键名都必须使用双引号括起来。

获取JSON中的值

在查询过程中经常会遇到查询一个JSON中某个字段的值是否符合需求，如上JSON中查找地区是‘Beijing’的数据，可以用以下SQL

select*from mytable where content ->>'$.address.city'='Beijing'

以上SQL中用到符号‘->>’操作符，作用从JSON对象中获取指定键的值，并将其作为字符串返回。

操作符->和->> 的区别

在MySQL中，->和->>这两个操作符都是用于操作JSON类型的数据的。它们的主要区别在于返回值的类型不同。

->操作符用于从一个JSON对象中获取指定key的值，返回的是一个JSON对象或NULL。
例如，假设我们有一个名为mytable的表，其中包含一个名为content的JSON类型的字段，我们可以使用以下语句获取content中的name键对应的值：

SELECT content->'$.name'AS name FROM mytable;-- 查询结果为
{"name": "Alice"}

->>操作符则用于从一个JSON对象中获取指定key的值，并将其作为字符串返回。
例如，我们可以使用以下语句获取content中的name键对应的值：

SELECT content->>'$.name'AS name FROM mytable;-- 查询结果为
Alice

因此，->和->>的主要区别在于返回值的类型，->返回JSON对象或NULL，而->>返回字符串。