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

背景

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

查询所有表名

通过查询表information_schema.TABLES 获得

SELECT
	TABLE_NAME AS '名称',
	TABLE_COMMENT AS '表注释',
	AUTO_INCREMENT AS '下一个自增长值'
FROM
	information_schema.TABLES 
WHERE
	table_schema = '数据库名';

查询表的所有字段

SELECT
	COLUMN_NAME AS '列名',
	DATA_TYPE AS '类型',
	COLUMN_COMMENT AS '列注释',
	COLUMN_KEY,
	EXTRA,
	CHARACTER_MAXIMUM_LENGTH,
	IS_NULLABLE,
	COLUMN_DEFAULT 
FROM
	information_schema.COLUMNS 
WHERE
	table_schema = '数据库名' 
	AND table_name = '表名';

过虑特征字段

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

SELECT
	cols.TABLE_NAME AS '表名',
	COLUMN_NAME AS '列名',
	COLUMN_COMMENT AS '列注释' 
FROM
	information_schema.COLUMNS cols 
WHERE
	TABLE_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

SELECT
	CONCAT('UPDATE ',cols.TABLE_NAME,' SET ',cols.COLUMN_NAME,' = ','REPLACE(',cols.COLUMN_NAME,',',"'hw.hongweisoft.com'",',',"'dev.dazesoft.cn'",')',';') AS '更新SQL'
FROM
	INFORMATION_SCHEMA.COLUMNS cols 
WHERE
	TABLE_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');

最后执行一下就可以了