Equals and LIKE operators treat trailing spaces differently in MySQL
Published on - Updated on
While working on some queries a couple of days ago I discovered that MySQL treats trailing spaces differently when the
LIKE operator is used compared to when the = operator is used.
Apparently, the standard = operator ignores trailing spaces in collations where the pad attribute has been set
to PAD SPACE, which applies to most collations in MySQL (except for some Unicode ones). In comparison, the LIKE
operator does not ignore these spaces, as it performs a byte-by-byte comparison of both Strings.
=
| String A | String B | Equal? |
|---|---|---|
'Some text' |
'Some text' |
Yes |
'Some text ' |
'Some text' |
Yes |
LIKE
| String A | String B | Equal? |
|---|---|---|
'Some text' |
'Some text' |
Yes |
'Some text ' |
'Some text' |
No |