具有唯一字段的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

所有MySQL排序规则的类型都是PADSPACE。这意味着MySQL中的所有CHAR、VARCHAR和TEXT值都将进行比较，而不考虑任何尾随空格。

对于那些去掉尾部填充字符或比较忽略它们的情况，如果列的索引需要唯一值，则在列中插入仅在尾部填充字符数上不同的值将导致重复键错误。例如，如果表包含"a"，则尝试存储"a"会导致重复键错误。

（此信息适用于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排序规则都有pad SPACE的pad属性。例外情况是基于UCA 9.0.0及更高版本的Unicode排序规则，其pad属性为NO pad。

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

基于高于4.0.0的UCA版本的Unicode排序规则在排序规则名称中包含该版本。因此，utf8mb4_unicode_520_ci基于UCA 5.2.0权重密钥，而utf8mb4_0900_ai_ci基于UCA 9.0.0权重密钥。

所以，如果你尝试：

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

可以插入带有或不带有尾部空白的值

您可以找到所有可用的无PAD排序规则：

 show collation where Pad_attribute='NO PAD';

Answer 2

这与CHAR与VARCHAR无关。SQL Server在进行字符串比较时不考虑尾部空格，这在检查唯一键约束时也适用。所以，并不是说不能插入带有尾随空格的值，而是一旦插入，就不能插入另一个带有更多或更少空格的值。

作为问题的解决方案，您可以添加一个保持字符串长度的列，并将长度与字符串值作为复合唯一键约束。

在SQL Server 2012中，您甚至可以将长度列作为计算列，这样您就不必担心值了。看见http://sqlfiddle.com/#！6/32e94，以SQL Server 2012为例。（我打赌MySQL中也可能有类似的东西。）

Answer 3

您可能需要了解VARCHAR和CHAR类型之间的差异。

CHAR和VARCHAR类型

在存储CHAR值时，会使用指定长度的空格对其进行右填充。检索CHAR值时，除非启用PAD_CHAR_TO_FULL_LENGTH SQL模式，否则会删除尾部空格。

对于VARCHAR列，无论使用何种SQL模式，都会在插入之前截断超过列长度的尾部空格，并生成警告。对于CHAR列，无论SQL模式如何，都会静默地截断插入值中多余的尾部空格。

VARCHAR值在存储时不会填充。根据标准SQL，在存储和检索值时保留尾随空格。

结论：如果希望在文本字符串的右侧保留空白，请使用CHAR类型（而不是VARCHAR）。

Answer 4

感谢@kennethc。他的回答对我有效。将字符串长度字段添加到表和唯一键中。

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,
string_length int(3),
PRIMARY KEY (id), UNIQUE KEY string (string,string_length) )
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

在MySQL中，可以使用以下触发器更新字符串长度字段：

CREATE TRIGGER `string_length_insert` BEFORE INSERT ON `strings` FOR EACH ROW SET NEW.string_length = char_length(NEW.string);
CREATE TRIGGER `string_length_update` BEFORE UPDATE ON `strings` FOR EACH ROW SET NEW.string_length = char_length(NEW.string);