########## 功能：在某个数据库中查找字符串并替换 ##########
# 初始化设置(设置后运行整个代码即可)
# 数据库名
USE `数据库名`;
set @var_db_name = '数据库名';
# 查找字符串
set @var_search = 'xxxxxx';
# 替换字符串
set @var_replacement = 'xxxxxx';
##########################################################
DELIMITER $$
DROP PROCEDURE IF EXISTS `db_replace`$$
DROP PROCEDURE IF EXISTS `tbl_replace`$$
#在数据库中查找替换
CREATE PROCEDURE `db_replace`(
IN `search` varchar(100),
IN `replacement` varchar(100),
IN `db_name` varchar(100)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE t_name VARCHAR(50);
DECLARE done int default 0;
DECLARE cur CURSOR FOR SELECT DISTINCT table_name as name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=db_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO t_name;
WHILE (done<>1) DO
call tbl_replace(search,replacement,db_name,t_name);
FETCH cur INTO t_name;
END WHILE;
END$$
#在数据表中查找替换
CREATE PROCEDURE `tbl_replace`(
IN `search` varchar(100),
IN `replacement` varchar(100),
IN `db_name` varchar(100),
IN `t_name` varchar(100)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE cul_name VARCHAR(50);
DECLARE done int default 0;
DECLARE cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=db_name and TABLE_NAME=t_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO cul_name;
WHILE (done<>1) DO
#更新操作
set @update_sql=CONCAT("UPDATE ",t_name," SET `",cul_name,"` = REPLACE(`",cul_name,"`,'",search,"','",replacement,"')");
prepare stmt from @update_sql;
execute stmt;
FETCH cur INTO cul_name;
END WHILE;
CLOSE cur;
END$$
#运行主程序
call db_replace(@var_search,@var_replacement,@var_db_name)$$
DROP PROCEDURE IF EXISTS `db_replace`$$
DROP PROCEDURE IF EXISTS `tbl_replace`$$
DELIMITER ;