MySQL全文本搜索引擎与触发器

来源：互联网发布：我的世界无限耐久js 编辑：程序博客网时间：2024/04/30 10:40

前言： MySQL 支技多种索引Index：主键，唯一索引（一个表可以有多个唯一索引，但必须只能有一个主键），普通索引（当大量的搜索是关于一个表的一列或几列时），全文本索引（以下重点阐述），聚族索引（一个表只能有一个，它们的顺序就是数据的物理顺序，不宜常更新！）。

一、mysql最常使用的搜索引擎为MyISAM（全文本搜索引擎）和InnoDB（可靠的事务处理引擎） ,前者支持全文本搜索且查询速度更快一些，后者不支持全文本搜索引擎。
二、全文本搜索有比LIKE匹配和正则表达式匹配更强的功能，在对表进行全文本搜索时，MySQL不需要分表查看每个行，不需要分别分析和处理每个词。 MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以有效的决定哪些词匹配（哪些行包含他们），哪些词不匹配，他们的匹配频率，等等。
三、为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。在索引之后，SELECT可与Match（）和Against（）一起使用以实际执行搜索。

例如：select id,web_name,match(col1,col2) Against('XXXX') from table A; 比较的是col1与col2的相似度，处于[0,1]之间，这里有一些是需要注意的：

1. ‘XXXX’必须要>=4个字符。

2. 'the', 'some' 这些是整用词，因为常用，所以不能用于'XXXX'当中。

    3. 如果有XX"XX，会被认为是两个字符串。
四、一般在创建表时启用全文本搜索。CREATE TABLE语句接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。
CREATE TABLE productnotes
(
    note_id INT NOT NULL AUTO_INCREAMENT,
    prod_id CHAR(10) NOT NULL,
    note_data DATETIME NOT NULL,
    note_text TEXT NULL,
    PRIARY KEY(note_id),
    FULLTEXT(note_text)
)ENGINE=MyISAM
这里只指定了一个列note_text进行索引，如果需要也可以指定多个列。在定义之后，MySQL自动维护该索引。增加、删除和更新行之后，索引会随之自动更新。可以在创建表时使用FULLTEXT，也可以稍后使用。注意：不要在导入数据时使用FULLTEXT。

在使用索引之前，必须要建立索引，且索引的类型要是vchar,char,string类型，如果不是MyISAM类型的索引，可以通过以下方法进行转换：

Alter table table_name Engine=MyISAM;
五、在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match（）制定被搜索的列，Against（）指定要使用的搜索表达式：
select note_text from productnotes where match(note_text) against('hello');
注意：1。传递给Match()的值必须与FULLTEXT（）定义的相同。如果指定多个列，则必须列出它们且次序正确。2. 除非使用BINARY方式，否则全文本搜索不区分大小写。
试一下这个语句：select note_text,MATCH(note_text) Against as rank from productnotes
六、使用查询扩展，能找出可能相关的结果，即使他们并不精确包含所要查找的词。
select note_text from productnotes where match(note_text) against('hello' with QUERY EXPANSION) .
可以看出，只有第一行包含词anvils，因此等级最高。第二行与词anvils无关，但因为它包含第一行中的两个词（customer和recommend），所以也被检索出来，第三行业包含相同的词，但他们，他们文本中的位置更靠后且分开的更远，所以排在第三。
七、MySQL同时还支持布尔方式（boolean mode）。
可以支持以下细节：要匹配的词；要排斥的词；排列提示；表达式分组；其他。
布尔文本搜索在没有建立索引的情况下也能使用，但速度会变得非常缓慢。
全文本布尔搜索操作符

布尔操作符说明

+ 包含，词必须存在

- 排除，词不必出现

> 包含，而且增加等级值

< 包含，且减小等级值

() 把词组成子表达式

~ 取消一个词的排序值

* 词尾的通配符

"" 定义一个短语

举例：

SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('+rabbit +bait' IN BOOLEAN MODE)

SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('rabbit bait' IN BOOLEAN MODE)

SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('"rabbit bait"' IN BOOLEAN MODE)

SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('>rabbit <carrot"' IN BOOLEAN MODE)

SELECT note_text
FROM `productnotes`
WHERE MATCH(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

八、使用说明

■在索引全文本数据时，短词被忽略且从索引中排除。短词定义为三个或三个以下字符的词（数目可以更改）

■MySQL带有一个内建的stopword列表，这些词在索引中是被忽略的，如果需要，可以覆盖这个列表。

■许多词出现的频率很高，搜索他们没用处。MySQL规定如果一个词出现50%以上的行中，则作为一个stopword忽略。这个规则不适用于全文本布尔搜索。

■如果表中行数少于三行，全文本搜索不返回结果。（因为每个词或者不出现，或者至少出现在50%的行中）

■忽略词中的单引号。例如，don't索引为dont。

■不具有词分隔符（如汉语和日语）的语言不能恰当地返回全文搜索结果。

■仅在MyISAM数据库引擎中支持全文本搜索。

触发器：

分为事前触发器与事后触发器。

■建立触发器：

create trigger all_reassign before/after insert or update on table_name

for each row

AS BEGIN

IF(new.job='教授') and new.sal<4000 THEN

new.sal=5000;

END　IF；

　END；

　■删除触发器：

　　　Drop trigger all_reassign on table_name;

在一个表上可以建立多个触发器，它们的顺序按照before 与after进行，如果有多个同类触发器，则按照顺序进行触发。