索引 - 全文索引 ¶

• 作者：KK

• 发表日期：2017.5.26

介绍 ¶

全文索引可以提供全更好的文本搜索功能，有点类似百度搜索，当然这级别上是相差甚远的

创建全文索引 ¶

#建表
CREATE TABLE article(
	id INT AUTO_INCREMENT,
	content TEXT,
	PRIMARY KEY(id)
);


#为content字段创建全文索引
ALTER TABLE article ADD FULLTEXT(content);


#插入3条记录以备后面查询测试
INSERT INTO article(content) VALUES('apple pear bird'),('if then else bird'),('you and me haha what?');

其中必须是CHAR、VARCHAR和TEXT类型的字段才可以建立全文索引

使用全文索引 ¶

平时查找一个文本字段中是否包含“apple”这个单词的时候条件是content LIKE '%apple%'这样写的，如果要基于全文索引的支持下来使用更优越的搜索功能可就不是用LIKE匹配了

需要通过特殊的语法和关键字才能使用全文索引，例子：

SELECT * FROM article WHERE MATCH(content) AGAINST('apple');

语法就是MATCH(要查找的全文索引字段) AGAINST(查找关键词)

查找多个关键词 ¶

SELECT * FROM article WHERE MATCH(content) AGAINST('apple haha');


/*输出：*/
+----+-----------------------+
| id | content               |
+----+-----------------------+
|  3 | you and me haha what? |
|  1 | apple pear bird       |
+----+-----------------------+

所以AGAINST是可以指定多个关键词的，并且用空格隔开

只能查找英文，不区分大小写 ¶

这下坑了咱们基本都打算搜索中文内容啊，那要不要学下去好呢……你自己决定吧，哦忘了告诉你，搜中文就用Sphinx

只针对单词索引 ¶

可是如果要MATCH(content) AGAINST('appl')这样匹配是匹配不出第一条带“apple”的记录的，原因是全文索引它是按照单词来建立索引的，所以它就找不到“appl”这个词

只索引指定长度以上的单词 ¶

可是如果MATCH(content) AGAINST('if')也是匹配不到数据的，为什么呢？上面插入的测试数据中不是有if这个词的吗？——答案就是这个词太短了！怎么才知道是长是短？执行show variables like '%min_word%';看看ft_min_word_len这个配置项的值，我这里的值是4，有的可能是3，通常不会小于3，这个配置的意思是说：一个词的字符数量大于等于这个数才会建立索引，所以我的配置就是4个字符或以上才会建索引，“if”只有2个字符，MySql并不为它建立索引

• 修改长度限制：要修改这个长度就在my.ini里改

  比如要修改为3个字符，就在my.ini里的[mysqld]区域添加ft_min_word_len=3这样就可以了

只有MyISAM和InnoDb引擎的表才能使用全文索引 ¶

MySql5.6版本以下的InnoDb引擎不支持全文索引的哦

出现率达到50%以上的词不会被搜索到 ¶

比如上面插入的3条测试数据中，有2条是包含了“bird”这个词的，所以出现频率是2/3，达到50%上了，于是SELECT * FROM article WHERE MATCH(content) AGAINST('bird');是查不出数据的

搜索某些单词会被忽略 ¶

MySql还设置了一些忽略词，比如上面的记录中无法搜索到“then”这个词（客官请复制测试SELECT * FROM article WHERE MATCH(content) AGAINST('then');），当然了因为它就是一个忽略词

根据我在网上搜集的忽略词大概有这些：

