https://www.cnblogs.com/Generalwake/p/9790690.html 转载
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','给数据库名称');
OK 小修改了一下 有问题暂时不能存储表名和列明 只能列出所有 此处原创 注出处
-- ----------------------------
-- Procedure structure for found_source 创建循环表
-- ----------------------------
DROP PROCEDURE IF EXISTS `found_source`;
delimiter ;;
CREATE PROCEDURE `found_source`(in orig_str varchar(100),in db_name varchar(100))
BEGIN
DECLARE t_name VARCHAR(200);
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;
DROP TABLE IF EXISTS `tmp_table`;
CREATE TABLE `tmp_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tablename` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`columnname` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`columnvalue` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
truncate TABLE tmp_table;
OPEN cur;
FETCH cur INTO t_name;
WHILE (done<>1) do
call do_replaces(orig_str,db_name,t_name);
FETCH cur INTO t_name;
END WHILE;
SELECT * from tmp_table;
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Procedure structure for do_replaces 循环添加查找到的字段
-- ----------------------------
DROP PROCEDURE IF EXISTS `do_replaces`;
delimiter ;;
CREATE PROCEDURE `do_replaces`(in orig_str varchar(100),in db_name varchar(100),in t_name varchar(100))
BEGIN
DECLARE cul_name VARCHAR(255);
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 DATA_TYPE='varchar' and CHARACTER_MAXIMUM_LENGTH >=8;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO cul_name;
set @update_sql=CONCAT("insert into tmp_table(columnvalue) select DISTINCT ", cul_name," as columnvalue from ", t_name, " where ", cul_name,
" LIKE '%", orig_str, "%' and EXISTS(select * from ",t_name," where ",cul_name," like '%",orig_str,"%');" );
prepare stmt from @update_sql;
execute stmt;
FETCH cur INTO cul_name;
END WHILE;
CLOSE cur;
END
;;
delimiter ;
调用:call found_source('XX','数据库名称'); 小心把库写死,大数据量需优化
大功告成 基本思路就是循环表先,然后循环表字段,完成所需! 欢迎留言!