mysql treatment of of ' '

Question

MySQL statement

mysql> select * from field where dflt=' '

appears to match empty values; and is different from statement

mysql> select * from field where concat('_',dflt,'_') = '_ _';

I couldn't find a description of this behavior in MySQL reference. How can I make MySQL interpret input literally?

EDITED: This indeed won't match NULL values, but it does match empty values.

"appears to match null / empty values" --- nope, it won't match `NULL` values. `NULL` != "empty string" — zerkms, Aug 27 '12 at 00:36
Yes, that's technically correct, however it also won't retrieve the rows you'd expect, and 'is null' will retrieve both nulls and empty varchars. See http://www.techonthenet.com/oracle/questions/empty_null.php and http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5984520277372 for example. — paxdiablo, Aug 27 '12 at 01:00

score 2 · Accepted Answer · edited May 23 '17 at 11:56

As mentioned in The CHAR and VARCHAR Types:

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 definition of the LIKE operator states:

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

As mentioned in this answer:

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   '

score 1 · Answer 2 · answered Aug 27 '12 at 00:40

I couldn't find any documentation, but it is widely known that trailing spaces are ignored when doing a text comparison.

To force a literal match, try this:

select *
from field
where dflt = ' '
and length(dflt) = 1; // length does not ignore trailing spaces

mysql treatment of of ' '

2 Answers2