mysql替换（replace）字符串字段的某些字符串、正则替换

由于图片字段上传到腾讯云cos之后，缩略图带了裁剪参数，存入数据库之后，参数也被存在了数据库中，导致图片的显示不全，现在需要把图片处理参数删除，也就是图片url问号后面的参数一并删除；刚开始想法是直接用mysql里面的regexp_replace函数对敏感字段进行处理。后来发现mysql8.0才支持regexp_replace()。mysql5.7里只有正则匹配的函数。最后还是用mysql本身提供的函数进行处理。

1、查询原始数据

#查询原始数据

SELECT 

  id,

  share_img

FROM

  `aikehou_hudong_online_game_round` 

WHERE `share_img` REGEXP '\\?'

2、查询需要替换的字段信息

#查询需要替换的字段信息，其中：1）、locate函数是查找问号在整个字符串的位置，2）、length函数是计算整个字符串的长度，3）、substr函数是将问号后面的字符串筛选出来

SELECT 

  id,

  share_img,

  LOCATE("?", `share_img`) AS str_pos,

  SUBSTR(

    `share_img`,

    LOCATE("?", `share_img`),

    LENGTH(`share_img`)

  ) AS str_left,

  LENGTH(`share_img`) AS str_length 

FROM

  `aikehou_hudong_online_game_round` 

WHERE `share_img` REGEXP '\\?';

3、替换

#替换问号后的所有字符串为其他字符串，replace函数是处理替换

UPDATE 

  `aikehou_hudong_online_game_round` 

SET

  `share_img` = REPLACE (

    `share_img`,

    SUBSTR(

      `share_img`,

      LOCATE("?", `share_img`),

      LENGTH(`share_img`)

    ),

    ""

  ) 

WHERE `share_img` REGEXP '\\?';

4、replace方法字符串前面加前缀

#查询数据

SELECT id,share_img,CONCAT('https://static.manyidea.cloud',`share_img`) 

FROM `aikehou_hudong` 

WHERE `share_img` NOT REGEXP 'https:' AND `share_img` REGEXP '\/uploads' AND `share_img` != '';

#更新数据

UPDATE `aikehou_hudong_online_game_round` 

SET `share_img` = REPLACE (

        `share_img`,

        `share_img`,

        CONCAT('https://static.manyidea.cloud',`share_img`)

) 

WHERE `share_img` NOT REGEXP 'https:' AND `share_img` REGEXP '\/uploads' AND `share_img` != '';

5、如果是mysql8版本的可以直接使用REGEXP_REPLACE 函数会更简单

MySQL 正则替换数据：REGEXP_REPLACE 函数

用法

注意：此函数为 MYSQL8.0 版本新增，低于 8.0 版本没有此函数

REGEXP_REPLACE() 函数用于模式匹配。它通过匹配字符来替换给定的字符串字符。

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

Replaces occurrences in the string expr that match the regular expression specified by the pattern pat with the replacement string repl, and returns the resulting string. If expr, pat, or repl is NULL, the return value is NULL.

（将字符串表达式中与模式 pat 指定的正则表达式匹配的匹配项替换为替换字符串 repl，并返回结果字符串。如果 expr、pat 或 repl 为 NULL，则返回值为 NULL）

REGEXP_REPLACE() takes these optional arguments:

pos: The position in expr at which to start the search. If omitted, the default is 1.

occurrence: Which occurrence of a match to replace. If omitted, the default is 0 (which means “replace all occurrences”).

match_type: A string that specifies how to perform matching. The meaning is as described for REGEXP_LIKE().

Prior to MySQL 8.0.17, the result returned by this function used the UTF-16 character set; in MySQL 8.0.17 and later, the character set and collation of the expression searched for matches is used. (Bug #94203, Bug #29308212)

使用示例

表数据

功能需求：把 name 字段中的 a 标签内容替换为空

实现 SQL:

UPDATE tableName set `name` = REGEXP_REPLACE(`name`, '<.*>', '') WHERE `name` REGEXP '<.*>';

via：

MySQL 正则替换数据：REGEXP_REPLACE函数 - 代码天地https://www.codetd.com/article/11975683

MySQL :: MySQL 8.0 Reference Manual :: 12.8.2 Regular Expressionshttps://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

5、参考：

MySQL LENGTH函数：获取字符串长度

MySQL 正则替换数据：REGEXP_REPLACE 函数