MySQL database with unique fields ignored ending spaces

by

The problem is that MySQL ignores trailing whitespace when doing string comparison. See
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.

(This information is for 5.7; for 8.0 this changed, see below)

The section for the like operator gives an example for this behavior (and shows that like does respect trailing whitespace):

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

Unfortunately the UNIQUE index seems to use the standard string comparison to check if there is already such a value, and thus ignores trailing whitespace.
This is independent from using VARCHAR or CHAR, in both cases the insert is rejected, because the unique check fails. If there is a way to use like semantics for the UNIQUE check then I do not know it.

What you could do is store the value as 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)

You better do not want to do anything like sorting alphabetically on this column, because sorting will happen on the byte values instead, and that will not be what the users expect (most users, anyway).

The alternative is to patch MySQL and write your own collation which is of type NO PAD. Not sure if someone wants to do that, but if you do, let me know 😉

Edit: meanwhile MySQL has collations which are of type NO PAD, according to https://dev.mysql.com/doc/refman/8.0/en/char.html :

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.

and 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.

So if you try:

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

you can insert values with and without trailing whitespace

You can find all available NO PAD collations with:

 show collation where Pad_attribute="NO PAD";

More Related Contents:

  1. Are table names in MySQL case sensitive?
  2. Can’t connect to MySQL server on ‘localhost’ (10061)
  3. SELECT INTO and “Undeclared variable” error
  4. MySQL 1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
  5. Should I use the datetime or timestamp data type in MySQL?
  6. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  7. Compare two MySQL databases [closed]
  8. MySQL – Trigger for updating same table after insert
  9. How do I get the current time zone of MySQL?
  10. Storing image in database directly or as base64 data?
  11. How to get the difference in years from two different dates?
  12. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  13. MySQL grant all privileges to database except one table
  14. Writing optional parameters within stored procedures in MySQL? [duplicate]
  15. How to get all columns’ names for all the tables in MySQL?
  16. How do I get MySQL to use an INDEX for view query?
  17. mysql stored procedure that calls itself recursively
  18. Restore the mysql database from .frm files
  19. When to use NULL in MySQL tables
  20. Mysql insert random datetime in a given datetime range
  21. Mysql insert into 2 tables
  22. MySQL UPDATE and SELECT in one pass
  23. where does MySQL store database files?
  24. MySQL: How to retrieve a random row or multiple random rows?
  25. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  26. Using SUM on FLOAT data
  27. Duplicate entry for key ‘group_key’
  28. SQL query with avg and group by
  29. How can I rebuild indexes and update stats in MySQL innoDB?
  30. mysql – move rows from one table to another

Leave a Comment