使用 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
範例
- create database keyword;
- use keyword;
- 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