drop PROCEDURE if exists init_replace;
CREATE PROCEDURE init_replace(in orig_str varchar(100),in new_str varchar(100),in db_name varchar(100))
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 do_replace(orig_str,new_str,db_name,t_name);
FETCH cur INTO t_name;
END WHILE;
END;
drop PROCEDURE if exists do_replace;
CREATE PROCEDURE do_replace(in orig_str varchar(100),in new_str varchar(100),in db_name varchar(100),in t_name varchar(100))
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 and column_name <>'constraint';
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,',''',orig_str,''',''',new_str,''');');
prepare stmt from @update_sql;
execute stmt;
FETCH cur INTO cul_name;
END WHILE;
CLOSE cur;
END;
函数调用
call init_replace('原内容','替换后的内容','数据库名称');