mysql fulltext

只有MyISAM引擎支持 

create table articles(

 id int unsigned auto_increment not null primary key,
 title varchar(200),
 body text,
 fulltext(title,body)
 )ENGINE=MyISAM default charset=utf8;


 mysql>  create table articles(
    ->  id int unsigned auto_increment not null primary key,
    ->  title varchar(200),
    ->  body text,
    ->  fulltext(title,body)
    ->  )ENGINE=MyISAM default charset=utf8;
Query OK, 0 rows affected (0.07 sec)


mysql> desc articles;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title | varchar(200)     | YES  | MUL | NULL    |                |
| body  | text             | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.10 sec)


mysql> select title,body, match(title,body) against('mysql') as score  from arti
cles ;
+------------------+--------------+--------------------+
| title            | body         | score              |
+------------------+--------------+--------------------+
| php              | lamp php     |                  0 |
| mysql guide      | book         | 0.6700310707092285 |
| optimizing mysql | in this page | 0.6700310707092285 |
| php security     | function     |                  0 |
| c++              | oop          |                  0 |
| java             | java web     |                  0 |
+------------------+--------------+--------------------+


mysql> select title,body, match(title,body) against('mysql') as score  from arti
cles where match(title,body) against('mysql');
+------------------+--------------+--------------------+
| title            | body         | score              |
+------------------+--------------+--------------------+
| mysql guide      | book         | 0.6700310707092285 |
| optimizing mysql | in this page | 0.6700310707092285 |
+------------------+--------------+--------------------+
2 rows in set (0.00 sec)


mysql> insert into articles(title,body)values('this is mysql mysql','mysql');
Query OK, 1 row affected (0.00 sec)


mysql> select title,body, match(title,body) against('mysql') as score  from arti
cles ;
+---------------------+--------------+---------------------+
| title               | body         | score               |
+---------------------+--------------+---------------------+
| php                 | lamp php     |                   0 |
| mysql guide         | book         |  0.2780880331993103 |
| optimizing mysql    | in this page |  0.2780880331993103 |
| php security        | function     |                   0 |
| c++                 | oop          |                   0 |
| java                | java web     |                   0 |
| this is mysql mysql | mysql        | 0.28441134095191956 |
+---------------------+--------------+---------------------+
7 rows in set (0.00 sec)


mysql> select title,body, match(title,body) against('mysql') as score  from arti
cles where match(title,body) against('mysql') ;
+---------------------+--------------+---------------------+
| title               | body         | score               |
+---------------------+--------------+---------------------+
| this is mysql mysql | mysql        | 0.28441134095191956 |
| mysql guide         | book         |  0.2780880331993103 |
| optimizing mysql    | in this page |  0.2780880331993103 |
+---------------------+--------------+---------------------+
有此可见 match 返回的是个相关度的值，按高到低排序，为0不相关，不显示