MySql全库检索指定字符串

DELIMITER //

CREATE PROCEDURE SearchAllTablesForString(IN searchStr VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE colName VARCHAR(255);

    -- 游标：获取包含varchar或text类型字段的所有表
    DECLARE curTables CURSOR FOR
        SELECT DISTINCT TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND DATA_TYPE IN ('varchar', 'text', 'longtext', 'mediumtext', 'tinytext');

    -- 游标：获取当前表中的varchar或text类型字段
    DECLARE curColumns CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = tableName
          AND DATA_TYPE IN ('varchar', 'text', 'longtext', 'mediumtext', 'tinytext');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储结果，增加字段值列
    DROP TABLE IF EXISTS search_results;
    CREATE TEMPORARY TABLE search_results (
                                              table_name VARCHAR(255),
                                              record_id VARCHAR(255),
                                              column_name VARCHAR(255),
                                              column_value TEXT  -- 新增：存储匹配的字段值
    );

    -- 遍历所有包含varchar或text字段的表
    OPEN curTables;
    tableLoop: LOOP
        FETCH curTables INTO tableName;
        IF done THEN
            LEAVE tableLoop;
        END IF;

        -- 检查当前表是否有id字段
        SET @idExists = 0;
        SELECT COUNT(*) INTO @idExists
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = tableName
          AND COLUMN_NAME = 'id';

        -- 如果没有id字段，跳过这个表
        IF @idExists = 0 THEN
            ITERATE tableLoop;
        END IF;

        -- 重置done变量
        SET done = FALSE;

        -- 遍历当前表中的varchar或text字段
        OPEN curColumns;
        columnLoop: LOOP
            FETCH curColumns INTO colName;
            IF done THEN
                LEAVE columnLoop;
            END IF;

            -- 构建动态SQL查询，增加字段值的查询
            SET @sql = CONCAT(
                    'INSERT INTO search_results (table_name, record_id, column_name, column_value) ',
                    'SELECT ''', tableName, ''', CAST(id AS CHAR), ''', colName, ''', ', colName, ' ',
                    'FROM ', tableName, ' ',
                    'WHERE ', colName, ' LIKE ''%', searchStr, '%'''
                       );

            -- 执行动态SQL
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END LOOP columnLoop;
        CLOSE curColumns;
    END LOOP tableLoop;
    CLOSE curTables;

    -- 返回结果
    SELECT * FROM search_results;

    -- 清理临时表
    DROP TABLE IF EXISTS search_results;
END //

DELIMITER ;

然后执行

CALL SearchAllTablesForString('需要查找的字符');