mysql基础语法之（全⽂索引）
1.概要
InnoDB引擎对FULLTEXT索引的⽀持是MySQL5.6新引⼊的特性，之前只有MyISAM引擎⽀持FULLTEXT索引。对于FULLTEXT索引的内容可以使⽤MATCH()…AGAINST语法进⾏查询。
为了在InnoDB驱动的表中使⽤FULLTEXT索引MySQL5.6引⼊了⼀些新的配置选项和INFORMATION_SCHEMA表。⽐如，为了监视⼀个FULLTEXT索引中⽂本处理过程的某⼀⽅⾯可以查询
INNODB_FT_CONFIG，INNODB_FT_INDEX_TABLE，INNODB_FT_INDEX_CACHE,INNODB_FT_DEFAULT_STOPWORD，INNODB_FT_DELETED 和INNODB_FT_BEING_DELETED这些表。可以通过innodb_ft_num_word_optimize和innodb_optimize_fulltext_only选项控制OPTIMIZETABLE命令对InnoDB FULLTEXT索引的更新。
2.相关库表
INFORMATION_SCHEMA库中与InnoDB全⽂索引相关的表如下：
Ø INNODB_SYS_INDEXES：提供了InnoDB索引的状态信息。
Ø INNODB_SYS_TABLES：提供了InnoDB表的状态信息。
Ø INNODB_FT_CONFIG：显⽰⼀个InnoDB表的FULLTEXT索引及其相关处理的元数据。
Ø INNODB_FT_INDEX_TABLE：转化后的索引信息⽤于处理基于InnoDB表FULLTEXT索引的⽂本搜索。⼀般⽤于调试诊断⽬的。使⽤该表前需先配置innodb_ft_aux_table配置选项，将其指定为想要查看的含FULLTEXT索引的InnoDB表，选项值的格式为database_name/table_name。配置了该选项后INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table配置选项指定的表关联的搜索索引相关信息。
Ø INNODB_FT_INDEX_CACHE：向含FULLTEXT索引的InnoDB表插⼊数据后新插⼊数据转后的索引信息。表结构与INNODB_FT_INDEX_TABLE⼀致。为含FULLTEXT索引的InnoDB表执⾏DML操作期间重组索引开销很⼤，因此将新插⼊的被索引的词单独存储于该表中，当且仅当为InnoDB表执⾏OPTIMIZE TABLE语句后才将新的转换后的索引信息与原有的主索引信息合并。使⽤该表前需先配置innodb_ft_aux_table配置选项。
Ø INNODB_FT_DEFAULT_STOPWORD：在InnoDB表上创建FULLTEXT索引所使⽤的默认停⽌字表。
Ø INNODB_FT_DELETED：记录了从InnoDB表FULLTEXT索引中删除的⾏。为了避免为InnoDB的FULLTEXT索引执⾏DML操作期间重组索引的⾼开销，新删除的词的信息单独存储于此表。当且仅当为此InnoDB表执⾏了OPTIMIZE TABLE操作后才会从主搜索索引中移除已删除的词信息。使⽤该表前需先配置innodb_ft_aux_table选项。
Ø INNODB_FT_BEING_DELETED：为含FULLTEXT索引的InnoDB表执⾏OPTIMIZE TABLE操作时会根据INNODB_FT_DELETED表中记录的⽂档ID从InnoDB表的FULLTEXT索引中删除相应的索引信息。⽽INNOFB_FT_BEING_DELETED表⽤于记录正在被删除的信息，⽤于监控和调试⽬的。
3.相关配置选项
Name Cmd-
Line
Option file System Var Status Var Scope Dynamic Yes Yes Yes Global Yes Yes Yes Yes Global No
Yes Yes Yes Global Yes Yes Yes Yes Global Yes Yes Yes Yes Global No
Yes Yes Yes Global No
Yes Yes Yes Global Yes Yes Yes Yes Global Yes Yes Yes Yes Global No
Yes Yes Yes Both Yes Yes Yes Yes Global Yes
Ø innodb_ft_aux_table：指定包含FULLTEXT索引的InnoDB表的的名称。该变量在运⾏时设置⽤于诊断⽬的。设置该值后INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG,INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table指定的表关联的搜索索引相关信息。
Ø innodb_ft_cache_size：当创建⼀个InnoDB FULLTEXT索引时在内存中存储已解析⽂档的缓存⼤⼩。
Ø innodb_ft_enable_diag_print：是否开启额外的全⽂搜索诊断输出。
Ø innodb_ft_enable_stopword：是否开启停⽌字。InnoDB FUllTEXT索引被创建时为其指定⼀个关联的停⽌字集。（若设置了innodb_ft_user_stopword_table 则停⽌字由该选项指定的表获取，若没有设置innodb_ft_user_stopword_table⽽设置了innodb_ft_server_stopword_table则停⽌字由该选项指定的表获取，否
则使⽤内置的停⽌字。）
Ø innodb_ft_max_token_size：存储在InnoDB的FULLTEXT索引中的最⼤词长。设置这样⼀个限制后可通过忽略过长的关键字等有效降低索引⼤⼩从⽽加速查询。
Ø innodb_ft_min_token_size：存储在InnoDB的FULLTEXT索引中的最⼩词长。增加该值后会忽略掉⼀些通⽤的没有显著意义的词汇从⽽降低索引⼤⼩继⽽加速查询。
Ø innodb_ft_num_word_optimize：为InnoDB FULLTEXT索引执⾏OPTIMIZE操作每次所处理的词数。因为在含有全⽂搜索索引的表中执⾏批量的插⼊或更新操作需要⼤量的索引维护操作来合并所有的变化。因此，⼀般会运⾏⼀系列OPTIMIZE TABLE语句，每次从上⼀次的位置开始，处理指定数⽬的词，知道搜索索引被完全更新。
Ø innodb_ft_server_stopword_table：含有停⽌字的表，在创建InnoDB FULLTEXT索引时或忽略表中的停⽌字。停⽌字表需为InnoDB表，且在指定前应当已存在。
Ø innodb_ft_sort_pll_degree：为较⼤的表构建搜索索引时⽤于索引和记号化⽂本的并⾏线程数。
Ø innodb_ft_user_stopword_table：含有停⽌字的表，在创建InnoDB FULLTEXT索引时或忽略表中的停⽌字。停⽌字表需为InnoDB表，且在指定前应当已存在。
Ø innodb_optimize_fulltext_only：改变OPTIMIZE TABLE语句对InnoDB表操作的⽅式。对含FULLTE
XT 索引的InnoDB表进⾏维护操作期间，⼀般临时的开启该选项。默认情况下，OPTIMIZE TABLE语句会重组表的聚集索引中的数据。若开启了该选项则该语句会跳过表数据的重组，⽽是只处理FULLTEXT索引中新插⼊的、删除的、更新的标记数据。（在对作为FULLTEXT索引的⼀部分的InnoDB表列进⾏了⼤量的插⼊、更新或删除操作后，先将
innodb_optimize_fulltext_only设置为on以改变OPTIMIZE TABLE的默认⾏为，然后设置innodb_ft_num_word_optimize为合适的值以将索引维护时间控制在⼀个合理的可接受范围内，最后执⾏⼀系列的OPTIMIZE语句知道搜索索引被完全更新。）
4.全⽂搜索功能
全⽂搜索的语法：MATCH(col1,col2,…) AGAINST (expr[search_modifier])。
其中MATCH中的内容为已建⽴FULLTEXT索引并要从中查数据的列，AGAINST中的expr为要查的⽂本内容，search_modifier为可选搜索类型。search_modifier的可能取值有：
IN NATURAL LANGUAGEMODE、
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、
IN BOOLEAN MODE、
WITH QUERY EXPANSION。
search_modifier的每个取值代表⼀种类型的全⽂搜索，分别为⾃然语⾔全⽂搜索、带查询扩展的⾃然语⾔全⽂搜索、布尔全⽂搜索、查询扩展全⽂搜索（默认使⽤IN NATURAL LANGUAGE MODE）。
MySQL中全⽂索引的关键字为FULLTEXT，⽬前可对MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT类型的列创建全⽂索引。全⽂索引同其他索引⼀样，可在创建表是由CREATE TABLE语句创建也可以在表创建之后⽤ALTER TABLE或者CREATE INDEX命令创建(对于要导⼊⼤量数据的表先导⼊数据再创建FULLTEXT索引⽐先创建索引后导⼊数据会更快)。
4.1⾃然语⾔全⽂搜索
⾃然语⾔全⽂搜索是MySQL全⽂搜索的默认搜索⽅式，实现从⼀个⽂本集合中搜索给定的字符串。这⾥，⽂本集合指的是指由FULLTEXT索引的⼀个或者多个列。
⽰例：建表，并给title，body字段加FULLTEXT索引
css3教程案例CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
导⼊数据
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
例1：
SELECT*FROM articles
WHERE MATCH (title,body)
无主之地3最新AGAINST ('database'IN NATURAL LANGUAGE MODE);
可以看到，语句查到了包含指定内容的⾏。实际上，返回的⾏是按与所查内容的相关度由⾼到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得，是⼀个⾮负浮点数。该值越⼤表明相应的⾏与所查的内容越相关，0值表明不相关。该值基于⾏中的单词数、⾏中不重复的单词数、⽂本集合中总单词数以及含特定单词的⾏数计算得出。
例2：
由上例可知MATCH (…) AGAINST (…)实际上会计算⼀个相关值，可通过下例来验证。
SELECT id, MATCH (title,body)
AGAINST ('Tutorial'IN NATURAL LANGUAGE MODE) AS score
FROM articles;
可以看到，所得结果的第⼆列即为改⾏与查内容的相关度。上例1中所得结果的顺序就是按此相关度排列的。
例3：
若想既看到查到的结果⼜需要了解具体的相关度，可⽤下述⽅法达成。
SELECT id, body, MATCH (title,body) AGAINST
('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE) AS score
FROM articles WHERE MATCH (title,body) AGAINST
('Security implications of running MySQL as root'
IN NATURAL LANGUAGE MODE);
可以看到，通过在查部分和条件部分分别使⽤相同的MATCH(…) AGAINST(…)可以同时获取两⽅⾯的内容（不会增加额外开销，优化器知道两个MATCH(…) AGAINST(..)是相同的，只会执⾏⼀次该语句）
注意事项
默认情况下全⽂搜索⼤⼩写不敏感，如上例1，查的内容为‘database’但含有‘DataBase’的⾏也会返回。可以通过为FULLTEXT索引列所使⽤的字符集指定⼀个特定的校对集来改变这种⾏为。
考虑下述两个SELECT语句：
1. SELECTCOUNT(*) FROM articles
mysql语句多表查询WHEREMATCH (title,body)
AGAINST('database' IN NATURAL LANGUAGE MODE);
2. SELECTCOUNT(IF(MATCH (title,body)
AGAINST('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count
FROMarticles;
这两条查询语句均可返回匹配的⾏数。但第⼀条语句可以利⽤基于WHERE从句的索引查，因此在匹配的⾏数较少时速度较第⼆句更快。第⼆句执⾏了全表扫描，因此在匹配的⾏数较多时较第⼀句更快。
html标签中MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(title,body)与FULLTEXT(title,body)。若要单独搜索某列，如body列，则需另外单独为该列建全⽂索引FULLTEXT(body)，然后⽤MATCH(body)搜索。
对于InnoDB表MATCH()中的列仅能来⾃于同⼀个表，因为索引不能快多张表（MyISAM表的的布尔搜索因为可以不使⽤索引所以可以跨多张表中的列，但速度很慢）。
全⽂搜索不仅可以搜索类似例1中‘database’这样的单个的单词，还可以搜索句⼦（这才是其被称为‘全⽂搜索‘的关键），如例3。全⽂搜索把任何数字、字母、下划线序列看作是单词，还可以包含“’”如aaa’bbb备解析为⼀个单词，但aaa’’bbb备解析为两个单词，FULLTEXT解析器⾃动移除⾸尾的“’”，
如’aaa’bbb’被解析为aaa’bbb。FULLTEXT解析器⽤“ ”（空格）、“,”（逗号）“.”（点号）作为默认的单词分隔符，因此对于不使⽤这些分隔符的语⾔如汉语来说FULLTEXT解析器不能正确的识别单词，对于这种情况需做额外处理。
全⽂搜索中⼀些单词会被忽略。⾸先是过短的单词，InnoDB全⽂搜索中默认为3个字符，MyISAM默认4个字符，可通过在创建FULLTEXT索引前改变配置参数来改变默认⾏为，对于InnoDB该参数为：innodb_ft_min_token_size，对于MyISAM为ft_min_word_len；另外stopword列表中的单词会被忽略。stopword 列表包含诸如“the”、“or”、“and”等常⽤单词，这些词通常被认为没有什么语义价值。MySQL由内建的停⽌字列表，但是可以所使⽤⾃定义的停⽌字列表来覆盖默认列表。对于InnoDB控制停⽌字的配置参数为innodb_ft_enable_stopword，innodb_ft_server_stopword_table， innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。
⽂本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的⾏中出现则该单词的权重越低，因为这表明其在⽂本集合中的语义价值较⼩。反之权重越⾼。例1中提到的相关度计算也与此值有关。
4.2布尔全⽂搜索
如果在AAGAINST()函数中指定了INBOOLEN MODE模式，则MySQL会执⾏布尔全⽂搜索。在该搜索模式下，待搜索单词前或后的⼀些特定字符会有特殊的含义。
例1：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL-YourSQL' IN BOOLEAN MODE);
该查询语句中“MySQL”前的“+”表明结果中必须包含“MySQL”⽽“YourSQL”前的“-”表明所得结果中不能含有“YourSQL”。
除了“+”和“-”外还有其他⼀些特定的字符。如空字符表明后跟的单词是可选的，但出现的话会增加该⾏的相关性；“@distance”⽤于指定两个或多个单词相互之间的距离（以单词度量）需在指定的范围内；“>”⽤于增加后跟单词对其所在⾏的相关性的贡献“<”⽤于降低该贡献；“（）”⽤于将单词分组为⼦表达式且可以
嵌套；“~”是后跟单词对其所在⾏的相关性的贡献值为负；“*”为普通的通配符，若为单词指定了通配符，那么即使该单词过短或者出现在了停⽌字列表中它也不会被移除；“””，括在双引号中的短语指明⾏必须在字⾯上包含指定的短语，全⽂搜索将短语分割为词后在FULLTEXT索引中搜索。⾮字字符⽆需完全匹配，如”test phrase”可以匹配含”test phrase”和”test phrase”的⾏，但匹配含”phrase test”的⾏。
android rom开发例2：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('MySQL YourSQL' IN BOOLEAN MODE);
到包含MySQL或者YourSQL的⾏
例3：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL+YourSQL' IN BOOLEAN MODE);
到包含同时MySQL和YourSQL的⾏
例4：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL YourSQL' IN BOOLEAN MODE);
到必须包含MySQl的⾏，YourSQL可有可⽆，但有YourSQL会增加相关性。
例5：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL ~YourSQL' INBOOLEAN MODE);
到包含必须包含MySQL的⾏，YourSQL可有可⽆，若出现了YourSQL则会降低其所在⾏的相关性。
例6：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+MySQL +(>Security <Optimizing)' IN BOOLEANMODE);
到必须同时包含MySQL以及Security或Optimizing的⾏Security会增加所在⾏的相关性，⽽Optimizing会降低所在⾏的相关性。
例7：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('da*' IN BOOLEAN MODE);
到包含da*的⾏。如包含DataBase、database等。
例8：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST('"MySQL,Tutorial"' IN BOOLEAN MODE);
unicode在线工具到包含“MySQL Tutorial”短语的⾏。
布尔全⽂搜索的⼀些特点
Ø MyISAM全⽂搜索会忽略⾄少在⼀半以上数据⾏中出现的单词（也即所谓的50%阈值），InnoDB⽆此限制。⽽在布尔全⽂搜索中MyISAM的50%阈值不⽣效。
Ø 停⽌字列表也适⽤于布尔全⽂搜索。
Ø 最⼩和最⼤词长全⽂搜索参数也适⽤于布尔全⽂搜索
Ø MyISAM中的布尔搜索在FULLTEXT索引不存在的时候仍可⼯作，但速度很慢。⽽InnoDB表的各类全⽂搜索必须有FULLTEXT索引，否则会出现不到与指定列相匹配的FULLTEXT索引的错误
Ø InnoDB中的全⽂搜索不⽀持在单⼀搜索单词前使⽤多个操作符如“++MySQL”。MyISAM中全⽂搜索可以处理这种情况，但是会忽略除了紧邻单词之外的其他操作符。
4.3查询扩展全⽂搜索
某些时候我们通过全⽂搜索来查包含某⽅⾯内容的⾏，⽐如我们搜索“database”，实际上我们期望返回结果不仅仅是仅包含“database”单词的⾏，⼀些包
含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的⾏也期望被返回。这个时候查询扩展全⽂搜索就能⼤显⾝⼿。
通过在AGAINST()函数中指定WITHQUERY EXPANSION 或者IN NATURAL MODE WITH QUERY EXPANSION可以开启查询扩展全⽂搜索模式。其⼯作原理是执⾏两次搜索，第⼀次⽤给定的短语搜索，第⼆次使⽤给定的短语结合第⼀次搜索返回结果中相关性⾮常⾼的⼀些⾏进⾏搜索。
例1：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
使⽤⾃然语⾔搜索返回了包含“database”的⾏。
例2：
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' WITH QUERY EXPANSION);
使⽤查询扩展全⽂搜索，不进返回了包含“database”的⾏，也返回了与例1中返回的⾏的内容相关的⾏。
注意事项
因为查询扩展会返回⼀些不相关的内容，因此会显著的引⼊噪声。索引仅当要查询的短语较短时才在考虑使⽤查询扩展全⽂搜索。
4.4全⽂搜索的停⽌字
上⽂已经简单介绍过了停⽌字列表，这⾥做详细介绍。停⽌字列表⽤MySQL Server所使⽤的字符集和校对集（分别由character_set_server和collation_server 两个参数控制）载⼊并执⾏搜索。若⽤于全⽂索引和搜索的停⽌字⽂件或者停⽌字表使⽤了与MySQL Server不同的字符集和校对集会则导致查停⽌字时错误的命中或未命中。
停⽌字查的⼤⼩写敏感性也依赖于MySQL Server所使⽤的校对集，例如校对集为latin1_swedish_ci则查是⼤⼩写不敏感的，若校对集为
latin1_geberal_cs或者latin1_bin则查是⼤⼩写敏感的。
InnoDB默认的停⽌字列表相对较短（因为技术上的或者⽂学等⽅⾯的⽂档常使⽤较短的词作为关键字或者有其他显著意义）。InnoDB默认的停⽌字列表存储在information_schema.innodb_ft_default_stopword表中。当然也可以通过⾃定义与innodb_ft_default_stopword表结构相同的表，填充期望的停⽌字，然后通过innodb_ft_server_stopword_table选项指定⾃定义的停⽌字表db_name/table_name，来改变默认的⾏为。另外还可以为innodb_ft_user_stopword_table选项指定含停⽌字的表，若同时指定了innodb_ft_default_stopword和innodb_ft_user_stopword_table则将使⽤后者指定的停⽌字表。上述操作改变所使⽤停⽌字表的操作需在创建全⽂索引前完成。且在指定所使⽤的停⽌字表时，表必须已经存在。
对于MyISAM可通过 ft_stopword_file选项指定所使⽤的停⽌字列表。MyISAM默认的停⽌字列表可在MySQL源码的 storage/myisam/ft_static.c⽂件中到。
4.5全⽂搜索的限制
Ø ⽬前只有InnoDB和MyISAM引擎⽀持全⽂搜索。其中InnodB表对FULLTEXT索引的⽀持从MySQL5.6.4开始。
Ø 分区表不⽀持全⽂搜索。
Ø 全⽂索引适⽤于多数多字节字符集。例外情况是：对于Unicode，utf8字符集可⽤但ucs2字符集不适⽤。尽管不能在ucs2列建⽴FULLTEXT索引，但可以在MyISAM表IN BOOLEAN MODE模式的搜索中搜索没有建⽴FULLTEXT索引的列。utf8的特性适⽤于utf8mb4，ucs2的特性适⽤于utf16、utf16e和utf32。
Ø 表意型语⾔如汉语、⽇语没有诸如空格之类的单词定界符。因此FULLTEXT解析器不能确定此类语⾔中词的起⽌。对于此种情况要特殊处理（⽐如将中⽂转换成⼀种单字节类似英⽂习惯的存储⽅式）。
Ø 允许在同⼀表中使⽤多种字符集，但FULLTEXT索引中的列必须使⽤同⼀字符集和校对集。
Ø MATCH()函数中的列必须与FULLTEXT索引中定义的列完全⼀致，除⾮是在MyISAM表中使⽤IN BOOLEAN MODE模式的全⽂搜索（可在没有建⽴索引的
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论