mysql 存储过程 替换_mysql数据库中替换所有表中的所有字段值（存储过程）

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

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

CREATE DEFINER=`root`@`localhost` PROCEDURE `init_replace`(in orig_str varchar(255),in new_str varchar(255),in db_name varchar(100))

BEGIN

#Routine body goes here...

DECLARE t_name VARCHAR(100);

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 DEFINER=`root`@`localhost` PROCEDURE `do_replace`(in orig_str varchar(100),in new_str varchar(100),in db_name varchar(100),in t_name varchar(100))

BEGIN

#Routine body goes here...

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://www.baidu.com','https://www.baidu.com','给数据库名称');