MySQL 全文搜尋：拋棄 LIKE，擁抱 FULLTEXT 索引

為什麼不用 LIKE？

SELECT * FROM t WHERE col LIKE '%kw%' 在大數據量下全表掃描，效率極低，且無法按相關性排序。

MySQL 內建全文搜尋方案

1. 在需搜尋的欄位建立 FULLTEXT 索引。
2. 使用 MATCH(col) AGAINST('keyword') 查詢，MySQL 自動按相關性降序返回。

實戰演示

1. 建表

CREATE TABLE reviews (
  id INT(5) PRIMARY KEY NOT NULL AUTO_INCREMENT,
  data TEXT
);

2. 插入樣本

INSERT INTO reviews (data) VALUES
('Gingerboy has a new single out called Throwing Rocks. It\'s great!'),
('Hello all, I really like the new Madonna single. One of the hottest tracks...'),
('Have you heard the new band Hotter Than Hell? They burn instruments on stage!');

3. 建立全文索引

ALTER TABLE reviews ADD FULLTEXT INDEX (data);

4. 驗證索引

SHOW INDEXES FROM reviews;
+--------+------------+----------+--------------+-------------+-----------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Index_type |
+--------+------------+----------+--------------+-------------+-----------+
| reviews| 0          | PRIMARY  | 1            | id          | BTREE     |
| reviews| 1          | data     | 1            | data        | FULLTEXT  |
+--------+------------+----------+--------------+-------------+-----------+

5. 全文搜尋查詢

-- 自然語言模式（預設），按相關性排序
SELECT * FROM reviews
WHERE MATCH(data) AGAINST('new single');

-- 布爾模式，支援 + - * 等操作符
SELECT * FROM reviews
WHERE MATCH(data) AGAINST('+new -Madonna' IN BOOLEAN MODE);

優勢總結

• 性能：索引優化，避免全表掃描。
• 智能：自然語言處理，自動過濾無關結果。
• 靈活：支援布爾模式精確控制。

注意：MySQL 5.6+ 才支援 InnoDB 全文索引；早期版本僅 MyISAM。中文分詞需配合 ngram parser 或外部分詞工具。