mysql中文全文索引的记录

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。

为什么要用全文索引呢？

      一般的数据 库搜索 都是用的SQL 的 like 语句，like 语句是不能利用索引的，每次查询都是从第一条遍历至最后一条，查询效率极其低下。一般数据超过10万或者在线人数过多,like查询都会导致数据库 崩溃。这也就是为什么很多程序 都只提供标题搜索的原因了，因为如果搜索内容，那就更慢了，几万数据就跑不动了。

     Mysql 全文索引是专门为了解决模糊查询提供的，可以对整篇文章 预先按照词进行索引，搜索效率高，能够支持百万级的数据检索。

     如果您使用的是自己的服务器 ，请马上进行设置，不要浪费了这个功能。

创建示例

-- 创建表格
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);
-- 添加全文索引 
-- 使用WITH PARSER ngram语句创建全文索引时，可以指定使用ngram分词器。
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content) WITH PARSER ngram;

-- 执行全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('你要搜索的关键词');

注意：后面要加上WITH PARSER ngram 才可以进行中文检索，ngram是一个全文解析器。

配置问题

ngram_token_size

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。如果你想查询到单个字，那么我们需要设置为1。ngram_token_size的值设置的越小，全文索引占用的空间也越小。一般来说，查询正好等于ngram_token_size的词，速度会更快，但是查询比它更长的词或短语，则会变慢。

-- 显示ngram_token_size的值
show VARIABLES like 'ngram_token_size';

修改方法：在my.ini文件下的 [mysqld] 下面加上 ngram_token_size = 2 即可，当然也可以设置成1。如果是Linux系统则修改my.cnf文件。

比如我的分词长度设置为2 也就是说如果输入“我爱学习”四个字，那么就会拆分成 “我爱”，“爱学”，“学习”，最小两个字进行检索

ft_min_word_len

这个参数只有生成默认索引时才有用，一般用于每个单词间有空格的文字。

在创建中文的全文索引时，我指定使用ngram分词器，所以这个参数对我没有影响。

使用 N-gram 解析器创建的全文索引可以搜索到更短的词语，因为它会将文本分成连续的字符片段作为词条。这意味着即使词语的长度小于 ft_min_word_len 参数的值，它们仍然可以被搜索到。

ft_min_word_len 默认值为 4 ，MySQL 将不会为长度小于 4 的词构建全文索引。

举个例子，假设你有一篇包含短词的文章，比如 "The cat is on the mat."，而 ft_min_word_len 设置为 4。在这种情况下，诸如 "the", "cat", "is", "on", "the", "mat" 这样的短词将不会被包括在全文索引中。因此，如果你搜索 "the"，你将无法找到任何匹配，因为 "the" 这个词在索引中不存在。

--显示当前 MySQL 实例中 ft_min_word_len 的设置值
SHOW VARIABLES LIKE 'ft_min_word_len';

如果你希望更改 ft_min_word_len 的值，你可以通过修改 MySQL 配置文件（通常是 my.cnf 或 my.ini）来实现。找到 ft_min_word_len 参数所在的部分，并将其设置为你想要的值，然后重新启动 MySQL 服务使更改生效。

重建索引

当配置更改后，需要重建索引才能生效。

重新构建索引：你可以使用 ALTER TABLE 语句来重建索引。这会重新创建索引并将其更新为当前表中的最新数据。例如：

-- 存储引擎修改为 MyISAM
ALTER TABLE your_table_name ENGINE=MyISAM;

-- 存储引擎修改为 InnoDB
ALTER TABLE your_table_name ENGINE=InnoDB;

优化表格：你可以使用 OPTIMIZE TABLE 命令来优化表格，这个过程中也会更新索引。例如：

OPTIMIZE TABLE your_table_name;

这会对表格 your_table_name 进行优化，包括重建表格的索引以及释放未使用的空间。

先删除再创建

--删除索引
ALTER TABLE articles DROP INDEX idx_title;
--创建索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_titl(title) WITH PARSER ngram;

索引问题

上面创建的示例中只有content设置了全文索引，要同时查询2个字段，就需要同时对2个字段进行索引，而不是单独再给title字段索引。

-- 删除现有的全文索引
ALTER TABLE articles DROP INDEX idx_content;
-- 添加2个字段的全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_title_content (title, content) WITH PARSER ngram;

现在可以同时查询2个字段了

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('中国人是强大的');

检索方式

自然语言模式

当你在 MATCH ... AGAINST 查询中不指定模式时，默认使用的是自然语言模式（Natural Language Mode）。

在自然语言模式下，MySQL 会根据全文索引的配置和搜索条件的特点，进行一些自动的调整和优化，比如自动忽略停用词、自动对搜索词进行词干提取等。自然语言模式更适合常规的全文搜索，可以提供更直观的搜索结果。

简单讲，就是该模式下会对搜索词进行分词，包含这些分词的结果都能被找到。比如“中国人是最强大的”，内容只要包含“中国、国人、人是、是最、最强、强大、大的”都能匹配到。

布尔模式

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('中国人' IN BOOLEAN MODE)

改模式下不会进行分词，搜索结果必须包含“中国人”，比如一个标题是“中国是强国”，就匹配不到。

