模糊搜索与模式匹配

模糊搜索是实现搜索功能的核心技术，本篇介绍各种模糊搜索的实现方式。

📊 支持情况

1. LIKE 模糊匹配

基本语法

-- % 匹配任意多个字符
SELECT * FROM users WHERE name LIKE '%张%';   -- 包含"张"
SELECT * FROM users WHERE name LIKE '张%';    -- 以"张"开头
SELECT * FROM users WHERE name LIKE '%张';    -- 以"张"结尾
SELECT * FROM users WHERE name LIKE '%张%三%';  -- 包含"张"和"三"

-- _ 匹配单个字符
SELECT * FROM users WHERE phone LIKE '138____6789';  -- 手机号中间4位任意

大小写敏感

-- MySQL默认不区分大小写
SELECT * FROM users WHERE name LIKE '%ZHANG%';  -- 也会匹配"张三"

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

Oracle（支持ILIKE）

-- Oracle支持ILIKE（不区分大小写）
SELECT * FROM users WHERE name ILIKE '%ZHANG%';

-- 等同于
SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%ZHANG%');

Hive

SELECT * FROM users WHERE name LIKE '%张%';
SELECT * FROM users WHERE phone LIKE '138____6789';

2. INSTR / LOCATE 位置查找

MySQL

-- INSTR返回子串位置（找不到返回0）
SELECT INSTR('Hello World', 'World');  -- 7
SELECT INSTR('Hello World', 'SQL');    -- 0

-- LOCATE可以指定起始位置
SELECT LOCATE('o', 'Hello World');     -- 5
SELECT LOCATE('o', 'Hello World', 6);  -- 8（从第6个字符开始找）

Oracle

SELECT INSTR('Hello World', 'World') FROM dual;  -- 7
SELECT INSTR('Hello World', 'o', 1, 2) FROM dual;  -- 8（找第2个o）

-- LOCATE
SELECT LOCATE('World', 'Hello World') FROM dual;

Hive / MaxCompute

SELECT instr('Hello World', 'World');  -- 7
SELECT instr('Hello World', 'o');      -- 5

ClickHouse

SELECT position('Hello World', 'World');  -- 7
SELECT positionCaseInsensitive('Hello World', 'world');  -- 7（不区分大小写）

3. PATINDEX 模式位置

SQL Server / PostgreSQL

-- 返回匹配模式的位置
SELECT PATINDEX('%[0-9]%', 'abc123def');  -- 4（第一个数字位置）
SELECT PATINDEX('%[a-z]%', '123abc');    -- 4（第一个字母位置）

MySQL替代方案

-- 使用REGEXP
SELECT REGEXP_INSTR('abc123def', '[0-9]');  -- 4

4. SIMILAR TO（标准SQL）

MySQL / Oracle

-- MySQL
SELECT 'abc' SIMILAR TO 'a%';        -- 1
SELECT 'abc' SIMILAR TO '(a|b|c)%';  -- 1

-- Oracle
SELECT 'abc' FROM dual WHERE SIMILAR TO 'a%';        -- abc
SELECT 'abc' FROM dual WHERE SIMILAR TO '(a|b|c)%';  -- abc

-- 组合模式
SELECT * FROM users
WHERE name SIMILAR TO '%(张|李|王)%';  -- 包含张、李或王

SIMILAR TO 语法

5. 正则表达式匹配

MySQL 8.0+

SELECT * FROM users WHERE email REGEXP '@gmail\\.com$';
SELECT * FROM products WHERE name REGEXP '手机|平板|笔记本';

Oracle

SELECT * FROM users WHERE REGEXP_LIKE(email, '@gmail\.com$');
SELECT * FROM products WHERE REGEXP_LIKE(name, '手机|平板|笔记本');

Hive

SELECT * FROM users WHERE email RLIKE '@gmail\\.com$';
SELECT * FROM products WHERE name RLIKE '手机|平板|笔记本';

6. 全文索引搜索

MySQL 全文索引

-- 创建全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_title_content (title, content);

-- MATCH AGAINST搜索
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE);

-- 布尔模式
SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('+数据库 -MySQL' IN BOOLEAN MODE);

MySQL 布尔模式操作符

Oracle 全文索引

-- 创建Oracle Text索引
CREATE INDEX idx_article_content ON articles(content) INDEXTYPE IS CTXSYS.CONTEXT;

-- 使用CONTAINS搜索
SELECT *
FROM articles
WHERE CONTAINS(content, '数据库 AND 优化') > 0;

ClickHouse 全文搜索

-- 使用 LIKE 优化
SELECT * FROM logs WHERE message LIKE '%error%';

-- 使用 full_text 索引
SELECT *
FROM articles
WHERE message ILIKE '%Database%';

7. 相似度匹配

MySQL 相似度（8.0+）

-- 莱文斯坦距离
SELECT LEVENSHTEIN('kitten', 'sitting');  -- 3

-- 相似度百分比
SELECT SIMILARITY('hello', 'helo');  -- 0.75

Oracle 相似度

-- UTL_MATCH包
SELECT UTL_MATCH.EDIT_DISTANCE('kitten', 'sitting') FROM dual;  -- 3
SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY('hello', 'helo') FROM dual;

点击House 编辑距离

SELECT levenshteinDistance('kitten', 'sitting');  -- 3

8. 搜索优化技巧

索引利用

-- ✅ 避免前导通配符
-- ❌ SELECT * FROM users WHERE name LIKE '%张%';  -- 无法使用索引

