在mysql中使用全文索引

在使用like进行模糊查询,当数据量大到一定程序的时候,我们会发现查询的效率是相当低下的。下面就此介绍一下mysql提供全文索引和搜索的功能。

正文：

下面是从参考资料里头摘录下来的内容，具体使用见参考资料，那里更详细哦

1) 很多互联网应用程序都提供了全文搜索功能，用户可以使用一个词或者词语片断作为查询项目来定位匹配的记录。在后台，这些程序使用在一个SELECT查询中 的LIKE语句来执行这种查询，尽管这种方法可行，但对于全文查找而言，这是一种效率极端低下的方法，尤其在处理大量数据的时候。
　　MySQL 针对这一问题提供了一种基于内建的全文查找方式的解决方案。在此，开发者只需要简单地标记出需要全文查找的字段，然后使用特殊的MySQL方法在那些字段 运行搜索，这不仅仅提高了性能和效率（因为MySQL对这些字段做了索引来优化搜索），而且实现了更高质量的搜索，因为MySQL使用自然语言来智能地对 结果评级，以去掉不相关的项目。

2)到 3.23.23 时，MySQL 开始支持全文索引和搜索。全文索引在 MySQL 中是一个 FULLTEXT 类型索引。FULLTEXT 索引用于 MyISAM 表，可以在 CREATE TABLE 时或之后使用 ALTER TABLE 或 CREATE INDEX 在 CHAR、VARCHAR 或 TEXT 列上创建。对于大的数据库，将数据装载到一个没有 FULLTEXT 索引的表中，然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引，这将是非常快的。将数据装载到一个已经有 FULLTEXT 索引的表中，将是非常慢的。

全文搜索通过 MATCH() 函数完成。 

下面大概介绍一下创建全文索引的过程，主要有两种办法：
第一种是创建表的时候就创建全文索引，第二种类是在创建表以后再增加全文索引，通过上面的引文我们知道后者比前者有些好处

1、先创建表，然后通过"AlTER TABLE"增加全文索引

mysql> create database ftt;   --创建数据库
Query OK, 1 row affected (0.03 sec)
mysql> use ftt;           --使用数据库
Database changed
mysql> create table reviews     --创建表
  -> (
  -> id int(5) primary key not null auto_increment,
  -> data text
  -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT ignore INTO `reviews` (`id`, `data`) VALUES(1, 'Gingerboy has a new singl
e out called Throwing Rocks. It\'sgreat!'); --下面三句都是插入数据
Query OK, 1 row affected (0.06 sec)

mysql> INSERT ignore INTO `reviews` (`id`, `data`) VALUES (2, 'Hello all, I really like
the new Madonna single. One of the hottest tracks currently playing...I\'ve bee
n listening to it all day');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT ignore INTO `reviews` (`id`, `data`)VALUES (3, 'Have you heard the new ba
nd Hotter Than Hell?They have five members and they burn their instruments when
they play in concerts. These guys totally rock! Like, awesome, dude!');
Query OK, 1 row affected (0.00 sec)

mysql> alter table reviews add fulltext index (data);   --通过alter增加全文索引
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select id from reviews where match (data) against ('Madonna'); --通过match和against实现全文检索
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

mysql> select id from reviews where match (data) against ('+Madonna+instruments'
); --搜索出包含Madonna或者instruments的所有记录的id号
+----+
| id |
+----+
| 2 |
| 3 |
+----+
2 rows in set (0.00 sec)

mysql> select id from reviews where match (data) against ('Mado*' in boolean mod
e); --检索出包含Mado的所有记录的id
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

补充
  从上面的结果不难看出，mysql的全文索引和检索支持多关键字，从下面的参考资料，你还可以发现更多实现，看看去哦

2、在创建表的时候就创建全文索引

由于其他的内容和上面差不多，下面只给出创建时的语法


Code:

[Ctrl+A Select All]



参考资料:

1)如何在MySQL中获得更好的全文搜索结果 http://dev.21tx.com/2006/04/05/10735.html
[主要介绍了全文检索和搜索是怎么用的]

2)mysql全文检索http://www.jolr.com/forum/?display=topic&id=4856&s=371cda7ed1694f669f75610dde769c82
[主要介绍了全文检索的一些语法知识等]
另外，如果想要看最原始的资料,请上mysql官方网站,http://www.mysql.com

大家注意拉,mysql5以后才支持视图和存储过程
要是想要使用这些东西,可以到www.mysql.cn或者www.phpchina.cn下载mysql5.0.18哦

1)下面是mysql下存储过程的使用
http://www.mimi163.net/cs/wenzhang/2006/3/16/996.htm
有时间写篇关于mysql和其他数据库存储过程使用的比较,呵呵
感觉这个东西还是很有用的拉
2)另外,这里有一篇介绍php怎么调用mysql的存储过程的
也是相当的不错,呵呵
http://www.ithack.net/Articles/php/2005052595601.html

不过上面的东西，我还没有测试过,正在使用中...

后面用了一下感觉不错

对拉，注意这个东西的意思哦
delimiter // －－－ 这是定义结束符为“//”

