mysql菜鸟教程

首页 >> mysql菜鸟教程

6.5 模糊查询：LIKE和通配符

在实际的数据查询中，我们经常需要根据不完整或不确定的信息查找数据。比如只知道客户姓氏的一部分、产品名称包含某个关键词、或者学号符合某种模式。这时就需要使用模糊查询，而LIKE操作符就是SQL中进行模糊匹配的核心工具。

一、LIKE模糊查询的核心价值

为什么需要模糊查询？

信息不完整：只知道部分信息时的搜索需求
数据变体：处理大小写、空格、简写等数据不一致问题
模式匹配：查找符合特定模式的数据（如特定格式的电话号码）
文本搜索：在文章、描述等文本字段中查找关键词

典型应用场景

在客户数据库中查找所有姓"张"的客户
在产品目录中搜索名称包含"手机"的商品
查找邮箱地址以"@gmail.com"结尾的用户
匹配特定格式的订单号（如"ORD2024****"）

二、LIKE基础语法与通配符

LIKE操作符的基本语法：

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 列名 LIKE '模式字符串';

两个核心通配符

通配符

说明

示例

匹配示例

%

匹配
任意数量
的字符（0个、1个或多个）

'张%'

张三、张无忌、张

_

匹配
单个
任意字符

'李_'

李四、李雷（不匹配：李）

继续使用扩展后的学生信息表：

-- 创建示例数据
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    student_no CHAR(10),
    email VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(200)
);

INSERT INTO students (name, student_no, email, phone, address) VALUES
('张三', '20240001', 'zhangsan@example.com', '13800138001', '北京市海淀区'),
('张无忌', '20240002', 'wuji.zhang@school.edu', '13800138002', '上海市浦东新区'),
('张小凡', '20240003', 'xiaofan_zhang@test.com', '13800138003', '广州市天河区'),
('李四', '20240004', 'lisi@example.com', '13900139001', '深圳市南山区'),
('李雷', '20240005', 'leili@example.com', '13900139002', '杭州市西湖区'),
('王五', '20240006', 'wangwu2024@school.edu', '13700137001', '南京市鼓楼区'),
('赵六', '20240007', 'zhaoliu@gmail.com', '13600136001', '成都市武侯区'),
('欧阳小枫', '20240008', 'ouyang.xf@example.com', '13500135001', '武汉市江汉区'),
('测试用户', 'TEST001', 'test@test.com', NULL, NULL);

三、LIKE基础用法示例

1. 以...开头（前缀匹配）

使用%在模式末尾

-- 查找所有姓"张"的学生
SELECT name, student_no, email
FROM students
WHERE name LIKE '张%';

-- 查找学号以"2024"开头的学生
SELECT name, student_no
FROM students
WHERE student_no LIKE '2024%';

2. 以...结尾（后缀匹配）

使用%在模式开头

-- 查找邮箱以".edu"结尾的学生（教育邮箱）
SELECT name, email
FROM students
WHERE email LIKE '%.edu';

-- 查找电话以"001"结尾的学生
SELECT name, phone
FROM students
WHERE phone LIKE '%001';

3. 包含...（中间匹配）

使用%在模式两端

-- 查找名字中包含"小"字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '%小%';

-- 查找邮箱中包含"example"的学生
SELECT name, email
FROM students
WHERE email LIKE '%example%';

4. 单个字符匹配

使用_匹配单个字符

-- 查找姓"李"且名字为两个字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '李_';

-- 查找学号格式为"2024XXXX"的学生（8位数字）
SELECT name, student_no
FROM students
WHERE student_no LIKE '2024____';

四、复杂模式与组合使用

1. 组合使用%和_

-- 查找名字第二个字是"三"的学生
SELECT name, student_no
FROM students
WHERE name LIKE '_三%';

-- 查找邮箱中"@"符号前有5个字符的学生
SELECT name, email
FROM students
WHERE email LIKE '_____@%';

-- 查找名字以"张"开头，长度为2-3个字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '张_' OR name LIKE '张__';

