MySQL三种模糊查询方式：​​LIKE、REGEXP和FULLTEXT

概述

• 在 MySQL 中，模糊查询主要有三种方式：LIKE、REGEXP（正则表达式）和 FULLTEXT（全文索引）。它们各有适用场景和性能差异，以下是详细对比和实际应用指南
• 资料已经分类整理好，喜欢的朋友自取：https://pan.quark.cn/s/f52968c518d3

一、LIKE：基础模糊匹配

特点
• 语法简单：使用 %（任意多个字符）和 _（单个字符）通配符。

• 适用场景：简单的前缀、后缀或中间包含的模糊匹配。

• 性能问题：以 % 开头的模糊查询（如 LIKE '%keyword'）无法使用索引，可能导致全表扫描。

示例

-- 查询以 "张" 开头的名字（使用索引，如果字段有索引）
SELECT * FROM users WHERE name LIKE '张%';

-- 查询包含 "张" 的名字（无法使用索引）
SELECT * FROM users WHERE name LIKE '%张%';

二、REGEXP：正则表达式匹配

特点
• 灵活性强：支持复杂模式匹配（如数字、字符范围、重复次数等）。

• 适用场景：需要复杂规则的模糊匹配（如邮箱、手机号格式验证）。

• 性能问题：正则表达式通常比 LIKE 更耗资源，大数据量时需谨慎使用。

示例

-- 匹配以数字结尾的名字
SELECT * FROM users WHERE name REGEXP '[0-9]$';

-- 匹配包含 "张" 或 "李" 的名字
SELECT * FROM users WHERE name REGEXP '张|李';

-- 匹配长度为 3 的中文字符
SELECT * FROM users WHERE name REGEXP '^[\\u4e00-\\u9fa5]{3}$';

三、FULLTEXT：全文索引搜索

特点
• 高效搜索：专为大型文本字段设计（如文章、描述），通过全文索引加速查询。

• 自然语言处理：支持分词、词干提取、停用词过滤（需配置）。

• 限制：

• 仅适用于 MyISAM 和 InnoDB（MySQL 5.6+）引擎。

• 默认不支持中文分词（需借助插件如 ngram）。

• 需要显式创建全文索引。

使用步骤

1. 创建全文索引：

   ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content);
   

2. 查询语法：

   -- 自然语言模式（默认）
   SELECT * FROM articles 
   WHERE MATCH(title, content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE);
   
   -- 布尔模式（支持运算符 + - ><）
   SELECT * FROM articles 
   WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
   

3. 中文全文检索（需启用 ngram 分词）：

   -- 创建表时指定 ngram 分词长度
   CREATE TABLE articles (
       id INT PRIMARY KEY,
       title VARCHAR(200),
       content TEXT,
       FULLTEXT(title, content) WITH PARSER ngram
   ) ENGINE=InnoDB;
   
   -- 查询中文
   SELECT * FROM articles 
   WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);
   

四、对比总结

五、性能优化建议

1. 优先使用前缀匹配（如 LIKE '张%'），可触发索引。
2. 避免全模糊查询（如 LIKE '%张%'），大数据量时改用 FULLTEXT。
3. 对中文全文搜索，考虑使用 Elasticsearch 或 第三方分词插件（如 jieba 分词 + MySQL 集成）。

六、实际选择策略

• 简单模糊查询 → LIKE

• 复杂规则匹配 → REGEXP

• 大文本搜索 → FULLTEXT（需处理中文分词）

• 高并发、大数据量 → 专业搜索引擎（如 Elasticsearch）

小结

根据业务需求和数据规模合理选择工具，避免过度依赖数据库模糊查询！