12

If the database row is like this: country = 'usa' and i query "select * from data where country = 'usa '" it also returns this row. So its not an exact match.

Why MySQL does this? And in what other cases it will also return TRUE when its not really true?

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
user1113803
  • 145
  • 2
  • 6

5 Answers5

13

As mentioned in the manual:

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.

In the definition of the LIKE operator, it 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   '
Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
6

The trailing spaces are omitted if the column is of type char or varchar; using like 'usa ' resolves the issue

Maxim Krizhanovsky
  • 26,265
  • 5
  • 59
  • 89
  • +1, http://dev.mysql.com/doc/refman/5.0/en/char.html , When CHAR values are retrieved, trailing spaces are removed. – Haim Evgi May 08 '12 at 09:15
1

try it with like or with this

country = 'usa ' AND LENGTH(country) = LENGTH('usa ')
silly
  • 7,789
  • 2
  • 24
  • 37
0

The trailing spaces are omitted if the column is of type char or varchar,

Can read more on it on following link:

http://blogs.agilefaqs.com/2011/06/22/trailing-white-spaces-are-ignored-by-mysql-for-comparison-in-a-select-statement/

Shaikh Farooque
  • 2,620
  • 1
  • 19
  • 33
0

The manual says this:

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 [...] This is true for all MySQL versions, and is not affected by the server SQL mode.

http://dev.mysql.com/doc/refman/5.5/en/char.html

If you need to consider blank space, you basically have to get rid of language-aware sorting. A simple way is to force a binary collation. Please run and compare:

SELECT 'ab'='ab ', BINARY 'ab'='ab '
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Super pedantic observation: the `BINARY` operator here isn't "forcing a binary collation", which would be accomplished by applying a `COLLATE *_bin` clause, but rather is casting its nonbinary operand to a binary string—the effect of so doing is that the comparison is exactly byte-for-byte, which is similar to what a binary collation would do (except that binary collations are still PADSPACE). Readers may find it useful to be aware of this difference, explained in more detail in the manual section [The _bin and binary Collations](https://dev.mysql.com/doc/en/charset-binary-collations.html). – eggyal Jul 26 '16 at 11:19
  • Thank you, these clarifications are never out of place. I wouldn't even discard that I actually had "binary comparison" in mind but just mistyped it—I do that pretty often :) – Álvaro González Jul 26 '16 at 11:40