两个记录的唯一约束错误(重复条目)，只有一个空格

Mariadb is version 10.0.23 I use below script to do testing

Mariadb是10.0.23版本，我使用下面的脚本进行测试

create table test(
username varchar(30)
,constraint UK_TEST unique (username);

insert into test values('name1');
1 row inserted.

insert into test values ('name1 ');

the second insert got error, the error message is

第二个插入有错误，错误消息是

duplicate entry 'name1 ' for key 'UK_TEST',

对于key 'UK_TEST'重复输入'name1 '，

highlight, the second one is not same as the first one, the values have one more space in suffix

突出显示，第二个与第一个不同，值在后缀中还有一个空格

is there anyone can help me on this issue?

在这个问题上有人能帮助我吗?

3 个解决方案

#1

According to the documentation, for VARCHAR and several other data types, trailing spaces are ignored in comparisons, including those used for unique constraints:

根据文件，对于VARCHAR和其他几个数据类型，在比较中忽略了尾随空格，包括用于惟一约束的空格:

Currently, all MariaDB collations are of type PADSPACE, meaning that VARCHAR (as well as CHAR and TEXT values) are compared without regard for trailing spaces. This does not apply to the LIKE pattern-matching operator, which takes into account trailing spaces.

目前，所有MariaDB的排序都是类型为PADSPACE的，这意味着VARCHAR(以及CHAR和TEXT值)在不考虑尾随空格的情况下进行比较。这不适用于类似的模式匹配操作符，它考虑了尾随空格。

If a unique index consists of a column where trailing pad characters are stripped or ignored, inserts into that column where values differ only by the number of trailing pad characters will result in a duplicate-key error.

如果一个唯一索引包含一个列，其中尾垫字符被删除或忽略，那么将值仅与尾垫字符数不同的列插入该列将导致重复键错误。

#2

You have marked "username" field as "UNIQUE". So, it will accept only unique values in that entire column. You are trying to insert duplicate values.

您将“username”字段标记为“UNIQUE”。因此，它只接受整个列中的唯一值。您正在尝试插入重复的值。

阅读更多:https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html

#3

You run into this kind of problems when the username in database is either set to "UNIQUE" or set as "PRIMARY KEY". In your case it is unique, hence you cannot have two lines that share the same username which is very logic and correct.

当数据库中的用户名被设置为“UNIQUE”或设置为“主键”时，就会遇到这种问题。在您的例子中，它是唯一的，因此您不能有两条线共享相同的用户名，这是非常逻辑和正确的。

I advise you to read more about mysql before going any forward.

我建议你在继续学习之前多读一些关于mysql的知识。

Please read more here about primary key constraints: https://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html

请在这里阅读更多关于主键约束的信息:https://dev.mysql.com/doc/refman/5.7/en/constraint-primar-key.html

#1