mysql全文检索match() against

mysql 本身提供了一种叫做全文搜索的技术

CREATE TABLE articles (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   title VARCHAR(200),
   body TEXT,FULLTEXT (title,body) //注意这里
);

创建表的 mysql 语句，其中最后一句 FULLTEXT (title,body)就是为 title 和 body 创建一个全文搜索，也就是以后方便搜索标题和标题正文的内容

查看表的抓紧索引 show indexs from 表名

show index from articles;

接下来为表添加数据

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. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');

之后用全文搜索来进行测试，模板如下 SELECT 表字段 FROM 表名 WHERE MATCH (全文搜索表字段) AGAINST ('搜索字符串')

SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('database') ; match表示要找的列，而against表示要搜索的条件

SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('+MySQL -YourSQL'IN BOOLEAN MODE); 搜索title和body中包含MySQL，但是不能有YourSQL的结果。

上面的方式是全文搜索和表同时建立，也可以在建立表之后在添加全文搜索alter table articles add fulltext index(title,body);

如果想更深层次了解可以访问http://blog.csdn.net/bravekingzhang/article/details/6727541

秒客网

mysql全文检索match() against

相关文章