当前位置：首页 > ops >正文

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

ops 2025/8/27 14:10:46

概述

在 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）。

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

使用步骤

创建全文索引：

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

查询语法：

-- 自然语言模式（默认）
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);

中文全文检索（需启用 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);

四、对比总结

方法	优点	缺点	适用场景
`LIKE`	简单易用，支持基本模糊匹配	性能差（以 `%` 开头时）	简单前缀、后缀或包含匹配
`REGEXP`	支持复杂模式匹配	性能较差，语法复杂	格式验证、复杂规则匹配
`FULLTEXT`	高效文本搜索，支持自然语言处理	配置复杂，中文支持需额外处理	大文本字段搜索（如文章、日志）