下文转自某些手册,你可以用这个语句来创建FULLTEXT类型的索引:
CREATE FULLTEXT INDEX INDX_TEXT ON ARTICLE (TITLE,NOTE);
到 3.23.23 时,MySQL 开始支持全文索引和搜索。全文索引在 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 |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
函数 MATCH()
对照一个文本集(包含