萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 模糊查詢技巧:FIND_IN_SET 與 CONCAT-LIKE 解決逗號分隔欄位搜尋

問題場景

資料表 myTable 中欄位 myField 儲存逗號分隔字串:'a','b','c',...,'n'。需查詢包含特定值(如 'a')的所有記錄。

錯誤嘗試

-- 錯誤:IN 運算子不支援在字串中搜尋
SELECT * FROM myTable WHERE 'a' IN (myField);

方案一:FIND_IN_SET() 函數(MySQL 專用,最簡潔)

SELECT * FROM myTable WHERE FIND_IN_SET('a', myField) > 0;

原理:FIND_IN_SET(str, strlist) 回傳 str 在逗號分隔的 strlist 中的位置(1-based),找不到回傳 0。要求分隔符必須是逗號,且字串中不能包含逗號作為資料一部分。

方案二:CONCAT + LIKE 通用寫法(跨資料庫相容)

-- 假設以逗號分隔
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
  • 高頻/大數據查詢**必須**正規化拆表
  • 新系統設計**堅決拒絕**逗號分隔欄位
copyright © 萬盛學電腦網 all rights reserved