教你怎样在MySQL中提高全文搜索效率

mysql> CREATE TABLE reviews (id INT(5) 
PRIMARY KEY NOT NULL AUTO_INCREMENT, data TEXT);

mysql> INSERT INTO `reviews` (`id`, `data`) VALUES

(1, 'Gingerboy has a new single out called Throwing Rocks. It's great!');

mysql> INSERT INTO `reviews` (`id`, `data`) VALUES 

(2, 'Hello all, I really like the new Madonna single. 

One of the hottest tracks currently 

playing...I've been listening to it all day');

mysql> INSERT INTO `reviews` (`id`, `data`)

VALUES (3, 'Have you heard the new band Hotter Than Hell?

They have five members and they 

burn their instruments when they play in concerts. 

These guys totally rock! Like, awesome, dude!');

mysql> SELECT * FROM reviews;

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

 id  data                                       

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

  1  Gingerboy has a new single out called ...  

  2  Hello all, I really like the new Madon ... 

  3  Have you heard the new band Hotter Than... 

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

3 rows in set (0.00 sec)

mysql> ALTER TABLE reviews ADD FULLTEXT INDEX (data);

Query OK, 3 rows affected (0.21 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM reviews;

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

 Table    Column_name    Packed  Null  Index_type  Comment 

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

 reviews   id            NULL          BTREE               

 reviews   data          NULL    YES   FULLTEXT            

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

2 rows in set (0.01 sec)

mysql> SELECT id FROM reviews WHERE MATCH (data) AGAINST ('single');+----+

 id 

+----+

  1 

  2 

+----+

2 rows in set (0.00 sec)

mysql> SELECT id, MATCH (data) AGAINST ('rock') FROM reviews;

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

 id  MATCH (data) AGAINST ('rock') 

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

  1                              0 

  2                              0 

  3                1.3862514533815 

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

3 rows in set (0.00 sec)

mysql> SELECT id FROM reviews WHERE MATCH (data) 
AGAINST ('+single -madonna' IN BOOLEAN MODE);

+----+

 id 

+----+

  1 

+----+

1 row in set (0.00 sec)

mysql> SELECT id FROM reviews WHERE MATCH (data) 
AGAINST ('hot*' IN BOOLEAN MODE);+----+

 id 

+----+

  3 

  2 

+----+

2 rows in set (0.00 sec)

mysql> SELECT id FROM reviews WHERE MATCH (data) 
AGAINST ('hell rocks' IN BOOLEAN MODE);

+----+

 id 

+----+

  1 

  3 

+----+

2 rows in set (0.00 sec)