-- ✅ 前缀匹配可以使用索引
SELECT * FROM users WHERE name LIKE '张%';  -- 可以使用索引

倒排索引搜索

-- 搜索关键词分词后查询倒排表
SELECT article_id
FROM keyword_index
WHERE keyword IN ('数据库', '优化', '性能')
GROUP BY article_id
HAVING COUNT(*) = 3;

Elasticsearch集成

-- 使用外部搜索引擎
SELECT *
FROM products
WHERE es_match('name', '高性能数据库');

9. 实战案例

场景1：商品搜索

-- MySQL 基础搜索
SELECT *
FROM products
WHERE name LIKE CONCAT('%', '手机', '%')
   OR description LIKE CONCAT('%', '手机', '%');

-- 加权搜索
SELECT *,
       (CASE WHEN name LIKE '%手机%' THEN 2 ELSE 0 END +
        CASE WHEN description LIKE '%手机%' THEN 1 ELSE 0 END) AS score
FROM products
HAVING score > 0
ORDER BY score DESC;

场景2：用户模糊查询

-- MySQL 多字段模糊匹配
SELECT *
FROM users
WHERE CONCAT(first_name, last_name) LIKE '%张三%'
   OR email LIKE CONCAT('%', 'zhangsan', '%')
   OR phone LIKE CONCAT('%', '1234', '%');

场景3：日志搜索

-- Hive 多种匹配方式
SELECT *
FROM logs
WHERE message LIKE '%ERROR%'
   AND message LIKE '%timeout%'
   AND message NOT LIKE '%INFO%';

-- 使用正则
SELECT *
FROM logs
WHERE message RLIKE 'ERROR.*timeout';

场景4：拼音搜索

-- MySQL 中文拼音搜索
SELECT *
FROM users
WHERE pinyin LIKE CONCAT('%', SUBSTRING('zhangsan', 1, 1), '%');

10. 高级搜索技巧

多关键词搜索

-- 包含所有关键词
SELECT *
FROM articles
WHERE title LIKE '%数据库%'
  AND title LIKE '%优化%'
  AND title LIKE '%性能%';

-- 包含任一关键词
SELECT *
FROM articles
WHERE title LIKE '%数据库%'
   OR title LIKE '%优化%'
   OR title LIKE '%性能%';

排除搜索

-- 排除特定词
SELECT *
FROM products
WHERE name LIKE '%手机%'
  AND name NOT LIKE '%苹果%'
  AND name NOT LIKE '%华为%';

邻近搜索

-- 两个词在一定距离内
-- MySQL 8.0+ 正则表达式
SELECT *
FROM articles
WHERE content REGEXP '数据库.{0,50}优化';  -- 数据库和优化在50字符内

-- Oracle
SELECT *
FROM articles
WHERE REGEXP_LIKE(content, '数据库.{0,50}优化');

⚠️ 注意事项

1. 性能问题

-- ❌ 前导通配符无法使用索引
SELECT * FROM users WHERE name LIKE '%abc%';

-- ✅ 解决方案：使用全文索引
ALTER TABLE users ADD FULLTEXT INDEX idx_name (name);
SELECT * FROM users WHERE MATCH(name) AGAINST('abc' IN NATURAL LANGUAGE MODE);

2. SQL注入风险

-- ❌ 危险：直接拼接用户输入
SELECT * FROM users WHERE name LIKE '%' + user_input + '%';

-- ✅ 安全：使用参数化查询
PREPARE stmt FROM 'SELECT * FROM users WHERE name LIKE ?';
SET @pattern = CONCAT('%', ?, '%');
EXECUTE stmt USING @pattern;

3. 特殊字符转义

-- LIKE中的特殊字符：%、_、\
-- 需要转义
SELECT * FROM users WHERE name LIKE '%\\%%' ESCAPE '\\';  -- 匹配包含%的名字

-- Oracle
SELECT * FROM users WHERE name LIKE '%\%%' ESCAPE '\';

📝 练习题

-- 建表
CREATE TABLE products (
    id INT,
    name STRING,
    description STRING,
    category STRING
);

INSERT INTO products VALUES
(1, 'iPhone 15 Pro', '苹果最新旗舰手机，A17芯片', '手机'),
(2, '华为Mate60', '华为旗舰手机，鸿蒙系统', '手机'),
(3, '小米14', '小米旗舰手机，骁龙8 Gen3', '手机'),
(4, 'MacBook Pro', '苹果笔记本电脑，M3芯片', '电脑'),
(5, 'ThinkPad X1', '联想商务笔记本，轻薄便携', '电脑'),
(6, 'iPad Pro', '苹果平板，M2芯片', '平板');

要求：

1. 搜索包含"手机"或"平板"的产品
2. 搜索"苹果"品牌产品（排除其他）
3. 搜索包含"芯片"的产品
4. 搜索名称以"i"开头的产品

-- 参考答案
-- 1. 多关键词
SELECT *
FROM products
WHERE name LIKE '%手机%' OR name LIKE '%平板%';

-- 2. 品牌搜索
SELECT *
FROM products
WHERE (name LIKE '%苹果%' OR description LIKE '%苹果%')
  AND name NOT LIKE '%小米%'
  AND name NOT LIKE '%华为%'
  AND name NOT LIKE '%联想%';

-- 3. 描述搜索
SELECT *
FROM products
WHERE description LIKE '%芯片%';

-- 4. 前缀匹配
SELECT *
FROM products
WHERE name LIKE 'i%';