SQL 大量重複 IN 列表優化：臨時表/子查詢消除硬編碼參數

痛點場景

備份與刪除操作需針對 21 個固定 aa 值，多張表（A/B/C/D/E...）重複出現相同的超長 IN (...) 列表，維護極其困難：

-- 重複 5+ 次的硬編碼列表
WHERE aa IN ('eimdmc','30mdnci','cienc03',...,'300993983d')

優化方案：集中管理參數集合

方案一：臨時表/變量表（推薦）

-- 1. 創建參數表（永久或臨時）
CREATE TABLE filter_values (val VARCHAR(50) PRIMARY KEY);
INSERT INTO filter_values VALUES 
('eimdmc'),('30mdnci'),('cienc03'),...,('300993983d');

-- 2. 所有查詢統一引用
CREATE TABLE A_1 AS SELECT * FROM A WHERE aa IN (SELECT val FROM filter_values);
DELETE FROM A WHERE aa IN (SELECT val FROM filter_values);
-- B/C/D/E 同理

方案二：CTE（公用表表達式，單語句場景）

WITH params AS (
  SELECT 'eimdmc' AS val UNION ALL
  SELECT '30mdnci' UNION ALL
  SELECT 'cienc03' UNION ALL ...
)
DELETE FROM A WHERE aa IN (SELECT val FROM params);

方案三：應用層構建動態 SQL（參數化）

-- 概念示例
List ids = Arrays.asList("eimdmc", "30mdnci", ...);
String placeholders = ids.stream().map(i -> "?").collect(Collectors.joining(","));
String sql = "DELETE FROM A WHERE aa IN (" + placeholders + ")";
// PreparedStatement 批量設置參數

優勢對比

進階技巧：大數據量時的性能優化

1. 為 filter_values.val 建索引，子查詢轉為高效連接（JOIN）。
2. 分區表：若目標表巨大，按 aa 分區，配合分區裁剪。
3. 批次刪除：避免單次事務過大導致鎖升級/日誌爆漲，分批 LIMIT 10000 循環刪除。

「將數據從代碼中分離」是 SQL 工程化的第一步 —— 用表管理列表，讓 SQL 回歸純邏輯。