解决了mysql5中直接创建全文索引时的一个问题

问题为:
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

解决:
http://www.dmresearch.net/shijun/?p=108

好东西,先放这里

《PHP 字符串操作入门教程 》
http://study.zhupao.com/infoview/Article_16867.html

我自己试了后精简说一下:

问题:
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
解决办法:
如果你以前使用:
create table test
(
id int not null primary key,
name text,
fulltext(name)
);
出错
那么现在使用
create table test
(
id int not null primary key,
name text,
fulltext(name)
)type=myisam;
就可以解决
或者把type用engine代替

补充:
在我使用的时候
mysql4.0竟然没有这个问题，但是使用mysql5.0的时候到出现,不过用上面办法就解决拉

4. 可能遇到的困扰

   到目前为止都很顺利，但是如果检索SQL改为下面会怎样呢？

SELECT * FROM articles
    WHERE MATCH (title,body) AGAINST ('well');

   结果让人大跌眼镜，开始我也困惑了许久，后来去网上查了下才知道原来是这么回事：

mysql指定了最小字符长度，默认是4，必须要匹配大于4的才会有返回结果，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度，也可以在mysql配置文件my.ini 更改最小字符长度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完后重启mysql即可。

   所以上面不能返回结果。但是我用上面的方法改配置文件并重启MySQL服务器后，再用show命令查看，并没有改变。

   另外，MySQL还会计算一个词的权值，以决定是否出现在结果集中，具体如下：

mysql在集和查询中的对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重)，因为在这个特定的集中，它有较低的语义值。否则，如果词是较少的，它将得到一个较高的权重，mysql默认的阀值是50%，上面‘you’在每个文档都出现，因此是100%，只有低于50%的才会出现在结果集中。

   但是如果不考虑权重，那么该怎么办呢？MySQL提供了布尔全文检索（BOOLEAN FULLTEXT SEARCH）

   假设well在所有记录中都出现，并且ft_min_word_len已经改为2，那么下面的SQL检索语句得到的结果集将包含所有记录：

 

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('well' IN BOOLEAN MODE );

 

 

5. 布尔全文检索语法

   上面通过IN BOOLEAN MODE指定全文检索模式为布尔全文检索。MySQL还提供了一些类似我们平时使用搜索引擎时用到的的语法：逻辑与、逻辑或、逻辑非等。具体通过几个SQL语句例子来说明

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

   + 表示AND，即必须包含。- 表示NOT，即不包含。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('apple banana' IN BOOLEAN MODE);

   apple和banana之间是空格，空格表示OR，即至少包含apple、banana中的一个。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple banana' IN BOOLEAN MODE);

   必须包含apple，但是如果同时也包含banana则会获得更高的权重。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple ~banana' IN BOOLEAN MODE);

   ~ 是我们熟悉的异或运算符。返回的记录必须包含apple，但是如果同时也包含banana会降低权重。但是它没有 +apple -banana 严格，因为后者如果包含banana压根就不返回。

SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);

   返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。

 

6. MySQL不支持中文的全文检索

   默认MySQL不支持中文全文检索，怎么办？大致方法有下面几个：

   A. 扩展MySQL，添加中文全文检索支持，难度较大

   B. 为中文内容表提供一个对应的英文索引表（即将FULLTEXT索引列按照一定的规则转化成英文索引表中的每一条记录，比如全部进行base64编码，内容表和英文索引表的id相同），检索时先将检索词也用相同规则转换成英文，然后再使用。如果还要支持按拼音全文检索，那么还需要在索引表中增加对应的拼音内容（就需要中文转拼音算法了）。当然如果还需要支持中英文交互搜索，比如搜索William时也需要返回威廉，反之亦然，那么还需要将威廉对应的英文翻译也存到索引表中去。

   参考网上的链接，具体做法包括先对中文内容进行分词，然后中文转换为四位区位码存到索引表中。检索时，包含中文的检索词也要先分词，再转换为四位区位码，然后在索引表中进行全文检索。

 

7. 核对条目

   A. 只有存储引擎类型为MyISAM类型的表，并且MySQL版本为4.X或者以上才能使用MySQL内置的全文检索支持

   B. MySQL全文检索默认不支持中文，且对英文检索时忽略大小写

   C. MySQL全文检索时，默认检索长度为4，即关键词的长度必须大于5才能被捕获

   D. MySQL全文检索时，所有FULLTEXT索引列必须使用相同的字符集

   E. MySQL全文检索返回结果集时还会考虑权重

   F. MySQL全文检索还支持灵活的布尔全文检索模式

   G. 更多内容参考MySQL5官方手册

 

参考链接：

   http://viralpatel.net/blogs/2009/04/full-text-search-using-mysql-full-text-search-capabilities.html

   http://hi.baidu.com/gogogo/blog/item/28b16c81b3bc87d6bc3e1eb7.html

   http://dev.mysql.com/doc/refman/5.1/zh/functions.html#fulltext-query-expansion

   http://www.chinahtml.com/0702/mysql-117187149111362.html

   http://www.phpx.com/happy/viewthread.php?tid=124691