postgresql - WHERE name = 'cat' 失败而 `WHERE name LIKE ' %cat` 在 PostGres 上工作

Question

我在 Postgres 上有一个非常简单的查询，它以 WHERE = 失败，但可以使用 WHERE LIKE。我有标签，其中一些标签的名称包含字母“cat”:

pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE '%cat%'
       ORDER BY tagging_tag.name ASC;

 id   |        name         | synonym_id
------+---------------------+------------
68496 | blackcat            |
    4 | cat                 |
28461 | catfight            |

看起来我有一个名为“cat”的标签，但如果我尝试自己获取它，它会失败:

pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name = 'cat'
       ORDER BY tagging_tag.name ASC;
id | name | synonym_id
---+------+------------
(0 line)

但如果我尝试使用部分 LIKE，它会起作用:

pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE '%cat'
       ORDER BY tagging_tag.name ASC;
 id   |        name         | synonym_id
------+---------------------+------------
68496 | blackcat            |
    4 | cat                 |

pgdb=> SELECT * FROM tagging_tag WHERE tagging_tag.name LIKE 'cat%'
       ORDER BY tagging_tag.name ASC;
 id   |      name      | synonym_id
------+----------------+------------
    4 | cat            |
28461 | catfight       |

我试图检查大小，想到了一个不可见的字符，但没有机会:

pgdb=> SELECT char_length(name), * FROM tagging_tag WHERE tagging_tag.name LIKE 'cat%'
       ORDER BY "tagging_tag"."name" ASC;
char_length |  id   |      name      | synonym_id
------------+-------+----------------+------------
          3 |     4 | cat            |
          8 | 28461 | catfight       |

我做了几次测试，似乎有些标签可以用 = 获取，有些不能，而且我找不到它们之间的任何共同点:字母的数量各不相同，它们全部为ASCII小写，ID不分组等。

这是一个解释:

EXPLAIN SELECT * FROM tagging_tag WHERE tagging_tag.name = 'cat'
        ORDER BY tagging_tag.name ASC;
                                    QUERY PLAN
-------------------------------------------------------------------------------------
Index Scan using tagging_tag_name on tagging_tag  (cost=0.29..4.31 rows=1 width=19)
  Index Cond: ((name)::text = 'cat'::text)
(2 lignes) 

关于表格的一些上下文:

pgdb=> \d tagging_tag
                                      Table « public.tagging_tag »
     Colonne   |         Type          |                        Modificateurs
    -----------+-----------------------+-------------------------------------
    id         | integer               | non NULL Par défaut, nextval('...
    name       | character varying(50) | non NULL
    synonym_id | integer               |
    Index :
       "tagging_tag_id_pkey" PRIMARY KEY, btree (id)
       "tagging_tag_name" UNIQUE, btree (name)
       "tagging_tag_synonym_id" btree (synonym_id)
    Foreign key contraints :
       "tagging_tag_synonym_id_fkey" FOREIGN KEY (synonym_id) REFERENCES tagging_tag(id)
    Referenced by :
       TABLE "tagging_tag" CONSTRAINT "tagging_tag_synonym_id_fkey"
       FOREIGN KEY (synonym_id) REFERENCES tagging_tag(id) 

Postgres 版本为 9.3.6。

Answer 1

因为 name LIKE '%cat' 和 name LIKE 'cat%' 的两个测试都返回相同的行，并且它只包含字符串 'cat' 一次(或它？)从逻辑上讲，name = 'cat' 也应该返回同一行。

前导或尾随空格无法解释这一点。

其余的解释包括:

1. 一个误解，您使用不同的数据库/不同的表、不同的 search_path 或类似的东西进行了测试。

2. 另一种误解:你的字符串中有一个换行符，实际上看起来是这样的:

   cat
   cat
   

   你错过了第二行？

3. 损坏的索引。 EXPLAIN 输出显示可能使用哪些索引。重新创建涉及的索引并查看是否可以解决问题。您的问题更新显示它必须是索引 tagging_tag_name:

   REINDEX INDEX tagging_tag_name;
   

   暴力替代方案是:

   VACUUM FULL tagging_tag;
   

   重写整个表及其上的所有索引(采用独占锁)。