Select trims spaces from strings - is this a bug or in the spec?

Question

in mysql:

select 'a' = 'a     ';

return 1

Answer 1

You're not the first to find this frustrating. In this case, use LIKE for literal string comparison:

SELECT 'a' LIKE 'a    ';    //returns 0

Answer 2

This behavior is specified in SQL-92 and SQL:2008. For the purposes of comparison, the shorter string is padded to the length of the longer string.

From the draft (8.2 <comparison predicate>):

If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD characteristic, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>.

In addition to the other excellent solutions:

select binary 'a' = 'a   '

Answer 3

I googled for "mysql string" and found this:

In particular, trailing spaces [using LIKE] are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator

Answer 4

From the documentation:

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

The trailing spaces are stored in VARCHAR in MySQL 5.0.3+:

CREATE TABLE t_character (cv1 CHAR(10), vv1 VARCHAR(10), cv2 CHAR(10), vv2 VARCHAR(10));

INSERT
INTO    t_character
VALUES  ('a', 'a', 'a ', 'a ');

SELECT  CONCAT(cv1, cv1), CONCAT(vv2, vv1)
FROM    t_character;

but not used in comparison.

Answer 5

Here's another workaround that might help:

select 'a' = 'a     ' and length('a') = length('a     ');

returns 0