如何通过SQL查询匹配相似城市名？无需外部搜索服务

我有一个名为cities的表，结构及部分记录如下：

表中还有更多格式一致的记录。我基于Node.js开发了一个API，会接收城市名称参数，该名称可能与name列完全匹配，也可能是其变体（比如拼写错误的'Albarta'、带后缀的'Alberta City'等）。请问能否不使用Elasticsearch这类外部服务，仅通过SQL查询返回对应的行？我试过like运算符，但它的功能太有限，比如下面的查询就无法返回想要的记录：

select * from cities c where c."name" like 'Albarta%'

不用外部服务的话，可以通过以下几种SQL原生或扩展功能实现需求：

1. 正则表达式匹配 #

针对拼写错误（比如单字符替换、遗漏），多数数据库支持正则表达式匹配，能灵活定义匹配规则：

• PostgreSQL（忽略大小写，匹配Albarta/Alberta）：

SELECT * FROM cities
WHERE name ~* 'alb[ae]rta';

• MySQL：

SELECT * FROM cities
WHERE name REGEXP 'alb[ae]rta';

2. 编辑距离（字符串相似度） #

通过计算两个字符串的编辑距离（允许的字符修改/插入/删除次数），筛选相似度高的结果：

• PostgreSQL（需先安装fuzzystrmatch扩展）：

-- 允许最多1个字符差异
SELECT * FROM cities
WHERE levenshtein(lower(name), lower('Albarta')) <= 1;

• MySQL/SQL Server（用发音相似度函数，适合发音接近的拼写）：

SELECT * FROM cities
WHERE DIFFERENCE(name, 'Albarta') >= 3;

（DIFFERENCE返回0-4，值越高发音越接近）

3. 处理带后缀的变体 #

针对像'Alberta City'这类带附加词的参数，可以先清理参数再匹配，或直接匹配核心词：

-- 清理参数中的常见后缀后匹配
SELECT * FROM cities
WHERE lower(name) LIKE lower('%' || replace('Alberta City', ' City', '') || '%');

如果要支持更多后缀，可结合正则替换：

-- PostgreSQL示例，去掉City/Town/Village等后缀
SELECT * FROM cities
WHERE lower(name) LIKE lower('%' || regexp_replace('Alberta City', ' (city|town|village)$', '', 'i') || '%');

4. 标准化存储优化查询 #

提前在表中新增一个标准化字段，存储处理后的城市名（比如小写、去掉后缀、去除特殊字符），查询时同步标准化参数，能大幅提升匹配效率：

-- 1. 添加并初始化标准化字段
ALTER TABLE cities ADD COLUMN normalized_name VARCHAR;
UPDATE cities SET normalized_name = lower(regexp_replace(name, ' (city|town|village)$', '', 'i'));

-- 2. 查询时标准化参数
SELECT * FROM cities
WHERE normalized_name = lower(regexp_replace('Alberta City', ' (city|town|village)$', '', 'i'));

内容的提问来源于stack exchange，提问作者Matt_crud