mysql 数据库替换所有表的所有行的所有字段的值

mysql admin 2023-05-05 23:17 569 0

思路:查询出数据路的所有表,在循环查询出表的所有字段并循环更新
1、创建存储过程:查询表的所有字段并指定更新值

delimiter //
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;
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

2、创建存储过程:查出指定数据库中所有的表


delimiter //
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



3、调用存储过程
CALL init_replace('被替换的词语','替换后的词语','数据库名');

具体示例:
CALL init_replace('88sw.cn','88sw.top','88sw_top');