mysql5.7实现regex_replace正则替换功能（亲测可用） - 查问我看

转 mysql5.7实现regex_replace正则替换功能（亲测可用）

PHPer 2023-08-01 1129 0 0

1.开启mysql自定义函数支持

a.查看mysql当前是否支持编写自定义：

show variables like '%fun%';

b.log_bin_trust_function_creators=OFF表示没有开启自定义函数。输入开启命令。

set global log_bin_trust_function_creators=1;

注：此处不开启，后面自定义函数写好。调用不起作用！

2.编写regexp_replace()自定义函数

a.编写代码如下：

#创建前删除已经创建的自定义函数
drop function if exists regexp_replace;

CREATE  FUNCTION `regexp_replace`(ori_str LONGTEXT,pattern VARCHAR(1000),repl_str VARCHAR(1000)) RETURNS longtext CHARSET utf8mb4
    DETERMINISTIC
BEGIN  
	DECLARE tmp_str LONGTEXT;  
	DECLARE target_str LONGTEXT;  
	DECLARE str0 LONGTEXT;  
	DECLARE i1 INT;
	DECLARE i2 INT;
 
	# 此处假设MySQL5.7的正则表达式匹配类型是非贪婪模式，目前没有在官方文档内确认。
	# 本函数的基本逻辑是，先从原文本开头不断的裁剪，不能匹配上时，则证明找到了目标文本的开头；如法炮制，找到目标文本的结尾。以此确定目标文本的位置。
	# 示例：若原文本是AABBCC,正则表达式为B+,大致流程如下
	# i1=1, tmp_str="AABBCC", match
	# i1=2, tmp_str="ABBCC", match
	# i1=3, tmp_str="BBCC", match
	# i1=4, tmp_str="BCC", match
	# i1=5, tmp_str="CC", unmatch
	# 定位开始位置为4
	# i2=6, tmp_str="BCC", match
	# i2=5, tmp_str="BC", match
	# i2=4, tmp_str="B", match
	# i2=4, tmp_str="", unmatch
	# 定位结束位置为4
	# 所以输出结果是replace("AABBCC", "B", "")

	# Warning：此函数效率比较差，而且在重复replace的时候，会重复匹配相同的位置，有违正则表达式的匹配逻辑。
	# Warning：此函数在pattern能匹配repl_str会进入死循环，优化后再考虑加入报错机制。
	# Warning：函数返回的是长文本格式，在存入数据库之前考虑转换一下数据类型。

	SET tmp_str = ori_str; # 强迫症行为

	# 循环查找以替换所有的目标字符串
	loop0: LOOP
		IF NOT tmp_str REGEXP pattern THEN  
			LEAVE loop0;  
		END IF;
		
		# 目前初始值多设了一位
		SET i1 = 1;
		SET i2 = CHAR_LENGTH(tmp_str);
		
		# 循环从头裁剪字符串
		loop1: LOOP
			SET str0 = SUBSTR(tmp_str, i1, i2-i1+1);
			IF NOT str0 REGEXP pattern THEN
				SET i1 = i1-1; # 当不能匹配上时，还原至上次循环到的位置。
				LEAVE loop1;
			ELSE
				SET i1 = i1+1;
			END IF;
		END LOOP;
		
		# 循环从尾裁剪字符串
		loop2: LOOP
			SET str0 = SUBSTR(tmp_str, i1, i2-i1+1);
			IF NOT str0 REGEXP pattern THEN
				SET i2 = i2+1; # 当不能匹配上时，还原至上次循环到的位置。
				LEAVE loop2;
			ELSE
				SET i2 = i2-1;  
			END IF;
		END LOOP;
		
		# 根据定位到的头和尾，截取目标文本
		SET target_str = SUBSTR(tmp_str, i1, i2-i1+1);
		
		# 替换原文本中的目标文本
		SET tmp_str = REPLACE(tmp_str, target_str, repl_str);
	END LOOP;  

	RETURN tmp_str;
END

b.上述代码执行后，检查下是否创建成功。结果已经创建成功。...

浏览更多内容请先登录。 立即注册

数据库

更新于：2023-08-01 20:55:04

您需要登录后才可以评论。立即注册

|-转 REGEXP_REPLACE 替换匹配的字符串

SELECT id,personal_phone,REPLACE (personal_phone,REGEXP_REPLACE (personal_phone,'[1][3456789][0-9]{9}','') ,'') as new_str from table WHERE `personal_phone` NOT REGEXP '^[1][3456789][0-9]{9}$'

update table set personal_phone = REPLACE (personal_phone,REGEXP_REPLACE (personal_phone,'[1][3456789][0-9]{9}','') ,'') WHERE `personal_phone` NOT REGEXP '^[1][3456789][0-9]{9}$'  limit 10

写在最后，mysql5.7用的是上文mysql5.7实现regex_replace正则替换功能（亲测可用） - 查问我看 - 查问网 (chawen.org)自定义的 REGEXP_REPLACE ...