MySQL(22) 更新数据库下所有表中含有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运行即可

---

今日分享语句：
没事晒晒太阳吧,没准晒黑了就没人说是白痴了。