在布尔模式下，你可以使用一些布尔运算符，比如 +、-、>、< 等来指定搜索条件，以及使用引号来表示短语搜索。布尔模式更灵活，能够更精确地控制搜索行为，但同时也需要用户提供更多的搜索条件。

以下是布尔模式下的搜索语法规则：

1. 加号 +：表示必须包含该词，即匹配的结果中必须包含该词。

2. 减号 -：表示不能包含该词，即匹配的结果中不能包含该词。

3. 星号 *：表示通配符，可以匹配任意字符。

4. 双引号 " "：表示短语搜索，即搜索结果必须包含完整的短语。

5. 大于号 > 和小于号 <：用于指定词汇的增加或减少权重，> 表示增加权重，< 表示减少权重。

6. 括号 ( )：用于分组条件，可以指定搜索条件的优先级。

示例：

-- 空格相当于是或，匹配`读书`或`快乐`
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('读书 快乐' IN BOOLEAN MODE)
-- 搜索快乐 且必须包含读书
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('快乐 +读书' IN BOOLEAN MODE)
-- 搜索读书 不能包含快乐
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('读书 -快乐' IN BOOLEAN MODE)
-- 完全匹配 `读书 快乐`包括中间的空格
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('"读书 快乐"' IN BOOLEAN MODE)

权重排序

在MySQL的全文搜索中，权重值是根据相关性分数来确定的。相关性分数是根据匹配的程度以及其他因素计算得出的，例如词频、位置等。

我们打开phpmyadmin，执行sql语句，先观察下

SELECT *, 
       MATCH(title) AGAINST('读书使我快乐' ) AS title_score,
       MATCH(content) AGAINST('读书使我快乐' ) AS content_score
FROM articles

这里关键字在标题和内容中的权重是相同的。一般的，我们更偏向于在标题中包含关键字的排在前面。

SELECT *, 
       MATCH(title) AGAINST('读书使我快乐' ) AS title_score,
       MATCH(content) AGAINST('读书使我快乐' ) AS content_score
FROM articles
WHERE MATCH(title, content) AGAINST('读书使我快乐' )
ORDER BY title_score DESC;

可以用大于号增加标题的权重，然后按总权重排序

SELECT *, 
       MATCH(title) AGAINST('>读书快乐' IN BOOLEAN MODE) AS title_score,
       MATCH(content) AGAINST('读书快乐' IN BOOLEAN MODE) AS content_score
FROM articles
WHERE MATCH(title, content) AGAINST('读书快乐' )
ORDER BY (title_score+content_score) DESC;

这样在同等条件下，标题中的权重大于内容。但实际应用时要复杂的多，需要设置实际的权重值才能更好的控制排序。

一个复杂的示例

SELECT *,
    -- 在标题中完全匹配 原有权重乘以10
    MATCH(title) AGAINST('读书快乐' IN BOOLEAN MODE) * 10 AS weighted_title_score,
    -- 在内容中完全匹配 权重乘以5
    MATCH(content) AGAINST('读书快乐' IN BOOLEAN MODE) * 5 AS weighted_content_score,  
    -- 在标题中分词匹配 权重乘以3
    MATCH(title) AGAINST('读书快乐')*3 AS title_score, 
    -- 在内容中分词匹配 权重不变
    MATCH(content) AGAINST('读书快乐') AS content_score
FROM articles
-- 筛选分词包含的内容
WHERE MATCH(title, content) AGAINST('读书快乐' )
-- 根据权重总分排序
ORDER BY (weighted_title_score + weighted_content_score + title_score + content_score) DESC;

不区分大小写

在 MySQL 或 MariaDB 中，可以使用 COLLATE 关键字来指定大小写不敏感的字符集（collation）。

SELECT *
FROM table_name
WHERE column_name LIKE '%abc%' COLLATE utf8_general_ci;

在 SQLite 中，要实现大小写不敏感的搜索，可以通过在查询中使用 `COLLATE NOCASE` 来实现。SQLite 不像某些其他数据库系统那样具有严格的字符集和 collation 设置，因此可以直接使用 `COLLATE NOCASE` 来进行大小写不敏感的搜索。

示例查询：

SELECT *
FROM table_name
WHERE column_name LIKE '%abc%' COLLATE NOCASE;

在上面的例子中，`COLLATE NOCASE` 指示 SQLite 在执行 `LIKE` 操作时不区分大小写。这种方式简单直接，适用于 SQLite 数据库中的文本搜索需求。

近义词问题

MySQL没有内置的同义词功能，所以需要在程序端来完成这个功能。

在本地建立一个近义词表，比如 俊俏——俊美，当我们搜索“俊俏的小伙子”，然后分词为“俊俏、小伙”，循环这个分词数组，取近义词表寻找，取出近义词。所以最终的结果是“俊俏、俊美、小伙”，然后把这个结果推送到mysql进行布尔模式查询。

注意：分词需要在程序端完成，才能去寻找近义词表。

分词器推荐：

Intl.Segmenter 浏览器内置的用于文本分词的 API，用法： https://doc.leovp.com/webapi/intl-segmenter.html

Friso C语言开发的一款高性能中文分词器，官方地址：https://code.google.com/p/friso/