sql - Mariadb 唯一约束错误(重复条目)，两个记录有一个空间差异

Question

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 ');

第二次插入出错，错误信息是

duplicate entry 'name1 ' for key 'UK_TEST',

突出显示，第二个与第一个不同，后缀中的值多一个空格

有没有人可以帮助我解决这个问题？

Answer 1

根据 the documentation , 对于 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.

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.