尽量不要使用 Like
由于 MySQL LIKE '%text%' 是无法使用索引的，Like 能用上索引的唯一可能
LIKE 'text%'
所以实际使用中 LIKE 毫无实用价值。。。
mysql 5.7.6 之后支持了中日韩文的全文检索
查看了本机的 mysql 版本
mysqld --version
mysqld Ver 5.7.18-0ubuntu0.16.10.1 for Linux on x86_64 ((Ubuntu))
A full-text index in MySQL is an index of type FULLTEXT.
- 实际上，就是增加了一个新的索引类型 FULLTEXT
- FULLTEXT 索引只能用于 InnoDB 及 MyISAM 表，可以应用于 CHAR，VARCHAR，TEXT 字段
- 提供了内置的支持 CJK （中文，日文，韩文）的 ngram 分词器
- 对于大量数据，先插数据，后建 FULLTEXT 索引更快，相对于先建 FULLTEXT 索引，后插数据而言
测试
ALTER TABLE `test`.`test`
ADD FULLTEXT INDEX `index3` (`msg` ASC) WITH PARSER ngram;
Error Parsing DDL for
test.testThere was an error while parsing the DDL retrieved from the server.
原来是 MySQL Workbench 6.3.6 的 bug，即不支持 WITH PARSER 的语法。
参考 https://bugs.mysql.com/bug.php?id=80760
Fixed as of the upcoming MySQL Workbench 8.0.11 release。
（之后我用 MySQL Workbench 8.0 客户端试了一下确实没问题了）
对于老版本的 MySQL Workbench 客户端，是没指望了，用 mysql 终端命令行即可。
SELECT msg FROM test;
+-----------------------------------+
| msg |
+-----------------------------------+
| 中华人民共和国万岁 |
| 加盟test hello 测试一下 |
| 在创建索引时，可以 |
| 员工不可申请加盟商 hello |
+-----------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT msg FROM test where match(msg) against('加' IN BOOLEAN MODE);
Empty set (0.00 sec)
中文单个字符及英文单个字母，默认是不需要进行检索的。会返回空。
mysql> SELECT msg FROM test where match(msg) against('加盟' IN BOOLEAN MODE);
+-----------------------------------+
| msg |
+-----------------------------------+
| 员工不可申请加盟商 hello |
| 加盟test hello 测试一下 |
+-----------------------------------+
2 rows in set (0.00 sec)
mysql> SELECT msg FROM test where match(msg) against('h' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)
mysql> SELECT msg FROM test where match(msg) against('ll' IN NATURAL LANGUAGE MODE);
+-----------------------------------+
| msg |
+-----------------------------------+
| 员工不可申请加盟商 hello |
| 加盟test hello 测试一下 |
+-----------------------------------+
2 rows in set (0.00 sec)
为何单个字符无法被检索到
Fulltext index, and therefore search is based on words, not patterns. There is a minimum length parameter that applies to words, any word below that length is not indexed, therefore is not considered as part of the weighting of the results. A single character word (6) is probably below the limit, therefore is discarded.
默认模式
不指定默认的情况下，默认为 IN NATURAL LANGUAGE MODE
是否支持 LONGTEXT
为博客建立全文检索，使用哪种模式好
我觉得自然语言模式比较合适。
如何在多字段上建立 FULLTEXT INDEX
例如，title 和 body 列都需要 FULLTEXT INDEX。
参考 https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html
For natural-language full-text searches, the columns named in the MATCH() function must be the same columns included in some FULLTEXT index in your table. For the preceding query, note that the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table's FULLTEXT index. To search the title or body separately, you would create separate FULLTEXT indexes for each column.
两种情况：
如果，title 和 body 需要分别检索，那么只需要对这两个字段单独建立两个 FULLTEXT INDEX。
如果，title 和 body 需要一起被检索，即 title 和 body 中有一方包含关键词即可，则需要建立类似联合索引
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
alter table 的写法
ALTER TABLE `articles`
ADD FULLTEXT INDEX `ft_title_body` (`title`, `body`) WITH PARSER ngram;
单个字符如何检索
暂时我没有找到简单的可行的方案。我目前采用的一套不太满意的方案：
- 搜索词为一个字时，like 最重要的字段，例如 name。这个地方是有问题的，例如数据量大时，性能有问题。
- 搜索词多于一个字时，使用 fulltext search
if ($keyword) {
if (mb_strlen($keyword) == 1) {
// like name field
$sql->where("name", 'like', '%'.$keyword.'%');
} else {
$sql->whereRaw("match(name, description) against(? IN NATURAL LANGUAGE MODE)", $keyword);
}
}
参考
- https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/fulltext-search.html
- https://blog.csdn.net/mysqldba23/article/details/70160862
微信关注我哦 👍
我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊， 查看更多联系方式