MySQL Fulltext Search 使用方式

使用 MySQL 來達到 Fulltext 的效果, MySQL 對於 英文會自己依照空格去斷開, 中文就得要自行斷詞囉~

MySQL Fulltext Search 使用方式 與 注意事項

MySQL Fulltext 不支援 InnoDB, 需要使用 MyISAM.

建立 Table 時, 需要設定 FULLTEXT(Col-name).

搜尋語法

• SELECT * FROM table-name WHERE MATCH(col-name) AGAINST ('keyword')
• SELECT * FROM table-name WHERE MATCH(col-name) AGAINST ('+keyword' IN BOOLEAN MODE)
• SELECT MATCH('table-name') AGAINST ('+keyword') as Relevance FROM table-name WHERE MATCH ('table-name') AGAINST('+keyword1 +keyword2') HAVING Relevance > 0.2 ORDER BY Relevance DESC

範例

1. create database keyword;
2. use keyword;
3. create table kw (id char(255) primary key,FULLTEXT(id)) engine=myisam; # 若使用 InnoDB 會出現下述錯誤.
   # fulltext don't support innodb
   # ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

執行語法

• SELECT * FROM kw WHERE MATCH(id) AGAINST ('ABC' IN BOOLEAN MODE)
• SELECT * FROM kw WHERE MATCH(id) AGAINST ('+ABC' IN BOOLEAN MODE)
• SELECT * FROM kw WHERE MATCH(id) AGAINST ('*ABC*' IN BOOLEAN MODE)
• SELECT MATCH('id') AGAINST ('+ABC' ) as Relevance FROM kw WHERE MATCH ('id') AGAINST('+keyword1 +keyword2') HAVING Relevance > 0.2 ORDER BY Relevance DESC

相關網頁

• Using MySQL Full-text Searching
• MySQL fulltext example
• FULLTEXT indices are NOT supported in InnoDB tables
• Mysql FULLTEXT索引说明
• Mysql全文搜索match…against的用法