#10.13全文检索 、全文索引

#全文索引 只适用 MyISAM数据表 不适用InnoDB数据表

ALTER TABLE TABLE_NAME ADD FULLTEXT(COLUMN1,COLUMN2);

#全文检索 MATCH … AGAINST…

SELECT * FROM TABLE_NAME
  WHERE MATCH(COLUMN1,COLUMN2) AGAINST('XXX1 XXXX2 XXXX3')  > 0.001

#利用全文检索来进行排序

SELECT *
       ,MATCH(COLUMN1,COLUMN2) AGAINST ('XXXX1 XXX2 XXX3') MTCH
     FROM TABLE_NAME
    HAVING MTCH >0.001
    ORDER BY MTCH DESC
    LIMIT 5

#全文检索的应用实例

CREATE TABLE FULLTITLES ENGINE =MYISAM AS SELECT * FROM TITLES;

ALTER TABLE FULLTITLES ADD FULLTEXT(TITLE,SUBTITLE);

#查询方法1

SELECT * 
       ,MATCH(TITLE,SUBTITLE) AGAINST('EXCEL BASIC') MTCH
     FROM FULLTITLES
    HAVING MTCH >0.001
    ORDER BY MTCH DESC
    LIMIT 5;

#查询方法2

SELECT * 
    FROM FULLTITLES
   WHERE MATCH(TITLE,SUBTITLE) AGAINST('EXCEL BASIC') >0.001;

#布尔表达式检索

SELECT * 
       ,MATCH(TITLE,SUBTITLE) AGAINST('-EXCEL +BASIC' IN BOOLEAN MODE) MTCH
     FROM FULLTITLES
    HAVING MTCH >0.001
    ORDER BY MTCH DESC;

布尔表达式含义

符号 含义
+word 这个字符必须出现
-word 这个字符不允许出现
~word 这个字符串不优先考虑

word 增加这个字符串的匹配程度
<word 减少这个字符串的匹配程度
word* 搜索以这个字符串开关的单词
“word1 word2” 这两个字符串必须出现，并且出现的先后顺序一致