在数据分析和处理中，文本搜索是一个常见且重要的需求。无论是用户输入纠错、产品名称匹配，还是日志全文检索，DuckDB 都提供了丰富的文本处理工具。本文将通过真实业务场景，带你掌握 DuckDB 中的模糊搜索与文本处理技巧。
场景：电商平台的商品搜索
假设你正在为一个电商平台构建商品搜索系统。用户输入的关键词经常存在拼写错误、同义词或模糊匹配的需求。我们需要一个系统来处理这些场景。
基础模糊匹配：LIKE 操作符
图：DuckDB 文本处理架构概览 — 从用户输入到最终搜索结果的完整流程
LIKE 是最基础的模糊匹配方式，支持 %（任意字符序列）和 _（单个字符）通配符。
-- 示例数据
CREATE TABLE products AS
SELECT * FROM (VALUES
('iPhone 15 Pro Max 256GB', 8999),
('iPhone 15 Pro 128GB', 7999),
('iPhone 14 Plus 128GB', 6999),
('Samsung Galaxy S24 Ultra', 9699),
('Samsung Galaxy S23 FE', 4999),
('Xiaomi 14 Ultra', 5999),
('Xiaomi 14 Pro', 4299),
('华为 Mate 60 Pro', 6999),
('华为 Pura 70 Ultra', 7999),
('OPPO Find X7 Ultra', 5499)
) AS t(name, price);
-- 匹配包含 "iPhone" 的产品
SELECT name, price
FROM products
WHERE name LIKE '%iPhone%';
| name | price |
|---|---|
| iPhone 15 Pro Max 256GB | 8999 |
| iPhone 15 Pro 128GB | 7999 |
| iPhone 14 Plus 128GB | 6999 |
LIKE 适合简单的模式匹配，但在处理拼写错误时力不从心。
进阶正则表达式：regexp
DuckDB 支持标准的正则表达式函数 regexp_matches、regexp_extract 和 regexp_replace。
-- 提取品牌名称
SELECT
name,
regexp_extract(name, '^(iPhone|Samsung|Xiaomi|华为|OPPO)', 1) AS brand,
regexp_extract(name, '\d+\s*GB', 0) AS storage
FROM products;
| name | brand | storage |
|---|---|---|
| iPhone 15 Pro Max 256GB | iPhone | 256GB |
| Samsung Galaxy S24 Ultra | Samsung | NULL |
| Xiaomi 14 Ultra | Xiaomi | NULL |
| 华为 Mate 60 Pro | 华为 | NULL |
| OPPO Find X7 Ultra | OPPO | NULL |
-- 统一价格格式：移除货币符号，提取数字
SELECT
name,
regexp_replace(name, '[\u{FFE5}\u{24}]', '') AS clean_name,
CAST(regexp_extract(name, '\d+', 'g') AS VARCHAR) AS price_digits
FROM products
LIMIT 5;
拼写纠错：Levenshtein 距离
DuckDB 内置了 levenshtein 函数，可以计算两个字符串之间的编辑距离。这在处理用户输入错误时非常有用。
-- 用户搜索词
WITH search AS (
SELECT 'Iphon 15' AS query
),
-- 计算与所有产品的编辑距离
distance AS (
SELECT
p.name,
p.price,
levenshtein(s.query, p.name) AS dist
FROM products p, search s
)
-- 按距离排序，取最相似的3个结果
SELECT name, price, dist
FROM distance
ORDER BY dist ASC
LIMIT 3;
| name | price | dist |
|---|---|---|
| iPhone 15 Pro Max 256GB | 8999 | 9 |
| iPhone 15 Pro 128GB | 7999 | 9 |
| iPhone 14 Plus 128GB | 6999 | 10 |
图：Levenshtein 距离查询执行结果（DuckDB CLI）
-- 批量纠错：为用户搜索词推荐最相似的产品名称
WITH search_terms AS (
SELECT UNNEST(['Iphon 15', 'Samsung Galaxy', 'Xiaomi 14', 'Huawei Mate']) AS query
),
ranked AS (
SELECT
s.query,
p.name AS recommended,
p.price,
levenshtein(s.query, p.name) AS dist,
ROW_NUMBER() OVER (PARTITION BY s.query ORDER BY levenshtein(s.query, p.name)) AS rank
FROM search_terms s
CROSS JOIN products p
)
SELECT query, recommended, price, dist
FROM ranked
WHERE rank <= 2
ORDER BY query, dist;
FTS 全文检索扩展
对于大规模文本搜索，DuckDB 的 FTS（Full-Text Search）扩展提供了类似数据库级全文检索的能力。
-- 加载 FTS 扩展
INSTALL fts;
LOAD fts;
-- 创建 FTS 索引
CREATE TABLE articles AS
SELECT * FROM (VALUES
('DuckDB 入门教程：从安装到实战', '详细介绍 DuckDB 的安装和基本用法'),
('窗口函数进阶：RANK 与 LAG 的应用', '深入讲解窗口函数在数据分析中的高级应用'),
('时间序列分析：滚动聚合与趋势预测', '如何利用 DuckDB 进行时间序列数据的分析'),
('JSON 数据处理：嵌套结构展开技巧', '处理复杂 JSON 数据的最佳实践'),
('DuckDB vs PostgreSQL：性能对比测试', '在不同场景下对比两种数据库的性能表现'),
('用 DuckDB 构建实时数据管道', '从数据采集到可视化的完整流程'),
('SQL 优化技巧：减少扫描提升性能', '实用的 SQL 查询优化方法'),
('DuckDB 扩展生态：插件与连接器', '介绍 DuckDB 丰富的扩展系统')
) AS t(title, content);
-- 创建 FTS 虚拟表
CREATEVIRTUALTABLE v_articles_fts AS FTS_EXPAND('articles');
-- 全文搜索
SELECT title, content, rank
FROM v_articles_fts
WHERE v_articles_fts MATCH '窗口函数 时间序列'
ORDER BY rank ASC
LIMIT 5;
FTS 扩展还支持布尔查询（AND、OR、NOT）、短语搜索和排名排序，适合构建搜索建议和高亮功能。
综合实战：商品搜索建议系统
将上述技术整合，构建一个完整的商品搜索建议系统：
-- 搜索建议系统
WITH user_query AS (
SELECT 'Samsng Galxy S2' AS query
),
-- 1. 精确匹配
exact_match AS (
SELECT name, price, 0 AS priority, 'exact' AS match_type
FROM products
WHERE name ILIKE '%Samsng Galxy S2%'
),
-- 2. 正则模糊匹配
regexp_match AS (
SELECT name, price, 1 AS priority, 'regexp' AS match_type
FROM products
WHERE regexp_like(name, 'Sam(sun)?g.*Galax(y|se).*S2')
AND name NOT IN (SELECT name FROM exact_match)
),
-- 3. Levenshtein 模糊匹配
fuzzy_match AS (
SELECT
p.name, p.price,
2 AS priority,
'fuzzy' AS match_type,
levenshtein(u.query, p.name) AS dist
FROM products p, user_query u
WHERE levenshtein(u.query, p.name) <= 8
AND p.name NOT IN (SELECT name FROM exact_match)
AND p.name NOT IN (SELECT name FROM regexp_match)
ORDER BY dist
LIMIT 3
),
-- 合并结果
all_matches AS (
SELECT * FROM exact_match
UNION ALL
SELECT * FROM regexp_match
UNION ALL
SELECT * FROM fuzzy_match
)
SELECT
name,
price,
match_type,
CASE match_type
WHEN 'fuzzy' THEN dist
ELSE NULL
END AS similarity_score
FROM all_matches
ORDER BY priority, similarity_score;
| name | price | match_type | similarity_score |
|---|---|---|---|
| Samsung Galaxy S24 Ultra | 9699 | fuzzy | 6 |
| Samsung Galaxy S23 FE | 4999 | fuzzy | 7 |
性能优化建议
- LIKE 索引：对于频繁查询的前缀模式（如
name LIKE 'Samsung%'），可以创建 B-tree 索引。 - FTS 索引：FTS 扩展使用倒排索引，适合大规模文本搜索。
- 批量 Levenshtein：避免对全表计算 Levenshtein 距离，先用 LIKE 过滤缩小范围。
- ILIKE 替代 LIKE：不区分大小写时使用
ILIKE，避免额外的大小写转换开销。
总结
DuckDB 提供了从简单到复杂的完整文本处理工具链：
- LIKE/ILIKE：快速前缀和后缀匹配
- 正则表达式：灵活的pattern匹配和提取
- Levenshtein：拼写纠错和模糊匹配
- FTS 扩展：大规模全文检索
根据你的场景选择合适的工具，可以显著提升数据处理的效率和准确性。
更多 DuckDB 实战技巧，请关注 DuckDB Lab（duckdblab.org）