MySQL-5.7环境下全局替换任意字段下URL方法

一、需求

由于文件服务器迁移、域名变更、云存储迁移图片素材等场景下，数据库中涉及到url文件地址信息的表字段内容需要涉及到前缀替换；

二、MySQL数据库

温馨小提示：基于mysql5.7版本

查询指定库中所有表字段信息

SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '指定库';

-- ex: 
SELECT TABLE_NAME, column_name, DATA_TYPE, column_comment FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'demo';

截取字段内容

SELECT substring( 指定字段, 从左到右需要截掉的长度 , LENGTH( substring( 指定字段, 1 )) ) FROM 指定表;

-- ex: 
SELECT substring( url, 28, LENGTH( substring( url, 1 )) ) FROM t_demo;

查询指定字段包含指定内容的结果

SELECT * FROM 指定表 WHERE locate('指定内容' , 指定字段);

-- ex: 
SELECT * FROM t_demo WHERE locate( 'https://', url );

更新字段值

UPDATE 指定表 SET 指定字段 =( substring( 指定字段, 从左到右需要截掉的长度, LENGTH( substring( 指定字段, 1 )) ))  WHERE LENGTH( substring( 指定字段, 1 ))> 0 AND locate( '指定内容', 指定字段 );

-- ex:
UPDATE t_demo SET url =( substring( url, 28, LENGTH( substring( url, 1 )) ))  WHERE LENGTH( substring( url, 1 ))> 0 AND locate( 'https://', url );

拼接最终更新字段值sql语句

SELECT
    CONCAT( 'update ', TABLE_NAME, ' set ', column_name, '=( substring(', column_name, ', 从左到右需要截掉的长度 ,LENGTH(substring(', column_name, ',1)) )) where LENGTH(substring(', column_name, ',1))>0 and locate(''指定内容'' , ', column_name, ');' ) 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = '指定库'

-- ex: 
SELECT CONCAT( 'update ', TABLE_NAME, ' set ', column_name, '=( substring(', column_name, ',28,LENGTH(substring(', column_name, ',1)) )) where LENGTH(substring(', column_name, ',1))>0 and locate(''https://'' , ', column_name, ');' ) FROM information_schema.COLUMNS  WHERE TABLE_SCHEMA = 'demo'

最后复制拼接好的sql运行即可