MySQL正则匹配替换

最近在SegmentFault上回答了个问题，觉得有点意思，且对于低版本MySQL用户应该也会有这个需求，故记录之。
表结构和数据：

CREATE TABLE `bodytext` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `body` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `bodytext` (`id`, `body`) VALUES ('1', '<p>导读： “我有一个人生建议会让你受益一生哦！”“什么人生建议啊。”<img alt="文字1" title="文字1" src="http://pic.google.com/image/1.jpg">“这辈子和我在一起。”快来看看励志故事网的小编为你们准备的抖音撩人的土味情话吧！</p><p></p><p>1. 往后日子，万事俱备，只差你而已。<img alt="文字2" title="文字2" src="http://pic.baidu.com/image/2.jpg"></p>');
INSERT INTO `bodytext` (`id`, `body`) VALUES ('2', '<p><img alt="文字3" title="文字3" src="http://pic.baidu.com/image/32779.jpg"></p>');
INSERT INTO `bodytext` (`id`, `body`) VALUES ('3', '<p><img alt="文字4" title="文字4" src="http://pic.google.com/image/32778.jpg"></p>');
INSERT INTO `bodytext` (`id`, `body`) VALUES ('4', '<p>导读： “我有一个人生建议会让你受益一生哦！”</p>');

需求：bodytext表的body字段中含有N个<img>标签，想去除掉src是来自pic.baidu.com的<img>标签（有的src来自pic.google.com的则要保留）。

一个完整的<img>标签，我们可以这样来正则匹配<img.*?>，以<img开头，最后是以>结尾。
如果mysql版本是8.0及以上，可以直接使用REGEXP_REPLACE函数进行正则替换。

但如果是低版本的mysql，则没有该函数可以使用。可以换个思路：循环查找出所有<img>标签，判断是否为pic.baidu.com，最后再关联主表bodytest进行update。用到了mysql的substr、locate、replace、substring_index函数来截取和替换。
首先查找出每个body中的<img>标签，看是否正确：

set @START_PAD='<img';
set @END_PAD='>';

select t3.id, substr(t3.body,char_length(t3.str)+1,locate(@END_PAD,t3.body,char_length(t3.str)+1)-char_length(t3.str)) as img  
from (select t1.id,t1.body,substring_index(t1.body,@START_PAD,t2.help_topic_id+1) str 
    from bodytext t1 
    inner join mysql.help_topic t2 on (LENGTH(t1.body)-LENGTH(replace(t1.body,@START_PAD,'')))/char_length(@START_PAD)>t2.help_topic_id
   )t3;

+----+---------------------------------------------------------------------------------+
| id | img                                                                             |
+----+---------------------------------------------------------------------------------+
|  1 | <img alt="文字1" title="文字1" src="http://pic.google.com/image/1.jpg">         |
|  1 | <img alt="文字2" title="文字2" src="http://pic.baidu.com/image/2.jpg">          |
|  2 | <img alt="文字3" title="文字3" src="http://pic.baidu.com/image/32779.jpg">      |
|  3 | <img alt="文字4" title="文字4" src="http://pic.google.com/image/32778.jpg">     |
+----+---------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

接下去就简单了，直接跟bodytext进行关联update，最终完整SQL如下：

set @START_PAD='<img';     #开始匹配字符串
set @END_PAD='>';          #结束匹配字符串
set @STR='pic.baidu.com';  #中间包含字符串

update bodytext m inner join 
 (select t3.id,substr(t3.body,char_length(t3.str)+1,locate(@END_PAD,t3.body,char_length(t3.str)+1)-char_length(t3.str)) as img 
   from (select t1.id,t1.body,substring_index(t1.body,@START_PAD,t2.help_topic_id+1) str from bodytext t1 inner join mysql.help_topic t2 on (char_length(t1.body)-char_length(replace(t1.body,@START_PAD,'')))/char_length(@START_PAD)>t2.help_topic_id)t3
 )n on m.id=n.id and n.img like concat('%',@STR,'%')
set m.body=replace(m.body,n.img,'');

如果有类似的正则匹配，仅需修改上面的3个变量和表名，依样画葫芦。

Abul成长之路

Comments