MySQL 取逗號分隔字串首段值：SUBSTRING_INDEX 與 LEFT+LOCATE 兩種寫法

場景

欄位 ecnNO 存儲逗號分隔值（如 'a1,b2,c3'），需查詢第一段等於 'a1' 的記錄數。

寫法 1：SUBSTRING_INDEX（推薦，語義清晰）

SELECT COUNT(*) AS lot
FROM OA_sample_check
WHERE SUBSTRING_INDEX(ecnNO, ',', 1) = 'a1';

• SUBSTRING_INDEX(str, delim, count)：count > 0 返回第 count 個分隔符前的子串。
• 可讀性強，標準 SQL 函數，跨資料庫相容性佳。

寫法 2：LEFT + LOCATE（原文寫法）

SELECT COUNT(*) AS lot
FROM OA_sample_check
WHERE LEFT(ecnNO, LOCATE(',', ecnNO) - 1) = 'a1';

• LOCATE(',', ecnNO) 找到第一個逗號位置（1-based）。
• LEFT(..., pos-1) 取逗號前字串。
• ⚠️ 若無逗號，LOCATE 返回 0，LEFT(..., -1) 返回空串，邏輯需額外處理。

效能與索引建議

兩者皆為非 SARGable（無法使用索引），全表掃描。大表建議：

1. 正規化設計：拆分關聯表，建立外鍵索引。
2. 生成欄位/虛擬欄位（MySQL 5.7+）：

   ALTER TABLE OA_sample_check
   ADD COLUMN first_ecn VARCHAR(50)
   GENERATED ALWAYS AS (SUBSTRING_INDEX(ecnNO, ',', 1)) STORED,
   ADD INDEX idx_first_ecn (first_ecn);
   

3. 應用層寫入時同步冗存首段值。

擴展：取第 N 段 / 尾段

-- 第 2 段
SUBSTRING_INDEX(SUBSTRING_INDEX(ecnNO, ',', 2), ',', -1)

-- 最後一段
SUBSTRING_INDEX(ecnNO, ',', -1)

-- 反轉：以逗號分隔並取陣列第 N 個（MySQL 8.0+ JSON_TABLE 或遞迴 CTE）