【SQL】快速查询表结构字段包含的文本

查询

CREATE DEFINER=`root`@`%` PROCEDURE `BatchSearchAndStore`()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE current_table VARCHAR(64);
    DECLARE current_column VARCHAR(64);
    DECLARE current_type VARCHAR(64);
    DECLARE match_count BIGINT DEFAULT 0;
    DECLARE has_error INT DEFAULT FALSE;
    
    -- 游标必须在 HANDLER 之前声明
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = 'database_name'
          AND DATA_TYPE IN ('varchar', 'char', 'text', 'tinytext', 'mediumtext', 'longtext', 'json')
        ORDER BY TABLE_NAME, ORDINAL_POSITION;
    
    -- HANDLER 必须在游标之后，其他声明之前
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = TRUE;
    
    -- 创建结果表
    DROP TABLE IF EXISTS search_results;
    CREATE TABLE search_results (
      id INT AUTO_INCREMENT PRIMARY KEY,
      table_name VARCHAR(64),
      column_name VARCHAR(64),
      data_type VARCHAR(64),
      match_count BIGINT,
      search_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      INDEX idx_table (table_name),
      INDEX idx_count (match_count)
    );
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO current_table, current_column, current_type;
        IF done THEN LEAVE read_loop; END IF;
        
        -- 重置错误标记
        SET has_error = FALSE;
        SET @cnt = 0;
        SET @tbl = current_table;
        SET @col = current_column;
        
        -- 构建动态SQL
        SET @sql = CONCAT(
          'SELECT COUNT(*) INTO @cnt FROM `database_name`.`', @tbl, 
          '` WHERE `', @col, '` LIKE ''%cos.accelerate.myqcloud.com%'''
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 如果没有错误且有匹配，插入结果
        IF NOT has_error AND @cnt > 0 THEN
            INSERT INTO search_results (table_name, column_name, data_type, match_count) 
            VALUES (current_table, current_column, current_type, @cnt);
        END IF;
        
    END LOOP read_loop;
    CLOSE cur;
    
    -- 返回结果
    SELECT * FROM search_results ORDER BY match_count DESC;
    
END

调用

CALL BatchSearchAndStore();

查询结果

SELECT * FROM search_results ORDER BY table_name DESC;

批量生成语句

SET @old_url = 'https://xk-1327267808.cos.accelerate.myqcloud.com';
SET @new_url = 'https://simtrum-1327267808.cos.accelerate.myqcloud.com';

-- 生成详细的 UPDATE 语句
SELECT CONCAT(
    '-- ', table_name, '.', column_name, ' (', match_count, '条)\n',
    'UPDATE `database_name`.`', table_name, 
    '` SET `', column_name, 
    '` = REPLACE(`', column_name, '`, ''', @old_url, ''', ''', @new_url, 
    ''') WHERE `', column_name, '` LIKE ''%', @old_url, '%'';'
) AS update_sql
FROM search_results
WHERE match_count > 0
ORDER BY table_name, column_name;