MySQL中字符串匹配的N种姿势-CSDN博客

本文链接：https://blog.csdn.net/aa_hdkf_vg/article/details/147768346

MySQL中字符串匹配的N种姿势：从基础到高级用法详解

@[TOC](MySQL中字符串匹配的N种姿势：从基础到高级用法详解)

前言
一、模糊匹配：LIKE与通配符
二、正则表达式匹配：REGEXP
1.基础语法
2.常用正则表达式模式

三、全文索引匹配：FULLTEXT
四、高级函数辅助匹配
五、性能优化总结
总结

前言

在MySQL数据库开发中，字符串匹配是高频需求。无论是用户搜索、数据清洗还是业务逻辑过滤，掌握高效的字符串匹配方法都至关重要。本文将结合实际场景，详细解析MySQL中字符串匹配的多种实现方式，涵盖基础语法、正则表达式、全文索引等核心知识点，帮助开发者快速定位最优解决方案。

一、模糊匹配：LIKE与通配符

LIKE 基本语法

LIKE 是 MySQL 中最常用的字符串匹配操作符，配合通配符实现模式匹配，语法如下：

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

通配符详解

% 通配符（匹配任意字符序列）

查询姓 “张” 的用户(以"张"为前缀)
SELECT * FROM users WHERE username LIKE '张%';

匹配包含 “test” 的字符串（不区分位置）
SELECT * FROM logs WHERE message LIKE '%test%';

_ 通配符（匹配单个任意字符）

查询用户名恰好为 3 位，且以 “a” 开头的记录
SELECT * FROM users WHERE username LIKE 'a__';

注意事项
性能：LIKE ‘%xxx%’ 无法利用索引，建议优化为前缀匹配（如 ‘xxx%’）；
大小写敏感：默认不区分大小写，可通过 BINARY 关键字开启敏感匹配：

SELECT * FROM users WHERE username LIKE BINARY 'Ab%'; -- 区分大小写

二、正则表达式匹配：REGEXP

1.基础语法

MySQL 通过 REGEXP 操作符支持正则表达式匹配，语法如下：

SELECT column_name(s)
FROM table_name
WHERE column_name REGEXP pattern;

2.常用正则表达式模式

字符匹配

^：匹配字符串开头

-- 匹配以数字开头的字符串
SELECT * FROM data WHERE value REGEXP '^[0-9]';

$：匹配字符串结尾

-- 匹配以“.com”结尾的域名
SELECT * FROM urls WHERE domain REGEXP '\.com$';

重复匹配

*：匹配前一个字符 0 次或多次

-- 匹配包含连续多个“a”的字符串
SELECT * FROM texts WHERE content REGEXP 'a*';

+：匹配前一个字符 1 次或多次

-- 匹配至少包含一个“a”的字符串
SELECT * FROM texts WHERE content REGEXP 'a+';

分组与或操作

|：逻辑或

-- 匹配“male”或“female”
SELECT * FROM users WHERE gender REGEXP 'male|female';

()：分组

-- 匹配手机号（支持13/15/18开头）
SELECT * FROM contacts WHERE phone REGEXP '^1(3|5|8)[0-9]{9}$';

性能建议
复杂正则表达式可能导致全表扫描，建议对匹配字段添加索引；
避免在表达式起始位置使用 ^ 以外的锚定符（如 $），以提升匹配效率。

三、全文索引匹配：FULLTEXT

适用场景

当需要处理大量文本数据（如文章内容、日志信息）的模糊匹配时，FULLTEXT 索引是更优选择，相比 LIKE 和 REGEXP 具有更高的查询效率。

创建全文索引

-- 创建表时添加全文索引
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT(title, content)
);

-- 对现有表添加全文索引
ALTER TABLE articles ADD FULLTEXT(title, content);

使用 MATCH AGAINST 查询

-- 简单匹配（查询包含“MySQL”的文章）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL');

-- 布尔模式匹配（+必须包含，-排除）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+优化 -索引' IN BOOLEAN MODE);

-- 自然语言模式（返回相关度排序结果）
SELECT *, MATCH(title, content) AGAINST('性能调优') AS score
FROM articles 
ORDER BY score DESC;

四、高级函数辅助匹配

SOUNDEX 函数（语音匹配）

用于匹配发音相似的字符串，适用于人名、地名的模糊搜索：

-- 查询发音与“Smith”相似的用户
SELECT * FROM users 
WHERE SOUNDEX(username) = SOUNDEX('Smith');

ELT 函数（枚举匹配）

根据索引值返回枚举值，常用于固定列表的快速匹配：

-- 根据状态码返回对应描述
SELECT id, ELT(status, '未开始', '进行中', '已完成') AS status_desc FROM tasks;

五、性能优化总结

匹配方式	适用场景	索引支持	性能等级
LIKE + 前缀匹配	简单模糊查询（如用户名前缀）	支持索引	★★★★☆
REGEXP	复杂模式匹配（如正则校验）	部分支持	★★☆☆☆
FULLTEXT	全文搜索（如文章内容）	全文索引支持	★★★★★
精确匹配	主键 / 唯一键查询	索引全支持	★★★★★