MySQL making whitespace matter
mysql
Solution
According to the manual, one quick fix is to use LIKE:
Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:
...
In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator ...
as long as you don't use any wildcards, this should be identical to `=`. This Stack Overflow question seems to support the assumption: Equals(=) vs. LIKE
The manual doesn't state whether `STRCMP()` is stricter than `=` in terms of whitespace, and I can't try it out right now - that might be worth taking a look at, too, as it makes it clearer why `=` is not used.
Binary comparison as suggested by tombom is also an option, but will have other side-effects (like the stricter comparison of Umlauts, eg. `A` and `Ä` will be different) which you may or may not want. More info on the effects of using a binary comparison in this question.
Problem
Apparently a very rare issue, but IMO extremely annoying and WRONG: Trailing whitespace in MySQL aren't used in comparison: ``` mysql> SELECT "A" = "A "; +------------+ | "A" = "A " | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) ``` This is especially problematic in the following scenario: ``` mysql> SELECT COUNT(*) FROM eq WHERE name != TRIM(name); +------------+ | COUNT(*) | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> UPDATE eq SET name=TRIM(name); Query OK, 866 row affected (0.01 sec) Rows matched: 650907 Changed: 866 Warnings: 0 ``` Is there a way to configure MySQL to treat whitespace properly?