使用unaccent模块 - 这与您链接的完全不同。
unaccent是一个删除重音的文本搜索词典(变音符号 来自lexemes的迹象)。
每个数据库安装一次:
CREATE EXTENSION unaccent;
如果您收到如下错误:
错误:无法打开扩展控制文件 “/usr/share/postgresql/9.x/extension/unaccent.control”:没有这样的文件 或目录
按照此相关答案中的说明在数据库服务器上安装contrib包:
在PostgreSQL上创建非强制扩展时出错
除此之外,它提供了您可以与您的示例一起使用的功能~(其中似乎不需要ILIKE)。
SELECT *
FROM users
WHERE unaccent(name) = unaccent('João');
指数
要为该类查询使用索引,请在表达式上创建索引。 但是,Postgres仅接受~索引功能。 如果函数可以为同一输入返回不同的结果,则索引可能会无声地中断。
~ only ILIKE not IMMUTABLE
不幸的是,~仅为ILIKE,而不是IMMUTABLE.根据pgsql-bugs上的这个帖子,这是由于三个原因:
这取决于字典的行为。
此字典没有硬连线连接。
因此它还取决于当前的~,它可以轻松改变。
网上的一些教程指示只是将函数波动率改为~。这种强力方法在某些条件下会破坏。
其他人建议使用简单的~包装函数(就像我过去自己做的那样)。
关于是否使用两个参数~来明确声明使用的字典存在争议。 在这里或这里阅读。
另一个替代方案是这个模块具有Musicbrainz的IMMUTABLE ~功能,在Github上提供。 没有自己测试过。 我想我已经提出了一个更好的主意:
现在最好
我提出的方法至少与其他解决方案一样有效,但更安全:使用双参数形式创建包装函数,并使用“hard-wire”函数和字典的模式:
CREATE OR REPLACE FUNCTION public.f_unaccent(text)
RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1) -- schema-qualify function and dictionary
$func$ LANGUAGE sql IMMUTABLE;
~是安装扩展的架构(默认为ILIKE)。
以前,我已经在函数中添加了~ - 直到我发现字典也可以是模式限定的,目前(第10页)没有记录。 在我的第9.5行和第10页的测试中,这个版本有点短,大约快两倍。
更新后的版本仍然不允许函数内联,因为声明为~的函数可能不会调用正文中的非不可变函数来允许它。 当我们在ILIKE函数上使用表达式索引时,对性能很重要:
CREATE INDEX users_unaccent_name_idx ON users(public.f_unaccent(name));
使用Postgres 10.3 / 9.6.8等来加强客户端程序的安全性。在任何索引中使用时,您需要对函数和字典进行模式限定。 看到:
postgres日志中的“文本搜索词典”unaccent“不存在”条目,据推测在自动分析期间
调整您的查询以匹配索引(以便查询计划程序可以使用它):
SELECT * FROM users
WHERE f_unaccent(name) = f_unaccent('João');
您不需要正确表达式中的函数。 您可以直接提供~等非重音字符串。
连字
在Postgres 9.5或更早的连字符如'Œ'或'ß'必须手动扩展(如果你需要),因为~总是替换一个字母:
SELECT unaccent('Œ Æ œ æ ß');
unaccent
----------
E A e a S
你会喜欢这个更新,以便在Postgres 9.6中使用unaccent:
扩展~的标准ILIKE文件以处理所有 Unicode已知的变音符号,并正确扩展连字(Thomas Munro,LéonardBenedetti)
大胆强调我的。 现在我们得到:
SELECT unaccent('Œ Æ œ æ ß');
unaccent
----------
OE AE oe ae ss
模式匹配
对于具有任意模式的~或~,请将其与PostgreSQL 9.1或更高版本中的模块~结合使用。 创建一个三元组GIN(通常是可取的)或GIST表达式索引。 GIN的示例:
CREATE INDEX users_unaccent_name_trgm_idx ON users
USING gin (f_unaccent(name) gin_trgm_ops);
可用于以下查询:
SELECT * FROM users
WHERE f_unaccent(name) LIKE ('%' || f_unaccent('João') || '%');
GIN和GIST索引的维护成本比普通btree要贵:
GiST和GIN指数之间的差异
对于左锚定模式,有更简单的解决方案。 有关模式匹配和性能的更多信息
与PostgreSQL中的LIKE,SIMILAR TO或正则表达式匹配的模式
~还为“相似性”(~)和“距离”(ILIKE)提供了有用的运算符。
Trigram索引还支持~等的简单正则表达式。 和不区分大小写的模式匹配ILIKE:
PostgreSQL重音+不区分大小写的搜索