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

11 浏览
0 Comments

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

背景

用户可以输入一个名称，系统应该能够匹配到文本，即使用户输入或数据库字段中包含有重音（UTF-8）字符。这可以使用pg_trgm模块实现。

问题

代码如下所示：

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

当用户输入fil时，查询会匹配到filbert，但不会匹配到filé powder。（因为有重音字符吗？）

解决方案失败 #1

我尝试实现了一个unaccent函数，并将查询重写为：

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

这只返回filbert。

解决方案失败 #2

按照建议：

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;

表上的所有其他索引已经被删除。然后：

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

这只返回一个结果：

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

问题

如何重写查询以确保返回这两个结果？谢谢！

相关

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

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

0

2 答案

0 Comments

问题的原因是在自动完成字段中，无法找到类似的UTF-8字符串。解决方法是使用pg_trgm模块提供的操作符类创建索引，并使用GiST索引类型。然后，将查询与索引表达式匹配以使用索引。如果想要使用%操作符，可以调整相似性运算符的阈值来过滤额外的匹配项。

0
0 Comments

问题的出现原因：

该问题的出现是因为在自动完成字段中，需要根据输入的字符串来匹配数据库中的相似字符串。然而，由于字符串的编码问题，可能会导致一些相似的字符串无法被正确匹配到。

解决方法：

为了解决这个问题，可以使用以下解决方法：

1. 安装必要的扩展：

在PostgreSQL 9.1中，需要安装pg_trgm和unaccent这两个扩展。可以使用以下命令进行安装：

CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

2. 创建一个修复STABLE vs. IMMUTABLE问题的函数：

为了解决unaccent函数的STABLE vs. IMMUTABLE问题，需要创建一个函数来修复。该函数将unaccent函数的返回值转换为text类型，并标记为IMMUTABLE。以下是创建函数的命令：

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

3. 创建一个unaccented索引：

为了进行忽略音调和大小写的匹配，需要在label字段上创建一个unaccented索引。以下是创建索引的命令：

CREATE INDEX the_table_label_unaccent_idx
ON the_table USING gin (lower(unaccent_text(label)) gin_trgm_ops);

4. 定义匹配阈值：

定义匹配的阈值，可以使用set_limit函数来设置。以下是设置阈值的命令：

SELECT set_limit(0.175);

5. 进行查询：

在进行查询时，可以使用相似性函数similarity来进行匹配。以下是查询的命令：

SELECT label
FROM the_table
WHERE lower(unaccent_text(label)) % 'fil'
ORDER BY similarity(label, 'fil') DESC;

此外，如果不想使用set_limit函数来设置阈值，还可以使用双波浪号（~~）操作符进行查询：

SELECT label
FROM the_table
WHERE lower(unaccent_text(label)) ~~ 'fil'
ORDER BY similarity(label, 'fil') DESC;

通过以上的解决方法，可以在自动完成字段中实现对UTF-8字符串的相似匹配。

0