mysql数据库字段地址替换

-- 1、查询有相关文件地址的字段
SELECT CONCAT("select '",table_schema,".",table_name,"' as table_name, '",column_name,"' as column_name, count(1) as num FROM ",table_schema,".",table_name," where ",column_name," like '%http://192.168.0.94:8300/files/xx/upload%' union all")
from information_schema.COLUMNS
where table_schema in('qh_unicom','qh_unicom_system','qh_unicom_partner','qh_unicom_workflow')
and data_type not in('bigint','datetime','int','decimal','tinyint','date');

-- 2、生成替换的sql
select *,CONCAT("update ",table_name," SET ",column_name," = REPLACE(",column_name,",'http://192.168.0.94:8300/files/xx/upload','https://192.168.0.94:8200/files/xx/upload');") 'replace_sql'
from (
-- 第一步执行的结果拷贝到此，并删除最后一个 union all
)t
where t.num>0

-- 3、将第二步执行的结果 replace_sql 列，拷贝出来进行执行