MySQL 模糊查询指南（以mysql为例）

目录

        1. 基本概念

2. 通配符详解

2.1 百分号（%）

2.2 下划线（_）

2.3 方括号（[]）与正则表达式

3. 大小写敏感性

4. 转义特殊字符

5. 性能优化（新手不用看）

5.1 前缀匹配优先

5.2 全文搜索替代方案

5.3 覆盖索引优化

6. 复杂查询示例

6.1 多条件模糊查询

6.2 正则表达式高级匹配

6.3 结合聚合函数

7. 注意事项

总结

1. 基本概念

        模糊查询用于匹配符合特定模式的字符串，而非精确匹配。MySQL 中主要通过 LIKE 操作符结合通配符实现。

2. 通配符详解

2.1 百分号（%）

匹配任意数量（包括零个）的任意字符。示例：

-- 查询所有姓张的用户  
SELECT * FROM users WHERE name LIKE '张%';  
-- 查询描述中包含"手机"的产品  
SELECT * FROM products WHERE description LIKE '%手机%';  
-- 查询以".com"结尾的邮箱  
SELECT * FROM users WHERE email LIKE '%.com';  

2.2 下划线（_）

匹配单个任意字符。示例：

-- 查询第二个字符是"明"的用户名（如"小明"、"赵明"）  
SELECT * FROM users WHERE username LIKE '_明';  
-- 查询长度为5且第3个字符是"a"的用户名  
SELECT * FROM users WHERE username LIKE '__a__';  

2.3 方括号（[]）与正则表达式

MySQL 中 LIKE 不支持 []，需使用 REGEXP 替代：

-- 查询手机号以13-19开头的用户  
SELECT * FROM users WHERE phone REGEXP '^1[3-9]';  
-- 查询姓"张"、"王"或"李"的用户  
SELECT * FROM users WHERE name REGEXP '^(张|王|李)';  
-- 查询包含数字的用户名  
SELECT * FROM users WHERE username REGEXP '[0-9]';  

3. 大小写敏感性

MySQL 默认不区分大小写（取决于字符集设置）。若需强制区分，可使用 BINARY 关键字：

-- 区分大小写的模糊查询  
SELECT * FROM users WHERE name LIKE BINARY '张%';  
-- 不区分大小写的模糊查询（等效于 ILIKE）  
SELECT * FROM users WHERE LOWER(email) LIKE '%.com';  

4. 转义特殊字符

若需匹配通配符本身（如 % 或 _），需使用 ESCAPE 定义转义字符：

-- 使用反斜杠 \ 作为转义字符  
SELECT * FROM products WHERE name LIKE '%50\%折扣%';  
-- 自定义转义字符（如 @）  
SELECT * FROM products WHERE name LIKE '%50@%折扣%' ESCAPE '@';  

5. 性能优化

模糊查询可能导致全表扫描，以下是优化建议：

5.1 前缀匹配优先

-- 可利用索引（如果 name 字段有索引）  
SELECT * FROM users WHERE name LIKE '张%';  
-- 无法利用索引（非前缀匹配）  
SELECT * FROM users WHERE name LIKE '%张';  

5.2 全文搜索替代方案

对于大量文本数据，使用全文索引（Full-Text Index）：

-- 创建全文索引  
ALTER TABLE articles ADD FULLTEXT(content);  
-- 高效全文搜索  
SELECT * FROM articles WHERE MATCH(content) AGAINST('关键词' IN NATURAL LANGUAGE MODE);  

5.3 覆盖索引优化

确保查询的字段都包含在索引中，避免回表：

-- 如果 (name, age) 是复合索引  
SELECT name, age FROM users WHERE name LIKE '张%';  

6. 复杂查询示例

6.1 多条件模糊查询

SELECT * FROM products  
WHERE (category LIKE '电子%' OR description LIKE '%智能%')  
  AND price > 1000;  

6.2 正则表达式高级匹配

-- 匹配手机号格式  
SELECT * FROM users WHERE phone REGEXP '^1[3-9]\\d{9}$';  
-- 匹配包含字母和数字的密码  
SELECT * FROM users WHERE password REGEXP '^(?=.*[A-Za-z])(?=.*\\d).+$';  

6.3 结合聚合函数

-- 统计每个姓氏的用户数量  
SELECT LEFT(name, 1) AS surname, COUNT(*)  
FROM users  
WHERE name LIKE '%_%'  -- 排除单字符姓名  
GROUP BY surname;  

7. 注意事项

• 索引限制：LIKE '%...' 无法利用索引，应尽量避免。
• 字符集影响：utf8mb4 与 latin1 对特殊字符的处理不同。
• 全文搜索限制：默认不支持少于 4 个字符的关键词搜索。

总结