MySQL数据库之InnoDB引擎之全文索引（FULLTEXT）之自然语言全文搜索样例

自然语言全文搜索（IN NATURAL LANGUAGE MODE）

自然语言全文搜索是MySQL全文搜索的默认搜索方式，实现从一个文本集合中搜索给定的字符串。
这里，文本集合指的是指由FULLTEXT索引的一个或者多个列。

建表

MySQL要求：5.6.24版本+

CREATE TABLE articles4 (
     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
     title VARCHAR(200),
     body TEXT,
     FULLTEXT (title,body)
) ENGINE=InnoDB;

插入数据

INSERT INTO articles4 (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 articles4 WHERE MATCH (title,body)  AGAINST ('database' IN NATURAL LANGUAGE MODE);

SELECT id, MATCH (title,body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
FROM articles4;

SELECT id, body, MATCH (title,body) AGAINST
   ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE) AS score
   FROM articles4 WHERE MATCH (title,body) AGAINST
   ('Security implications of running MySQL as root' IN NATURAL LANGUAGE MODE);