MySQL中批量将字符串的某个指定子串替换成另一个子串
问题如下:
需要将表model_basic中MODELURL字段的“manage.zaiguiyang.com”全部替换成“manage.zaichengdu.com”
实现语句如下:
UPDATE model_basic SET MODELURL = replace(MODELURL,'manage.zaiguiyang.com','manage.zaichengdu.com');
扩展
replace的用法
REPLACE(str,from_str,to_str)
将str中所有的from_str替换成str,然后返回。该函数对大小写敏感和多字节编码安全。
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
解决上述问题的其他实现方式
使用CONCAT(str1,str2,…)、FIND_IN_SET(str,strlist)、CHAR_LENGTH()、MID(str,pos,len)三个函数组合实。
思路
- 先计算出‘manage.zaiguiyang.com’在整个字符串中的起始位置和长度,使用到:find_in_set和char_length两个函数
- 然后使用mid函数将’manage.zaiguiyang.com’前后的子串截下来
- 最后通过concat函数将三个子串按顺序连接起来
SQL语句
UPDATE
model_basic
SET
MODELURL = CONCAT (
MID( MODELURL, 0, FIND_IN_SET( MODELURL, 'manage.zaiguiyang.com' ) )
,'manage.zaichengdu.com',
MID(MODELURL,FIND_IN_SET( MODELURL, 'manage.zaiguiyang.com' ) + CHAR_LENGTH( 'manage.zaiguiyang.com' ),
CHAR_LENGTH( MODELURL ) - FIND_IN_SET( MODELURL, 'manage.zaiguiyang.com' ) - CHAR_LENGTH( 'manage.zaiguiyang.com' )
)
);
这个方案与使用replace实现的方案中的区别在于,使用replace实现时会将字符串中所有的from_str都换掉,而使用concat的拼接实现仅仅会替换掉第一个from_str
不同实现方式的效率问题
在源字符串特别长的情况下,使用concat实现在理论上效率是优于replace。所以,在明知道源字符串中有且仅有一个from_str的时候,选用concat效果好。但是在不明确的情况下,还是需要使用replace。[该想法未经证实]