mysql数据库中指定值在所有表中所有字段中的替换

MySQL数据库:

  指定值在数据库中所有表所有字段值的替换(存储过程):

  1.写一个存储过程,查指定数据库中所有的表名: 

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;

  2.然后1中调用2中的方法,在查询表中所有的字段,并且指定更新值:

  

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;

  3.调用1的方法:

  启动存储过程:call init_replace('http://localhost:5005','https://www.wqbol.net','给数据库名称');

上一篇:MySQL查询结果复制到新表(更新、插入)


下一篇:JQ滑动导航菜单的实现