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)三个函数组合实。

思路

1. 先计算出‘manage.zaiguiyang.com’在整个字符串中的起始位置和长度，使用到：find_in_set和char_length两个函数
2. 然后使用mid函数将’manage.zaiguiyang.com’前后的子串截下来
3. 最后通过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。[该想法未经证实]