MySQL 字串處理測試資料集：FIND_IN_SET、INSTR、CONCAT 實戰範例

測試場景

多對多關聯中，files.f_mids 以逗號分隔儲存會員 ID (如 '1,2,3')，需查詢某會員可存取的檔案清單。

建表與樣本資料

CREATE TABLE category (
    c_id BIGINT PRIMARY KEY,
    c_name VARCHAR(255) DEFAULT '',
    c_type INT DEFAULT 1
);

CREATE TABLE files (
    f_id BIGINT PRIMARY KEY,
    c_id BIGINT NOT NULL,
    f_name VARCHAR(255) DEFAULT '',
    f_mids TEXT  -- 逗號分隔會員 ID
);

CREATE TABLE members (
    m_id BIGINT PRIMARY KEY,
    m_name VARCHAR(255) DEFAULT ''
);

-- 插入測試資料 (略，見原始碼)

核心查詢：會員 ID=1 可存取檔案

方法一：INSTR + CONCAT (相容舊版)

SELECT f_id, f_name, f_mids,
       INSTR(CONCAT(',', f_mids, ','), ',1,') AS matched
FROM files
WHERE INSTR(CONCAT(',', f_mids, ','), ',1,') > 0;

原理： 前後加逗號確保邊界匹配，避免 1 誤匹配 11、21。

方法二：FIND_IN_SET (MySQL 專屬、語意清晰)

SELECT f_id, f_name, f_mids
FROM files
WHERE FIND_IN_SET('1', f_mids) > 0;

• 內部同樣做字串掃描，但語法可讀性高。
• 限制： 單值長度 < 65535 字元、集合值不含逗號。

方法三：JSON_CONTAINS (5.7+ 推薦重構)

-- 將 f_mids 改為 JSON 陣列 [1,2,3]
ALTER TABLE files MODIFY f_mids JSON;
UPDATE files SET f_mids = JSON_ARRAY(1,2) WHERE f_id=1;

-- 查詢
SELECT * FROM files WHERE JSON_CONTAINS(f_mids, '1', '$');

• 支援索引 (虛擬欄位 + Functional Index 8.0+)。
• 型別安全、可直接 JOIN。

效能對比 (萬筆測試)

最佳實務建議

1. 新系統： 直接用關聯表 file_members(file_id, member_id)，外鍵約束、索引完備、正規化。
2. 舊系統無法改表： 使用 FIND_IN_SET 維持可讀性；極大表考慮 TRIGGER 同步至關聯表。
3. 避免： LIKE '%,1,%' (邊界漏洞)、REGEXP '[[:<:]]1[[:>:]]' (效能差)。

逗號分隔欄位是反模式，能重構盡早重構；暫時共存時，FIND_IN_SET 是最平衡的選擇。

1. 上一篇文章： MySQL 主從複寫與雙主熱備配置全攻略 (my.cnf 實戰範本)
2. 下一篇文章： SQL 數據排序與排名技巧：兩種高效實現方法詳解

MySQL 資料庫相關知識

MySQL 查詢資料編碼詳解：mysql_escape_string() 實戰

為何需要資料編碼？ SQL 語句以字串拼接方式構建時，資料中

Perl DBI 命令列實戰：多格式目錄產生、會員續費通知與資料編輯

專案目標與腳本規劃 本章完成「歷史同盟」四大命令列任務：

MySQL基礎查詢大全：WHERE/IN/BETWEEN/LIKE/NULL/排序/分組/正則/LIMIT實戰語法速查

參考表結構 student(id, name, birth,

MySQL 啟動失敗「No space left on device」三大案例與解決方案

MySQL 無法啟動並報錯 Cant start serve