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

思路：查询出数据路的所有表，在循环查询出表的所有字段并循环更新

1、创建存储过程：查询表的所有字段并指定更新值

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

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

CREATE DEFINER=`root`@`localhost` 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('http://www.baidu.com','https://www.baidu.com','替换的数据库名');