具有唯一字段的 MySQL 数据库忽略了结尾空格

Question

我的项目需要从用户开始输入一个单词的左边和右边的空格，例如“apple”。如果用户输入'apple'或'apple'，无论是单词左侧还是右侧的一个空格或多个空格，我都需要这样存储。

此字段具有 Unique 属性，但我尝试在左侧插入带有空格的单词，但效果很好。但是，当我尝试在右侧插入带有间距的单词时，它会剪掉单词右侧的所有间距。

所以我想在空格后面的单词右边加一个特殊字符。但我希望这个问题有更好的解决方案。

CREATE TABLE strings
( id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
string varchar(255) COLLATE utf8_bin NOT NULL,
created_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id), UNIQUE KEY string (string) )
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Answer 1

问题是 MySQL 在进行字符串比较时会忽略尾随空格。看 http://dev.mysql.com/doc/refman/5.7/en/char.html

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces.

...

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

(此信息适用于 5.7；对于 8.0，此信息已更改，请参见下文)

like 运算符的部分给出了此行为的示例(并表明 like 确实遵守尾随空格):

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)

不幸的是，UNIQUE 索引似乎使用标准字符串比较来检查是否已经存在这样的值，因此忽略了尾随空格。这与使用 VARCHAR 或 CHAR 无关，在这两种情况下插入都会被拒绝，因为唯一性检查失败。如果有一种方法可以将 like 语义用于 UNIQUE 检查，那么我不知道。

您可以将值存储为 VARBINARY:

mysql> create table test_ws ( `value` varbinary(255) UNIQUE );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into test_ws (`value`) VALUES ('a');
Query OK, 1 row affected (0.08 sec)

mysql> insert into test_ws (`value`) VALUES ('a ');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT CONCAT( '(', value, ')' ) FROM test_ws;
+---------------------------+
| CONCAT( '(', value, ')' ) |
+---------------------------+
| (a)                       |
| (a )                      |
+---------------------------+
2 rows in set (0.00 sec)

您最好不要在此列上按字母顺序排序，因为排序将发生在字节值上，而这将不是用户所期望的(无论如何，大多数用户)。

另一种方法是修补 MySQL 并编写您自己的 NO PAD 类型的排序规则。不确定是否有人想这样做，但如果你这样做，请告诉我；)

编辑:同时根据 https://dev.mysql.com/doc/refman/8.0/en/char.html，MySQL 具有 NO PAD 类型的排序规则:

Most MySQL collations have a pad attribute of PAD SPACE. The exceptions are Unicode collations based on UCA 9.0.0 and higher, which have a pad attribute of NO PAD.

和https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html

Unicode collations based on UCA versions later than 4.0.0 include the version in the collation name. Thus, utf8mb4_unicode_520_ci is based on UCA 5.2.0 weight keys, whereas utf8mb4_0900_ai_ci is based on UCA 9.0.0 weight keys.

所以如果你尝试:

  create table test_ws ( `value` varbinary(255) UNIQUE )
    character set utf8mb4 collate utf8mb4_0900_ai_ci;

您可以插入带或不带尾随空格的值

您可以找到所有可用的 NO PAD 归类:

 show collation where Pad_attribute='NO PAD';