又是项目所需,继续造轮子。替换指定数据库的「所有表」内「所有记录」的「所有字段」的值,以下为 SQL 语句。

0x00 创建存储过程

CREATE PROCEDURE `replace_table` (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(100);
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
# MySQL 8.0 亦可使用 REGEXP_REPLACE 函数支持正则替换
SET @update_sql = CONCAT("UPDATE `", t_name, "` SET `", cul_name, "` = REPLACE(`", cul_name, "`, '", orig_str, "', '", new_str, "')", " WHERE `", cul_name, "` REGEXP '" , ".*" , "';");
# 输出替换 SQL
-- SELECT @update_sql;
# 执行替换 SQL
PREPARE stmt FROM @update_sql;
EXECUTE stmt;
FETCH cur INTO cul_name;
END WHILE;
CLOSE cur;
END;

CREATE PROCEDURE `replace_db` (IN orig_str VARCHAR(100), IN new_str VARCHAR(100), IN db_name VARCHAR(100), IN tbl_name VARCHAR(100))
BEGIN
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 AND table_name LIKE tbl_name;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
FETCH cur INTO t_name;
WHILE (done<>1) DO
CALL replace_table(orig_str, new_str, db_name, t_name);
FETCH cur INTO t_name;
END WHILE;
END;

0x01 调用

CALL replace_db('<KEYWORD>', '<REPLACE_TO>', '<DB_NAME>', '<TBL_NAME>');
  • <KEYWORD>:被替换的字符串,如:xkedou.cn
  • <REPLACE_TO>:替换值,如:zjhejiang.com
  • <DB_NAME>:数据库名,如:zjhj_mall
  • <TBL_NAME>:表名,如:hjmall_% 指定表前缀。

以上示例参数可实现:将 zjhj_mall 数据库内所有前缀为 hjmall_ 的表内任何字段出现的 xkedou.cn 全部替换为 zjhejiang.com

另附正则替换图片域名实例:

SELECT REGEXP_REPLACE (
'http://foo.bar.com/addons/zjhj_mall/core/web/uploads/example.png',
'^(http|https)://foo.bar.com/(.*)(\.png|\.jpg|\.gif|\.jpeg)$',
'$1://bar.foo.com/$2$3'
);