資料表 myTable 中欄位 myField 儲存逗號分隔字串:'a','b','c',...,'n'。需查詢包含特定值(如 'a')的所有記錄。
-- 錯誤:IN 運算子不支援在字串中搜尋
SELECT * FROM myTable WHERE 'a' IN (myField);
SELECT * FROM myTable WHERE FIND_IN_SET('a', myField) > 0;
原理:FIND_IN_SET(str, strlist) 回傳 str 在逗號分隔的 strlist 中的位置(1-based),找不到回傳 0。要求分隔符必須是逗號,且字串中不能包含逗號作為資料一部分。
-- 假設以逗號分隔
SELECT * FROM myTable WHERE CONCAT(',', myField, ',') LIKE '%,a,%';
-- 其他分隔符(如分號、管線符)替換逗號即可
SELECT * FROM myTable WHERE CONCAT('|', myField, '|') LIKE '%|a|%';
核心技巧:前後加上分隔符,確保邊界匹配,避免 'a' 匹配到 'ab' 或 'ba'。
| 方案 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|
| FIND_IN_SET | 語法簡潔、可讀性高 | MySQL 專用、無法使用索引、大數據慢 | 小表、臨時查詢、分隔符固定為逗號 |
| CONCAT+LIKE | 標準 SQL、跨資料庫 | 全表掃描、無法用索引、需注意轉義 | 分隔符非逗號、跨平台相容 |
| 正規化設計 | 可建索引、效能最好、符合範式 | 需修改結構、遷移成本高 | 新系統設計、高頻查詢、大數據量 |
將逗號分隔欄位拆分為關聯表:
-- 原表
CREATE TABLE myTable (
id INT PRIMARY KEY,
myField VARCHAR(1000) -- 'a,b,c'
);
-- 正規化後
CREATE TABLE myTable (
id INT PRIMARY KEY,
-- 其他欄位
);
CREATE TABLE myTable_tags (
id INT PRIMARY KEY AUTO_INCREMENT,
myTable_id INT NOT NULL,
tag VARCHAR(50) NOT NULL,
INDEX idx_tag (tag),
FOREIGN KEY (myTable_id) REFERENCES myTable(id)
);
-- 查詢變為高效索引查詢
SELECT m.* FROM myTable m
JOIN myTable_tags t ON m.id = t.myTable_id
WHERE t.tag = 'a';
<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$res = $mysqli->query("SELECT id, myField FROM myTable");
while ($row = $res->fetch_assoc()) {
$tags = explode(',', trim($row['myField'], ",' "));
foreach ($tags as $tag) {
$tag = trim($tag, "' ");
if ($tag) {
$mysqli->query("INSERT INTO myTable_tags (myTable_id, tag) VALUES ({$row['id']}, '$tag')");
}
}
}
echo "遷移完成";
?>
FIND_IN_SET 或 CONCAT+LIKE