2. 匹配特定位置

-- 查找学号第三位是"2"的学生
SELECT name, student_no
FROM students
WHERE student_no LIKE '__2%';

-- 查找邮箱用户名部分以"zhang"开头的学生
SELECT name, email
FROM students
WHERE email LIKE 'zhang%@%';

3. 精确长度匹配

-- 查找名字正好是3个字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '___';

-- 查找电话号为11位数字的学生
SELECT name, phone
FROM students
WHERE phone LIKE '___________';

五、转义特殊字符

当需要查找包含通配符%或_本身的数据时，需要使用转义字符：

使用默认转义字符\

-- 假设有数据：name = '张_三'
-- 查找名字中包含下划线的学生
SELECT name, student_no
FROM students
WHERE name LIKE '%\_%';

-- 查找包含"50%"的数据
SELECT column_name
FROM some_table
WHERE column_name LIKE '%50\%%';

自定义转义字符

-- 使用ESCAPE关键字指定转义字符
SELECT name, student_no
FROM students
WHERE name LIKE '%$_%' ESCAPE '$';

六、NOT LIKE：排除匹配

查找不匹配某个模式的数据：

-- 查找不姓"张"的学生
SELECT name, student_no
FROM students
WHERE name NOT LIKE '张%';

-- 查找非教育邮箱的学生
SELECT name, email
FROM students
WHERE email NOT LIKE '%.edu';

-- 查找电话号不是11位数字的学生
SELECT name, phone
FROM students
WHERE phone NOT LIKE '___________' OR phone IS NULL;

七、性能优化与注意事项

1. LIKE查询的性能特点

LIKE '张%'（以...开头）：可以使用索引，性能较好
LIKE '%张'（以...结尾）：不能使用索引，需要全表扫描
LIKE '%张%'（包含...）：不能使用索引，性能最差

2. 优化建议

-- 避免在模式开头使用通配符（如果可能）
-- 慢：全表扫描
SELECT * FROM students WHERE name LIKE '%小%';

-- 相对较快：如果name有索引
SELECT * FROM students WHERE name LIKE '张%';

-- 对于必须的模糊搜索，考虑其他方案
-- 1. 使用全文索引（FULLTEXT）
-- 2. 使用专门的搜索引擎（如Elasticsearch）
-- 3. 预处理数据，建立反向索引

3. 大小写敏感问题

-- MySQL默认的LIKE是大小写不敏感的，但取决于字符集和排序规则
-- 明确指定大小写敏感/不敏感
SELECT name, email
FROM students
WHERE name LIKE 'zhang%';  -- 可能匹配"Zhang"、"zhang"等

-- 使用BINARY进行大小写敏感匹配
SELECT name, email
FROM students
WHERE BINARY name LIKE 'Zhang%';  -- 只匹配"Zhang"开头

八、实用技巧与高级应用

1. 结合其他条件

-- 查找姓"张"且邮箱是教育邮箱的学生
SELECT name, email, student_no
FROM students
WHERE name LIKE '张%' 
  AND email LIKE '%.edu';

-- 查找电话号码以138开头，且名字不是两个字的学生
SELECT name, phone
FROM students
WHERE phone LIKE '138%'
  AND name NOT LIKE '__';

2. 模式匹配的常见用例

-- 邮箱验证模式
SELECT email
FROM students
WHERE email LIKE '%@%.%'  -- 简单邮箱格式验证
  AND email NOT LIKE '%@%@%';  -- 排除多个@符号

-- 手机号模式（中国大陆）
SELECT name, phone
FROM students
WHERE phone LIKE '1________'  -- 1开头，11位
  AND phone REGEXP '^1[3-9][0-9]{9}$';  -- 更精确的正则表达式

-- 学号模式匹配
SELECT name, student_no
FROM students
WHERE student_no LIKE '2024____'  -- 2024年入学
   OR student_no LIKE 'TEST%';    -- 测试数据

