mysql查找字符串并替换字符串_mysql在某个数据库中查找字符串并替换

########## 功能：在某个数据库中查找字符串并替换 ##########

# 初始化设置(设置后运行整个代码即可)

# 数据库名

USE `数据库名`;

set @var_db_name = '数据库名';

# 查找字符串

set @var_search = 'xxxxxx';

# 替换字符串

set @var_replacement = 'xxxxxx';

##########################################################

DELIMITER $$

DROP PROCEDURE IF EXISTS `db_replace`$$

DROP PROCEDURE IF EXISTS `tbl_replace`$$

#在数据库中查找替换

CREATE PROCEDURE `db_replace`(

IN `search` varchar(100),

IN `replacement` varchar(100),

IN `db_name` varchar(100)

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

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 tbl_replace(search,replacement,db_name,t_name);

FETCH cur INTO t_name;

END WHILE;

END$$

#在数据表中查找替换

CREATE PROCEDURE `tbl_replace`(

IN `search` varchar(100),

IN `replacement` varchar(100),

IN `db_name` varchar(100),

IN `t_name` varchar(100)

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

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,"`,'",search,"','",replacement,"')");

prepare stmt from @update_sql;

execute stmt;

FETCH cur INTO cul_name;

END WHILE;

CLOSE cur;

END$$

#运行主程序

call db_replace(@var_search,@var_replacement,@var_db_name)$$

DROP PROCEDURE IF EXISTS `db_replace`$$

DROP PROCEDURE IF EXISTS `tbl_replace`$$

DELIMITER ;