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?
The symbol <> in MySQL is same as not equal to operator (!=). Both gives the result in boolean or tinyint(1). If the condition becomes true, then the result will be 1 otherwise 0. Case 1 − Using !=
In MySQL, you can use the <> or != operators to test for inequality in a query. For example, we could test for inequality using the <> operator, as follows: SELECT * FROM contacts WHERE last_name <> 'Johnson';
Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the Transact-SQL comparison operators.
As mentioned in the manual:
All MySQL collations are of type
PADSPACE
. This means that allCHAR
andVARCHAR
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
orVARCHAR
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 '
The trailing spaces are omitted if the column is of type char
or varchar
; using like 'usa '
resolves the issue
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With