全文索引在 MySQL 中是一个FULLTEXT类型索引。FULLTEXT索引用于MyISAM表,可以在CREATE TABLE时或之后使用ALTER TABLE或CREATE INDEX在CHAR、VARCHAR或TEXT列上创建。对于大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再使用ALTER TABLE(或CREATE INDEX) 创建索引,这将是非常快的。将数据装载到一个已经有FULLTEXT索引的表中,将是非常慢的。
全文搜索通过MATCH()函数完成。
mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> ); Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles VALUES -> (NULL,''MySQL Tutorial'', ''DBMS stands for DataBase ...''), -> (NULL,''How To Use MySQL Efficiently'', ''After you went through a ...''), -> (NULL,''Optimising MySQL'',''In this tutorial we will show ...''), -> (NULL,''1001 MySQL Tricks'',''1. Never run mysqld as root. 2. ...''), -> (NULL,''MySQL vs. YourSQL'', ''In the following database comparison ...''), -> (NULL,''MySQL Security'', ''When configured properly, MySQL ...''); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST (''database''); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ |& |