MySQL学习笔记（9）

mysql全文检索
＝＝＝＝＝＝＝＝＝＝＝＝＝
模糊查询（相似度）
－－－－
如何存储
如何快速检索问题
－－－－－－－－
全文索引在mysql中是一个fulltext类型索引
fulltext类型索引可以在create table 时或之后使用alter table 或
create index在char,varchar或text列上创建
对于大database,将database装载到一个没有fulltext类型索引
的表中然后再使用alter table 或create index创建索引，这将是非常快的，将数据装载到一个已经有fulltext索引的表中，将是非常慢的
＝＝
msyql>create table articles(
id int unsigned auto_increment not null primary key,
title varchar(200),
body text,
fulltext (title,body)
);
全文索引是通过match()函数来实现的
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....'),
('mysqlvs yoursql','in the following database comprasion...'),
('mysql security','when configured properly,mysql ..');
----
select * from articles
where match(title,body) against('database');
+----+-----------------+-----------------------------------------+
| id | title           | body                                    |
+----+-----------------+-----------------------------------------+
|  5 | mysqlvs yoursql | in the following database comprasion... |
|  1 | mysql tutorial  | dbms stands for database...             |
+----+-----------------+-----------------------------------------+
2 rows in set (0.07 sec)
========
against()是忽略字母大小写的
会给一个度，相似度，默认返回相关性由高到底排序上面（5，1）
=======
select id,match(title,body) against ('tutorial') from articles;
看相关性，相似度
+----+----------------------------------------+
| id | match(title,body) against ('tutorial') |
+----+----------------------------------------+
|  1 |                       0.65545833110809 |
|  2 |                                      0 |
|  3 |                       0.66266459226608 |
|  4 |                                      0 |
|  5 |                                      0 |
|  6 |                                      0 |
+----+----------------------------------------+
6 rows in set (0.05 sec)
==========
select * from articles where match(title,body)
against('+mysql -yoursql' in boolean mode);
包含mysql,但不包含yoursql
============
against匹配串:
apple banana
找至少包含上面词中的一个记录行
+apple +juice
二个词均在被包含
+apple macintosh
包含"apple",但是如果同时包含"macintosh"它的排列将更高一些
+apple -macintosh
包含"apple",但不包含"macintosh"
"some words"
可以包含"some words of wisdom",但不是"some noise words"