mysql中文全文搜尋

[mysqld]
ngram_token_size=2

mysql> USE test;

mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) WITH PARSER ngram
) ENGINE=InnoDB CHARACTER SET utf8mb4;

mysql> SET NAMES utf8mb4;

INSERT INTO articles (title,body) VALUES
('資料庫管理','在本教程中我將向你展示如何管理資料庫'),
('資料庫應用開發','學習開發資料庫應用程式');

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB CHARACTER SET utf8;

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

# Or:

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

SELECT * FROM articles WHERE MATCH(title,body) AGAINST('資料庫教程');

mysql> SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title| body|
+----+-------------------+------------------------------------------+
|1 | MySQL Tutorial| DBMS stands for DataBase ...|
|5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title| body|
+----+-----------------------+-------------------------------------+
|1 | MySQL Tutorial| DBMS stands for DataBase ...|
|2 | How To Use MySQL Well | After you went through a ...|
|3 | Optimizing MySQL| In this tutorial we will show ...|
|4 | 1001 MySQL Tricks| 1. Never run mysqld as root. 2. ... |
|6 | MySQL Security| When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+