MySQL 中 SQL 语句的匹配策略-优快云博客

MySQL 中 SQL 语句的匹配策略：

1. 一般的匹配：

a. 标准SQL模式匹配

LIKE和NOT LIKE，以及'_' 和'%'

b. 正则表达式匹配

REGEXP和NOT REGEXP，以及很多功能强大地构造正则表达式

详情参考：

http://blog.youkuaiyun.com/omohe/archive/2007/09/28/1805631.aspx

2. 全文检索：

a. 一般全文检索

SELECT * FROM name2gid_9606 WHERE MATCH(name) AGAINST ("gname");

优点：能够对各个单词进行索引；结果有相关值并可对结果进行排序；

缺点: 存在50%问题 (即如果一个词出现过多将被当作停滞词忽略掉)，而且单词最小长度为4个字符 (即长度小于4个字符的单词不被建立索引)；

b. 布尔全文检索

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN

BOOLEAN MODE);

很优秀，有一些操作符，但要求关键词必须大于4个。

(详细使用可参考MySQL Manual)

参考总结的MySQL中的全文检索

------------------------------

----------------------------------------------------------------------------

MySQL的全文搜索功能

参看：http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-search

FullText Search

mysql> CREATE TABLE articles (

-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

-> title VARCHAR(200),

-> body TEXT,

-> FULLTEXT (title,body)

-> );

Query OK, 0 rows affected (0.00 sec)

mysql> 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 ...');

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM articles

-> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

| id | title | body|

+----+-------------------+------------------------------------------+

| 5 | MySQL vs. YourSQL | In the following database comparison ... |

| 1 | MySQL Tutorial | DBMS stands for DataBase ... |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

Match函数会对against 中的参数到Match参数的列的资料库中做自然语言搜索， Match函数会返回一个相关值即搜索字符串和要Match的列中的文字的一个相似性度量

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial') AS score

-> FROM articles ORDER BY MATCH (title,body) AGAINST ('Tutorial')

Match函数的搜索策略以及返回结果的相关性计算：

a. MySQL FullText将任何单字字符原形（字母、数字和下划线）的序列视为一个单词。因此单引号  ' 连接的词都被视为一个单词。FullText 分析程序寻找分隔符（空格，逗号以及句号等）确定单词的起始位置和结束位置。因此，在中文里由于没有分割，因此使用 FullText 进行全文检索前需要对文本进行预处理，如添加诸如" 之类的分隔符。

一些单词在全文搜索中会被忽略：

过于短的词，全文搜索所能找到的词的默认最小长度为 4 个字符；

停滞词

( 可以通过微调MySQL 全文搜索来调节默认的最小单词长度和停滞词)

另外，搜索策略会对搜索的关键词的重要性进行衡量，如果这个单词出现在许多文件中，则具有较低的重要性： (  通常如果单词出现在至少全文的50% 的行中，则将被视作停滞词)

这样的话有时候使用全文搜索会很奇怪，即

例如 , 虽然单词 "MySQL " 出现在文章表中的每一行，但对这个词的搜索可能得不到任何结果：

mysql> SELECT * FROM articles

-> WHERE MATCH (title,body) AGAINST ('MySQL');

找不到搜索的词(0.00 秒)

这个搜索的结果为空，原因是单词 "MySQL" 出现在至少全文的50%的行中。因此, 它被列入停止字。

Boolean全文搜索

需要绕过该50% 限制的用户可使用布尔搜索代码<布尔全文搜索 >

1）语法：

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

2）布尔搜索的一般特点：

# 它们不使用 50% 域值。( 不会出现一般全文检索的那样子，如果出现次数过多反而找不到的情况)

# 它们不会按照相关性渐弱的顺序将行进行分类。

# 即使没有FULLTEXT，它们仍然可以工作，尽管这种方式的搜索执行的速度非常之慢。

# 最小单词长度全文参数和最大单词长度全文参数均适用。(依然有最小单词长度限制，默认为4 个字符)

# 停止字适用。

3）布尔搜索的强大之处在于其可定制性，主要定制AGAINST中的搜索关键词模式：

+必须包含,-不能出现,无操作符可有可无,> ,<,()组合表达式,~,*通配符 ,"构造词组(注意非单词字符不算)

4）

一些实例：

包含至少两个单词中的一个的行，'apple banana';

两个单词都包含的行,'+apple +juice';

包含单词  "apple"的行，若这些行也包含单词"macintosh "，则列为更高等级，'+apple macintosh';

包含"apple"，不包含"macintosh" 的行，'+apple -macintosh';

'+apple +(>turnover <strudel)'，包含apple和 tunover或者包含apple和strudel的行，而且包含 apple turnover的行比包含apple strudel的行排列等级更高；

'apple*',匹配apple,apples等通配符；

'"some words"'寻找包含短语"some words"的行 (会找到"some words of wisdom"的行，而不会找到"some noise words" 的行)

5）我的这里可能使用

第一个,以及通配符的情况较多等。

全文搜索带查询扩展：

带有查询扩展功能的全文检索，WITH QUERY EXPANSION；

注意优点和缺点；

注意：盲查询扩展功能很容易返回非相关文件而增加无用信息，因此只有在查询一个长度很短的短语时才有必要使用这项功能。

全文停滞词

全文限定条件

微调MySQL全文搜索(MySQL是 open source的，可以修改源代码来订制和安装MySQL)

1)调节全文搜索参数的时候需要慎重，因为大多数情况只能降低其性能，除非非常了解自己在做什么。

2)另外，一些变量的改变需要重建表中的FULLTEXT索引；

可以修改编入索引单词的最小长度和最大长度；

等等。。。