mysql如何替换数据库所有表中某些字段含有的特定值

背景

公司的测试域名更换了，导致存放在数据库中的域名也要跟着替换，当然把域名存放在数据库表中是不科学的，不建议这样做，但公司的同事就这样做了，分配我把每个数据库中的域名都换了，想了很久，后面得到了一个比较完美的解决方案，下面我们就一起来学习学习吧。

查询所有表名

通过查询表information_schema.TABLES 获得

SELECTTABLE_NAME AS '名称',TABLE_COMMENT AS '表注释',AUTO_INCREMENT AS '下一个自增长值'
FROMinformation_schema.TABLES 
WHEREtable_schema = '数据库名';

在这里插入图片描述

查询表的所有字段

SELECTCOLUMN_NAME AS '列名',DATA_TYPE AS '类型',COLUMN_COMMENT AS '列注释',COLUMN_KEY,EXTRA,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT 
FROMinformation_schema.COLUMNS 
WHEREtable_schema = '数据库名' AND table_name = '表名';

在这里插入图片描述

过虑特征字段

通过where过虑要替换的字段名

SELECTcols.TABLE_NAME AS '表名',COLUMN_NAME AS '列名',COLUMN_COMMENT AS '列注释' 
FROMinformation_schema.COLUMNS cols 
WHERETABLE_SCHEMA = '数据库名' AND (cols.COLUMN_NAME LIKE '%url%' OR cols.COLUMN_NAME LIKE '%adress%' OR cols.COLUMN_NAME LIKE '%file%' OR cols.COLUMN_NAME LIKE '%image%' OR cols.COLUMN_NAME LIKE '%img%' OR cols.COLUMN_COMMENT LIKE '%图片%' OR cols.COLUMN_COMMENT LIKE '%文件%' OR cols.COLUMN_COMMENT LIKE '%地址%' );

在这里插入图片描述

替换字段中含有的特定值

用到的函数：CONCAT，REPLACE
例子中将hw.hongweisoft.comu替换成dev.dazesoft.cn

SELECTCONCAT('UPDATE ',cols.TABLE_NAME,' SET ',cols.COLUMN_NAME,' = ','REPLACE(',cols.COLUMN_NAME,',',"'hw.hongweisoft.com'",',',"'dev.dazesoft.cn'",')',';') AS '更新SQL'
FROMINFORMATION_SCHEMA.COLUMNS cols 
WHERETABLE_SCHEMA = '数据库名' AND (cols.COLUMN_NAME LIKE '%url%' OR cols.COLUMN_NAME LIKE '%adress%' OR cols.COLUMN_NAME LIKE '%file%' OR cols.COLUMN_NAME LIKE '%image%' OR cols.COLUMN_NAME LIKE '%img%' OR cols.COLUMN_COMMENT LIKE '%图片%' OR cols.COLUMN_COMMENT LIKE '%文件%' OR cols.COLUMN_COMMENT LIKE '%地址%' );

在这里插入图片描述
替换的sql语句：

UPDATE buyer_invoice SET address = REPLACE(address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE buyer_user_auth SET address = REPLACE(address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE buyer_user_auth SET company_address = REPLACE(company_address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE buyer_user_auth SET operate_address = REPLACE(operate_address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_activity_sign SET qr_image = REPLACE(qr_image,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_address_forbid_config SET forbid_type = REPLACE(forbid_type,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_address_forbid_config SET address = REPLACE(address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_after_dilever SET image = REPLACE(image,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_after_product SET image = REPLACE(image,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_buyer_receive SET receive_id = REPLACE(receive_id,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_buyer_receive SET receive_adress = REPLACE(receive_adress,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_buyer_receive SET address = REPLACE(address,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_buyer_receive SET label = REPLACE(label,'hw.hongweisoft.com','dev.dazesoft.cn');
UPDATE cere_buyer_user SET head_image = REPLACE(head_image,'hw.hongweisoft.com','dev.dazesoft.cn');