mysql如何创建全文索引_mysql fulltext索引详解-mysql教程-PHP中文网

mysql全文索引仅支持char/varchar/text列，不支持int、json、blob；innodb与myisam均支持但分词行为不同；中文需依赖ngram插件或外部搜索引擎。

mysql如何创建全文索引_mysql fulltext索引详解

MySQL 全文索引只能建在 CHAR/VARCHAR/TEXT 列上

不是所有字段类型都支持 FULLTEXT，比如 INT、JSON、BLOB（哪怕你用 CAST 转成字符串也不行）——MySQL 会直接报错 ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes。InnoDB 和 MyISAM 都支持，但行为有差异：MyISAM 对停用词更宽松，InnoDB 默认用更严格的内置停用词表，且最小词长是 3（innodb_ft_min_token_size），低于这个长度的词直接被忽略。

实操建议：

建表时就指定 FULLTEXT，比如：

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT(title, body)
);

已有表加索引用 ALTER TABLE articles ADD FULLTEXT(title, body);，别用 CREATE INDEX ——它不支持 FULLTEXT 语法
如果列含大量 HTML 标签或换行符，先清洗再建索引，否则 MATCH ... AGAINST 可能匹配不到预期内容

MATCH() AGAINST() 的三种模式必须选对

MATCH(col) AGAINST('xxx') 看似简单，但默认是自然语言模式（NATURAL LANGUAGE MODE），它不认通配符、不支持布尔操作符；想用 +、- 或 *，必须显式声明 IN BOOLEAN MODE；而想做精确短语匹配，得用 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION（极少用，容易放大噪声）。

常见错误现象：写 AGAINST('+mysql -db' IN BOOLEAN MODE) 却没生效，其实是字段里压根没存 “mysql” 这个独立词（比如存的是 “MySQL8.0”），被分词器切成了 “mysql8” 和 “0”，导致匹配失败。

实操建议：

调试时先用 SELECT * FROM articles WHERE MATCH(title, body) AGAINST('mysql' IN NATURAL LANGUAGE MODE); 看有没有基础命中
布尔模式下，+ 表示“必须包含”，- 表示“必须排除”，但 + 后面不能跟空格，写成 +'mysql' 才合法
想查前缀（如 “mysq*”），必须用布尔模式 + 星号，且星号只支持后缀，AGAINST('mysq*' IN BOOLEAN MODE) 可以，'*sql' 不行

中文全文索引基本不可用，别硬扛

MySQL 原生 FULLTEXT 没有中文分词能力，它按空白符和标点切词，而中文词与词之间没有空格。结果就是：你搜 “数据库”，它把整段文字当一个超长词处理，几乎无法命中；或者把 “MySQL数据库教程” 切成 “MySQL数据库教程” 这一个词，而不是 “MySQL”、“数据库”、“教程”。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

这不是配置能解决的问题，是架构级限制。有人试过改 ft_stopword_file 或调小 innodb_ft_min_token_size，都没用——切词逻辑本身就不适配中文。

实操建议：

中文场景优先考虑外部方案：Elasticsearch、MeiliSearch，或者 MySQL 8.0+ 的 ngram 插件（需提前启用，且只支持前缀匹配，精度有限）
如果非要用 MySQL 原生，至少把标题、标签等短文本单独提取出来，用英文/拼音命名，再建 FULLTEXT
千万别在生产环境上线后才发现中文搜不出，测试阶段就要用真实中文数据跑 MATCH ... AGAINST

重建全文索引会锁表，且不可跳过

InnoDB 的 FULLTEXT 索引不是实时更新的。插入/更新数据后，索引变更会延迟合并（默认每 10000 行或 1 小时触发一次），所以新数据可能几秒甚至几分钟后才可搜到。而一旦执行 OPTIMIZE TABLE 或修改表结构，MySQL 会强制重建全文索引，期间表会被锁住（尤其是大表，可能持续数分钟）。

更隐蔽的问题是：即使你只是给其他字段加普通索引，只要表里存在 FULLTEXT，ALTER TABLE 仍可能触发全文索引重建（取决于存储引擎和 MySQL 版本）。

实操建议：

线上大表加 FULLTEXT 务必在低峰期操作，并预留足够时间
避免频繁 OPTIMIZE TABLE，它对全文索引来说不是“优化”，而是“重做”
监控 innodb_ft_aux_table 状态变量，确认后台合并是否卡住；若卡住，可能需要手动 SET GLOBAL innodb_optimize_fulltext_only = ON; 再跑一次 OPTIMIZE

事情说清了就结束。真正难的不是语法，是分词逻辑和中文适配——这两点踩坑成本最高，也最容易被开发初期忽略。