3. 使用LIKE进行数据清洗

-- 查找可能无效的电话号码
SELECT name, phone
FROM students
WHERE phone LIKE '%[a-zA-Z]%'  -- 包含字母
   OR phone LIKE '%.%'         -- 包含点号
   OR phone LIKE '%-%'         -- 包含连字符
   OR LENGTH(phone) != 11;     -- 长度不正确

-- 查找可能无效的邮箱
SELECT name, email
FROM students
WHERE email NOT LIKE '%@%.%'   -- 基本邮箱格式
   OR email LIKE '% %'         -- 包含空格
   OR email LIKE '..%'         -- 连续点号
   OR email LIKE '%@%@%';      -- 多个@符号

九、实战练习与解答

练习题目

基于students表，请编写SQL语句实现以下需求：

基础练习：查找所有名字以"李"开头的学生
后缀匹配：查找所有使用gmail邮箱的学生
中间匹配：查找名字中包含"小"字的学生
精确匹配：查找名字正好是三个字的学生
复杂模式：查找学号格式为"2024"后跟任意四个数字的学生
排除匹配：查找不姓"张"也不姓"李"的学生
综合应用：查找手机号以"138"开头，且邮箱是教育邮箱（.edu）的学生

参考答案

-- 1. 查找所有名字以"李"开头的学生
SELECT name, student_no, phone
FROM students
WHERE name LIKE '李%';

-- 2. 查找所有使用gmail邮箱的学生
SELECT name, email
FROM students
WHERE email LIKE '%@gmail.com';

-- 3. 查找名字中包含"小"字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '%小%';

-- 4. 查找名字正好是三个字的学生
SELECT name, student_no
FROM students
WHERE name LIKE '___';

-- 5. 查找学号格式为"2024"后跟任意四个数字的学生
SELECT name, student_no
FROM students
WHERE student_no LIKE '2024____';

-- 6. 查找不姓"张"也不姓"李"的学生
SELECT name, student_no
FROM students
WHERE name NOT LIKE '张%' AND name NOT LIKE '李%';

-- 7. 查找手机号以"138"开头，且邮箱是教育邮箱的学生
SELECT name, phone, email
FROM students
WHERE phone LIKE '138%'
  AND email LIKE '%.edu';

十、LIKE使用要点总结

通配符对比表

通配符	功能	示例	匹配	不匹配
%	任意数量字符	'张%'	张三、张无忌	李四、小张
_	单个字符	'李_'	李四、李雷	李、李小明

性能优化建议

索引利用：尽量使用LIKE '前缀%'，可以利用索引
避免全模糊：LIKE '%模糊%'会导致全表扫描，大数据表慎用
考虑替代方案：频繁的模糊搜索考虑使用全文索引
预处理数据：去除前后空格、统一大小写，提高匹配准确率

常见陷阱与解决方案

问题	原因	解决方案
匹配不到数据	存在空格或不可见字符	使用TRIM()函数清理数据
大小写问题	排序规则导致不敏感	明确指定BINARY或使用正则表达式
性能极差	开头使用通配符	优化查询模式或使用全文搜索
特殊字符匹配	通配符被解释	使用转义字符
NULL值处理	NULL不会匹配任何模式	使用 IS NULL 单独处理

LIKE与其他匹配方式对比

方法	描述	优点	缺点	适用场景
LIKE	简单模式匹配	简单、通用	功能有限、性能问题	简单的前缀/后缀匹配
REGEXP	正则表达式	功能强大、灵活	语法复杂、性能差	复杂模式验证
全文搜索	文本内容搜索	性能好、功能强	需要特殊索引	大文本字段搜索
等值匹配	= 操作符	性能最佳	只能精确匹配	主键、唯一键查询

LIKE操作符是SQL模糊查询的基础工具，虽然功能相对简单，但在日常开发中使用频率极高。掌握它的各种用法和注意事项，能帮助你更高效地处理各种模糊匹配需求。

发表评论

昵称:

联系方式:

评论内容: