sql 两个表列包含_SQL之文质彬彬（三）

不管做什么，都要抓住“重点” ～

全文本搜索的最初一步，在于建立索引。

而索引又是什么呢？

单刀直入

大家还记得数年前（？）查询字典的时候吗？

比如我们想要查询“暗”这个字，就会很本能地从字典最前面开始找起——因为“暗”的拼音是“an”，而“a”按照英文字母排序时排在最前面。而若是我们翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明这本字典中没有这个字【这是什么盗版字典】。

同样，若是我们想要查询“总”字，我们便会开始从末尾开始翻起，因为“zong”的开头为“z”，处于字典最末。

换言之，字典的正文部分本身就是一个目录，我们无需再去查其他目录来检索我们需要的内容。

这种正文内容本身就是一种按照一定规则排列的目录便是 聚集索引。

而假如我们并不知道一个字的读音时，我们会去查找其偏旁部首来锁定这个字的页码后直接翻到某页来找到那个字。然而我们结合部首目录和检字表而查到的字的排序并不是真正的正文的排序方法。

我们可以通过这种方式来找到所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到所需要的页码。

这种目录纯粹是目录，正文纯粹是正文的排序方式便是 非聚集索引。

通过先在索引中进行筛选再根据细节进行检索的方法，在拥有庞大数据量的情况下可有效提高SQL运行的性能。

开启索引

就像前文中所说的，为了进行全文本搜索，我们必须为被搜索的列建立索引，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

而启用全文本搜索的方式非常简单，通过FULLTEXT（全文本）子句便可在create table中直接对列进行索引：

CREATE TABLE 表名(列1 INT, 列2 CHAR(100), 列3 TEXT, FULLTEXT(列3)

这里，我们便针对列3启用了全文本搜索。

当然，我们可以在创建表时指定FULLTEXT，也可在事后指定。

需要注意的是，更新索引要花时间，所以如果要导入数据到一个新表时，应该先导入所有数据，再修改表，定义FULLTEXT。这样有助于更快地导入数据，并且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间。

全文搜索

在索引之后，SELECT与Match（相配）和Against（相对）一起使用便可实际执行搜索。

其中Match( )指定被搜索的列，Against( )指定要使用的搜索表达式。

全文搜索举例

今天要建的表因为代码略长放在了文末，欢迎复制学习～

比如我们想找到有关于“兔子”（rabbit）的文本：

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit')

Match(note_text)指示MySQL针对指定的列“note_text”进行搜索，Against('rabbit')指定词“rabbit”作为搜索文本。由于有两行包含词rabbit，则这两个行被返回。

有两点需要留意一下：

• Match()中的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须次序正确地列出它们。
• 与REGEXP相同，若是想要区分文本的大小写，则需要使用BINARY方式。

这个例子我们其实也可以通过LIKE完成，甚至会更简单：

SELECT note_text AS 我是用LIKE完成的
FROM product notes
WHERE note_text LIKE '%rabbit%'

那两者有什么区别呢？

智能排序

虽然在上一个例子中两者返回的结果次序相同，但全文本搜索的一个重要部分就是对结果排序，返回以文本匹配的良好程度排序的数据——具有较高等级的行先返回，因为这些行很可能是我们真正想要的行。

排序举例

比如我们来看一看在刚才的那个例子中，SQL是怎样贴心地帮我们排序的：

SELECT note_text, 
MATCH(note_text) AGAINST('rabbit') AS 等级
FROM productnotes

等级是由MySQL根据行中词的一些特征所计算出来的：

• 数目；
• 唯一词的数目；
• 整个索引中词的总数；
• 包含该词的行的数目。

正如我们所见，不包含词rabbit的行等级为0，而包含词rabbit的两个行每行都有一个等级值。

全文本搜索会排除那些等级为0的行，并按照等级以降序排序。

如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值。

小结

今天我们介绍了索引的概念，并且初步介绍了如何开启全文搜索（FULLTEXT），如何运用MATCH和AGAINST来进行全文搜索。

全文搜索的范围并不仅于此，明天我们将介绍如何利用它来找到“近似”结果。

---

希望上文可以帮助大家初步了解全文搜索，如果还是有什么疑问或是建议的话，欢迎留言询问～

祝各位学习愉快！

#这里是画风逐渐飘逸【不】的正经（？）学习公众号：