主页 / dba / 问题 / 147645
Accepted
Chloe
Asked: 2016-08-24 11:07:33 +0800 CST

这个唯一索引如何允许重复行？

  • 772

有没有办法让这个唯一索引允许重复行？我想也许有一些额外的空格字符，但我找不到它们。

=> select *, length(keyword), length(country), length(language) from keyword where id in (4588076, 4951423);
   id    |       keyword       | seed_id | source | search_count | country | language | volume | cpc  | competition | modified_on | violation | revenue | length | length | length
---------+---------------------+---------+--------+--------------+---------+----------+--------+------+-------------+-------------+-----------+---------+--------+--------+--------
 4588076 | power wallet review |         | SPYFU  |            0 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
 4951423 | power wallet review |         | SPYFU  |            2 |         |          |     70 | 0.11 |        0.31 |             |           |         |     19 |        |
(2 rows)

指数是

"keyword_keyword_country_language" UNIQUE, btree (keyword, country, language)

PostgreSQL 9.5.3

好的，我打算删除其他两列，但我想我会测试该keyword列并发现：

=> select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column?
---------+---------------------+---------+---------------------+----------
 4588076 | power wallet review | 4951423 | power wallet review | f
postgresql index

2 个回答

  1. 答案可以在令人惊叹的文档中找到.. 看起来您的表中有 NULL 值.. 当数据库检查唯一性时，它会说“NULL 是否等于 NULL？不！” 并允许它。

    下面的重要一点（强调我的）：

    不允许。空值不被视为相等。多列唯一

    如果您想在所有三列中保持唯一性，同时将空值视为相等，那么您必须通过使它们成为部分索引来创造性地使用您的 UNIQUE 索引。

    CREATE UNIQUE INDEX ix1 ON table (col1, col2wNull, col3wNull) WHERE col2wNull is not NULL and col3wNull is not Null;
CREATE UNIQUE INDEX ix2 ON table (col1, col2wNull) WHERE col2wNull is not Null and col3wNull is Null;
CREATE UNIQUE INDEX ix3 ON table (col1, col3wNull) WHERE col2wNull is Null and col3wNull is not Null;
CREATE UNIQUE INDEX ix4 ON table (col1) WHERE col2wNull is NULL and col3wNull is NULL;

    如您所见..它很容易变得有点疯狂。

    另一种选择是将 col2wNull 和 col3wNull 定义为 NOT NULL 并在未提供任何内容时提供一些默认值。这可能是也可能不是一个好主意，具体取决于您在做什么。“魔法值”往往会在以后给你带来很多问题。

    关于您的编辑和两个字符串似乎相等，但数据库报告它们不是 - 我只能想象字符串中有一些“不可见”字符（UTF-8？）。或者它可能很简单，比如一个字符串在末尾有一个额外的空间。这部分取决于您如何将其保存到数据库中。（您是否正在对它们执行 trim()、lower() 等..）

    您可以尝试以各种其他方式比较字符串（例如查看 md5 哈希）。我相信您也可以要求 postgres 将列值转换为十六进制以查看，但是目前如何去做这件事让我无法理解（我很抱歉）。

    • 6
  2. Best Answer

    好的，我发现文本中有一个不间断的空格，它使用了 2 个字节，尽管 Postgres 说它们的长度相同。

    => select k1.id, k1.keyword, k2.id, k2.keyword, k1.keyword=k2.keyword, encode(convert_to(k1.keyword, 'UTF8'), 'hex'), encode(convert_to(k2.keyword, 'UTF8'), 'hex') from keyword k1, keyword k2 where k1.id=4588076 and k2.id=4951423;
   id    |       keyword       |   id    |       keyword       | ?column? |                  encode                  |                 encode
---------+---------------------+---------+---------------------+----------+------------------------------------------+----------------------------------------
 4588076 | power wallet review | 4951423 | power wallet review | f        | 706f7765722077616c6c6574c2a0726576696577 | 706f7765722077616c6c657420726576696577
(1 row)

    这两个十六进制值是

    706f7765722077616c6c6574c2a0726576696577
706f7765722077616c6c657420726576696577

    不同的是c2a0。PHP：

    >>> hex2bin('c2a0')
=> " "

    我搜索c2a0并发现这是一个非破坏性空间。 http://www.fileformat.info/info/unicode/char/00a0/index.htm

    我不知道如何在唯一索引中防止这种情况发生。你？

    另一个答案也值得称赞。

    • 0

相关问题