postgresql – 自动完成字段的类似UTF-8字符串

背景 @H_404_1@用户可以输入名称,系统应与文本匹配,即使用户输入或数据库字段包含重音(UTF-8)字符也是如此.这是使用pg_trgm模块.

@H_404_1@问题

@H_404_1@代码类似于以下内容：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label,'fil' ) DESC

@H_404_1@当用户键入fil时,查询匹配filbert但不匹配filépowder. (因为重音字符？)

@H_404_1@解决方案#1失败

@H_404_1@我尝试实现unaccent 函数并将查询重写为：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    unaccent( label ) % unaccent( 'fil' )
  ORDER BY
    similarity( unaccent( t.label ),unaccent( 'fil' ) ) DESC

@H_404_1@这只返回榛子.

@H_404_1@解决方案#2失败

@H_404_1@建议：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

CREATE OR REPLACE FUNCTION unaccent_text(text)
  RETURNS text AS
$BODY$
  SELECT unaccent($1); 
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 1;

@H_404_1@表中的所有其他索引都已删除.然后：

CREATE INDEX label_unaccent_idx 
ON the_table( lower( unaccent_text( label ) ) );

@H_404_1@这只返回一个结果：

SELECT
    t.label
  FROM
    the_table t
  WHERE
    label % 'fil'
  ORDER BY
    similarity( t.label,'fil' ) DESC

@H_404_1@题

@H_404_1@重写查询以确保返回两个结果的最佳方法是什么？

@H_404_1@谢谢！

@H_404_1@有关

@H_404_1@http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Unaccent_filtering_dictionary

@H_404_1@http://postgresql.1045698.n5.nabble.com/index-refuses-to-build-td5108810.html

您没有使用pg_trgm模块提供的运算符类.我会创建一个像这样的索引：

CREATE INDEX label_Lower_unaccent_trgm_idx
ON test_trgm USING gist (lower(unaccent_text(label)) gist_trgm_ops);

@H_404_1@最初,我在这里有一个GIN索引,但后来我才知道GiST可能更适合这种查询,因为它可以返回按相似性排序的值.更多细节：

@H_404_1@> Postgresql: Matching Patterns between Two Columns
> Finding similar strings with PostgreSQL quickly

@H_404_1@您的查询必须与索引表达式匹配才能使用它.

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) % 'fil'
ORDER  BY similarity(label,'fil') DESC -- it's ok to use original string here

@H_404_1@然而,根据％运算符,“filbert”和“filépowder”实际上并非与“fil”非常相似.我怀疑你真正想要的是这个：

SELECT label
FROM   the_table
WHERE  lower(unaccent_text(label)) ~~ '%fil%'
ORDER  BY similarity(label,'fil') DESC -- it's ok to use original string here

@H_404_1@这将找到包含搜索字符串的所有字符串,并根据％运算符首先对最佳匹配进行排序.

@H_404_1@多汁的部分：表达式可以使用自Postgresql 9.1以来的GIN或GiST索引！我引用manual on the pg_trgm moule：

@H_404_1@Beginning in Postgresql 9.1,these index types also support index
searches for LIKE and ILIKE,for example

@H_404_1@如果你真的打算使用％运算符：

@H_404_1@您是否尝试使用set_limit()降低相似度运算符％的阈值：

SELECT set_limit(0.1);

@H_404_1@甚至更低？默认值为0.3.只是看看它是否是过滤额外匹配的阈值.

postgresql – 自动完成字段的类似UTF-8字符串

猜你在找的Postgre SQL相关文章