PostgreSQL全文搜索中包含单引号的词汇存在问题

Question

我需要使用全文搜索来获取包含单词 O'henry 的列的行。

我的代码：

$word = "O'henry";
$sql = "SELECT * FROM mytable WHERE to_tsvector('english', col) @@ to_tsquery(:word) ";

$sth = $db->prepare( $sql );
$sth->execute(
    array(":word"=>$word)
);

我有两个问题：

1）这个查询匹配包含单词O'henry的列，但也匹配包含“O yes, thierry henry is good sportsman.”等内容的列。

2）如果$word以引号开头，例如'henry，我会收到一个错误：syntax error in tsquery: "'henry"，尽管搜索字符串已经被转义了。

我该如何解决这个问题？

- Oto Shavadze

---

你尝试过使用ph_escape_string吗？http://php.net/manual/en/function.pg-escape-string.php。 - Telshin

非常感谢，这解决了我问题中的两个问题，但是第一个问题仍然存在。 - Oto Shavadze

Answer 1

默认的英文词典会将你的数据分词，把'视为空格。你可以使用ts_debug来检查PostgreSQL/tsearch如何处理你的文本：

psql=# SELECT * FROM ts_debug('english','o''henry');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes 
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | o     | {english_stem} | english_stem | {o}
 blank     | Space symbols   | '     | {}             |              | 
 asciiword | Word, all ASCII | henry | {english_stem} | english_stem | {henri}
(3 rows)

从输出结果可以清楚地看出，如果您希望postgres将o'henry视为单个单词，则必须执行以下操作之一：

• 创建一个自定义字典来处理带有撇号的单词
• 或者在使用前从您的tsvector和tsquery中去掉撇号。

我认为第二个选项是最简单的：

$sql = "SELECT * FROM mytable WHERE plainto_tsvector('english', replace(col, '''','')) @@ to_tsquery(replace(:word,'''',''));"

- Chris Farmiloe

---

你知道如何为它创建自定义字典吗？我已经有一个用于去除重音的自定义字典，所以将其整合进去会很好。 - rutchkiwi

Answer 2

阅读文档后，我认为这是to_tsquery接受的字符串类型限制所致。根据此页面所述：

to_tsquery的输入必须遵循tsquery输入的一般规则。

此处定义了tsquery输入规则。根据给定的示例，引擎将每个输入单词处理为单引号字符串，这会破坏您的输入。作为解决方法，我在此论坛帖子中找到了一个建议（用户试图正确转义输入'ksan）。

select *
from items
where to_tsvector(name) @@ to_tsquery(E'[\']ksan')

为了将其应用到您的情况中，您需要使用类似以下内容的预格式化输入：

$word = 'E' . str_replace("'", "[\']", $word);

- George Cummins

---

非常感谢，这解决了我问题中的两个（在我的问题中），但是第一个问题仍然存在。 - Oto Shavadze

你通过测试验证了吗？据我所知，第一个问题是由于你的搜索词被分成了两个字符串（“O”和“henry”），而这种新方法应该可以防止这种情况发生。 - George Cummins

是的，已经测试过了，这个查询：... WHERE to_tsvector('english', col) @@ to_tsquery(E'O[\']henry') 也匹配包含文本 O some words here henry 的列。 - Oto Shavadze

嗯，我明白了。我会继续寻找解决方案，但基于像这个这样的开放性问题，我并不是非常有希望。 - George Cummins