"a", "a's", "able", "about", "above", "according", "accordingly", "across", "actually", "after", "afterwards", "again", "against", "ain't", "all", "allow", "allows", "almost", "alone", "along", "already", "also", "although", "always", "am", "among", "amongst", "an", "and", "another", "any", "anybody", "anyhow", "anyone", "anything", "anyway", "anyways", "anywhere", "apart", "appear", "appreciate", "appropriate", "are", "aren't", "around", "as", "aside", "ask", "asking", "associated", "at", "available", "away", "awfully", "b", "be", "became", "because", "become", "becomes", "becoming", "been", "before", "beforehand", "behind", "being", "believe", "below", "beside", "besides", "best", "better", "between", "beyond", "both", "brief", "but", "by", "c", "c'mon", "c's", "came", "can", "can't", "cannot", "cant", "cause", "causes", "certain", "certainly", "changes", "clearly", "co", "com", "come", "comes", "concerning", "consequently", "consider", "considering", "contain", "containing", "contains", "corresponding", "could", "couldn't", "course", "currently", "d", "definitely", "described", "despite", "did", "didn't", "different", "do", "does", "doesn't", "doing", "don't", "done", "down", "downwards", "during", "e", "each", "edu", "eg", "eight", "either", "else", "elsewhere", "enough", "entirely", "especially", "et", "etc", "even", "ever", "every", "everybody", "everyone", "everything", "everywhere", "ex", "exactly", "example", "except", "f", "far", "few", "fifth", "first", "five", "followed", "following", "follows", "for", "former", "formerly", "forth", "four", "from", "further", "furthermore", "g", "get", "gets", "getting", "given", "gives", "Go", "goes", "going", "gone", "got", "gotten", "greetings", "h", "had", "hadn't", "happens", "hardly", "has", "hasn't", "have", "haven't", "having", "he", "he's", "hello", "help", "hence", "her", "here", "here's", "hereafter", "hereby", "herein", "hereupon", "hers", "herself", "hi", "him", "himself", "his", "hither", "hopefully", "how", "howbeit", "however", "i", "i'd", "i'll", "i'm", "i've", "ie", "if", "ignored", "immediate", "in", "inasmuch", "inc", "indeed", "indicate", "indicated", "indicates", "inner", "insofar", "instead", "into", "inward", "is", "isn't", "it", "it'd", "it'll", "it's", "its", "itself", "j", "just", "k", "keep", "keeps", "kept", "know", "knows", "known", "l", "last", "lately", "later", "latter", "latterly", "least", "less", "lest", "let", "let's", "like", "liked", "likely", "little", "look", "looking", "looks", "ltd", "m", "mainly", "many", "may", "maybe", "me", "mean", "meanwhile", "merely", "might", "more", "moreover", "most", "mostly", "much", "must", "my", "myself", "n", "name", "namely", "nd", "near", "nearly", "necessary", "need", "needs", "neither", "never", "nevertheless", "new", "next", "nine", "no", "nobody", "non", "none", "noone", "nor", "normally", "not", "nothing", "novel", "now", "nowhere", "o", "obviously", "of", "off", "often", "oh", "ok", "okay", "old", "on", "once", "one", "ones", "only", "onto", "or", "other", "others", "otherwise", "ought", "our", "ours", "ourselves", "out", "outside", "over", "overall", "own", "p", "particular", "particularly", "per", "perhaps", "placed", "please", "plus", "possible", "presumably", "probably", "provides", "q", "que", "quite", "qv", "r", "rather", "rd", "re", "really", "reasonably", "regarding", "regardless", "regards", "relatively", "respectively", "right", "s", "said", "same", "saw", "say", "saying", "says", "second", "secondly", "see", "seeing", "seem", "seemed", "seeming", "seems", "seen", "self", "selves", "sensible", "sent", "serious", "seriously", "seven", "several", "shall", "she", "should", "shouldn't", "since", "six", "so", "some", "somebody", "somehow", "someone", "something", "sometime", "sometimes", "somewhat", "somewhere", "soon", "sorry", "specified", "specify", "specifying", "still", "sub", "such", "sup", "sure", "t", "t's", "take", "taken", "tell", "tends", "th", "than", "thank", "thanks", "thanx", "that", "that's", "thats", "the", "their", "theirs", "them", "themselves", "then", "thence", "there", "there's", "thereafter", "thereby", "therefore", "therein", "theres", "thereupon", "these", "they", "they'd", "they'll", "they're", "they've", "think", "third", "this", "thorough", "thoroughly", "those", "though", "three", "through", "throughout", "thru", "thus", "to", "together", "too", "took", "toward", "towards", "tried", "tries", "truly", "try", "trying", "twice", "two", "u", "un", "under", "unfortunately", "unless", "unlikely", "until", "unto", "up", "upon", "us", "use", "used", "useful", "uses", "using", "usually", "v", "value", "various", "very", "via", "viz", "vs", "w", "want", "wants", "was", "wasn't", "way", "we", "we'd", "we'll", "we're", "we've", "welcome", "well", "went", "were", "weren't", "what", "what's", "whatever", "when", "whence", "whenever", "where", "where's", "whereafter", "whereas", "whereby", "wherein", "whereupon", "wherever", "whether", "which", "while", "whither", "who", "who's", "whoever", "whole", "whom", "whose", "why", "will", "willing", "wish", "with", "within", "without", "won't", "wonder", "would", "would", "wouldn't", "x", "y", "yes", "yet", "you", "you'd", "you'll", "you're", "you've", "your", "yours", "yourself", "yourselves", "z", "zero"

如果要修改忽略词，需要修改MySql源代码然后重新编译，修改的位置是myisam/ft_static.c这个文件，最后还得把旧的全文索引删除，重新建立索引

返回的记录是按匹配度排序的 ¶