本网站更换图床地址的SQL案例

DELIMITER //

DROP PROCEDURE IF EXISTS replace_string_in_all_tables_columns //

CREATE PROCEDURE replace_string_in_all_tables_columns(
    IN old_string VARCHAR(255),
    IN new_string VARCHAR(255)
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE col_name VARCHAR(255);
    DECLARE data_type VARCHAR(255);
    DECLARE update_sql TEXT;

    -- 声明游标，遍历所有可能包含目标字符串的字符串类型列
    DECLARE cur CURSOR FOR
        SELECT
            TABLE_NAME,
            COLUMN_NAME,
            DATA_TYPE
        FROM
            information_schema.COLUMNS
        WHERE
            TABLE_SCHEMA = DATABASE() -- 只在当前数据库中操作
            AND DATA_TYPE IN ('varchar', 'char', 'text', 'mediumtext', 'longtext')
            AND TABLE_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys'); -- 排除系统表

    -- 声明中断处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tbl_name, col_name, data_type;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 构建 UPDATE 语句
        SET update_sql = CONCAT(
            'UPDATE `', tbl_name,
            '` SET `', col_name,
            '` = REPLACE(`', col_name,
            '`, ''', old_string,
            ''', ''', new_string,
            ''') WHERE `', col_name,
            '` LIKE ''%', old_string, '%'''
        );

        -- 打印生成的SQL语句 (可选，用于调试)
        SELECT update_sql AS Generated_SQL_Statement;

        -- 执行SQL语句
        SET @dynamic_sql = update_sql;
        PREPARE stmt FROM @dynamic_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    CLOSE cur;

    SELECT '替换操作完成。' AS Status;

END;
//

DELIMITER ;

-- 调用存储过程来执行替换操作
CALL replace_string_in_all_tables_columns(
    'zengqueling.oss-cn-shenzhen.aliyuncs.com',
    'zengqueling.oss-cn-shenzhen.aliyuncs.com'
);

DROP PROCEDURE replace_string_in_all_tables_columns;