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 条件1 AND 条件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 条件1 AND 条件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，而->>返回字符串。