通用模糊查詢存儲過程：動態構建全字段 LIKE 條件

適用場景與限制

• **目標數據庫：SQL Server (T-SQL)**，非 MySQL。MySQL 不支持 sysobjects、information_schema.columns 游標寫法不同
• 適用於：後台管理、通用搜索接口、元數據驅動查詢
• **性能警告**：全表掃描、無索引利用、大表極慢，**嚴禁高併發核心業務使用**

存儲過程完整代碼

IF EXISTS (SELECT 1 FROM sysobjects WHERE Name = 'sp_search' AND xtype = 'P')
    DROP PROCEDURE sp_search;
GO

CREATE PROCEDURE sp_search
    @table_name VARCHAR(200),   -- 目標表名
    @condition  VARCHAR(100)    -- 搜索關鍵字
WITH ENCRYPTION  -- 加密定義文本（可選）
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @str_sql    VARCHAR(8000),
            @col_name   VARCHAR(100),
            @str_cols   VARCHAR(8000);
    
    SET @str_cols = '';
    
    -- 1. 獲取表所有列名（排除不適合 LIKE 的類型：image, text, ntext, xml, timestamp 等）
    DECLARE cur_cols CURSOR FOR
        SELECT COLUMN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = @table_name
          AND DATA_TYPE NOT IN ('text', 'ntext', 'image', 'xml', 'timestamp', 'binary', 'varbinary', 'hierarchyid', 'geometry', 'geography')
          AND CHARACTER_MAXIMUM_LENGTH IS NOT NULL;  -- 僅字符串類型
    
    OPEN cur_cols;
    FETCH NEXT FROM cur_cols INTO @col_name;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 2. 構建 OR 連接的 LIKE 條件：col LIKE '%keyword%'
        SET @str_cols = @str_cols + @col_name + ' LIKE ''%' + @condition + '%'' OR ';
        FETCH NEXT FROM cur_cols INTO @col_name;
    END
    
    CLOSE cur_cols;
    DEALLOCATE cur_cols;
    
    -- 3. 移除尾部多餘的 ' OR '
    IF LEN(@str_cols) > 0
        SET @str_cols = LEFT(@str_cols, LEN(@str_cols) - 4);
    
    -- 4. 構建動態 SQL
    IF LEN(@str_cols) > 0
    BEGIN
        SET @str_sql = 'SELECT * FROM ' + QUOTENAME(@table_name) + ' WHERE ' + @str_cols;
        -- PRINT @str_sql;  -- 調試用
        EXEC (@str_sql);
    END
    ELSE
    BEGIN
        -- 無可搜索列時返回空結果集
        SET @str_sql = 'SELECT * FROM ' + QUOTENAME(@table_name) + ' WHERE 1=0';
        EXEC (@str_sql);
    END
END;
GO

關鍵技術點解析

1. INFORMATION_SCHEMA.COLUMNS：標準元數據視圖，跨數據庫可移植
2. 游標遍歷列名，動態拼接 OR 條件
3. QUOTENAME()：防止表名注入，自動加 [] 括號
4. 過濾非字符串類型列，避免 LIKE 報錯
5. WITH ENCRYPTION：隱藏過程定義（非強加密，僅混淆）

調用示例

-- 搜索 Users 表中包含 'john' 的所有字符串列
EXEC sp_search 'Users', 'john';

-- 等效動態生成 SQL（假設 Users 有 name, email, phone, address 列）：
SELECT * FROM [Users] WHERE 
  [name] LIKE '%john%' OR 
  [email] LIKE '%john%' OR 
  [phone] LIKE '%john%' OR 
  [address] LIKE '%john%';

MySQL 版本實現參考

MySQL 存儲過程語法不同，需調整：

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_search` $$
CREATE PROCEDURE `sp_search`(IN p_table_name VARCHAR(64), IN p_condition VARCHAR(100))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_col_name VARCHAR(64);
  DECLARE v_where_clause TEXT DEFAULT '';
  DECLARE v_sql TEXT;
  
  DECLARE cur CURSOR FOR
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table_name
      AND DATA_TYPE IN ('char','varchar','text','tinytext','mediumtext','longtext');
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_col_name;
    IF done THEN LEAVE read_loop; END IF;
    SET v_where_clause = CONCAT(v_where_clause, '`', v_col_name, '` LIKE ', QUOTE(CONCAT('%', p_condition, '%')), ' OR ');
  END LOOP;
  CLOSE cur;
  
  IF LENGTH(v_where_clause) > 0 THEN
    SET v_where_clause = LEFT(v_where_clause, LENGTH(v_where_clause) - 4);
    SET v_sql = CONCAT('SELECT * FROM `', p_table_name, '` WHERE ', v_where_clause);
    SET @sql = v_sql;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  ELSE
    SELECT * FROM `p_table_name` WHERE 1=0;
  END IF;
END $$

DELIMITER ;

最佳實踐建議

1. 僅用於低頻、小表、後台管理：如用戶數 < 10萬、日志表 < 100萬
2. 大表必上全文索引：MySQL FULLTEXT、SQL Server FULLTEXT CATALOG、Elasticsearch/OpenSearch
3. 參數化防注入：上方代碼已用 QUOTE()/QUOTENAME() 處理關鍵字，表名仍有風險，建議白名單校驗
4. 結果集分頁：動態 SQL 外層包裝 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY (SQL Server 2012+) 或 LIMIT (MySQL)