MySQL学习笔记集--模糊匹配

模糊匹配

1. 使用LIKE进行模糊查询

LIKE是最常用的模糊查询方式，结合通配符%和_实现模式匹配。

• %表示任意多个字符（包括零个字符）：

  SELECT * FROM users WHERE name LIKE '%John%'; -- 匹配任何包含"John"的记录
  SELECT * FROM users WHERE name LIKE 'John%';  -- 匹配以"John"开头的记录
  SELECT * FROM users WHERE name LIKE '%John';  -- 匹配以"John"结尾的记录
  

• _表示任意单个字符：

  SELECT * FROM users WHERE name LIKE 'J%N'; -- 匹配以"J"开头，后面跟着任意字符，然后是"N"的记录
  SELECT * FROM users WHERE name LIKE 'J_N';  -- 匹配以"J"开头，后面跟着一个任意字符，然后是"N"的记录
  

2. 使用REGEXP进行正则表达式匹配

REGEXP提供更强大的模式匹配功能，适用于复杂查询。

• 基本用法：

  SELECT * FROM users WHERE name REGEXP '^J.*n'; -- 匹配以"J"开头，后面跟着任意字符，然后是"n"的记录
  SELECT * FROM users WHERE name REGEXP '[0-9]'; -- 匹配包含数字的记录
  

• 特殊字符：

  SELECT * FROM users WHERE name REGEXP '[^a-zA-Z]'; -- 匹配不包含字母的记录
  

3. 使用内置函数进行模糊匹配

MySQL提供了INSTR、LOCATE和POSITION等函数，用于检查子串是否存在。

• INSTR：

  SELECT * FROM users WHERE INSTR(name, 'John') > 0; -- 检查"John"是否存在于name字段中
  

• LOCATE：

  SELECT * FROM users WHERE LOCATE('John', name) > 0; -- 检查"John"是否存在于name字段中
  

• POSITION：

  SELECT * FROM users WHERE POSITION('John' IN name) > 0; -- 检查"John"是否存在于name字段中
  

4. 使用全文索引进行高效模糊查询

全文索引适用于文本搜索，可以显著提高查询效率。

• 创建全文索引：

  ALTER TABLE users ADD FULLTEXT (name);
  

• 使用全文索引查询：

  SELECT * FROM users WHERE MATCH(name) AGAINST('John'); -- 匹配包含"John"的记录
  

5. 忽略大小写的模糊查询

• 使用不区分大小写的字符集：

  CREATE TABLE users (
      id INT PRIMARY KEY,
      name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
  );
  SELECT * FROM users WHERE name LIKE 'john'; -- 匹配"John"和"john"
  

• 使用LOWER或UPPER函数：

  SELECT * FROM users WHERE LOWER(name) LIKE 'john'; -- 将name字段转换为小写后匹配
  

6. 性能优化建议

• 避免在LIKE查询的开始处使用%，因为这会导致全表扫描。
• 尽量避免复杂的正则表达式，以减少计算量。
• 使用前缀索引，尽管MySQL对前缀索引的支持有限。
• 创建全文索引，可以大幅提升文本搜索的效率。