MySQL making whitespace matter

Question

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?

A binary comparison will prevent removal of trailing spaces: `SELECT BINARY 'a ' = BINARY 'a';` — Michael Berkowski, Jul 02 '12 at 13:55

score 6 · Answer 1 · edited Jun 20 '20 at 09:12

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.

`STRCMP` seems to act identical to `=`. While `LIKE` also pays attention to the capitalization. Thanks! — Mikhail, Jul 02 '12 at 14:09
@Mikhail ahh, so neither works for you? That sucks. Then I guess binary comparison is the only way to go — Pekka, Jul 02 '12 at 14:16

score 2 · Answer 2 · answered Jul 02 '12 at 13:56

2

You may use LIKE

SELECT "A" LIKE "A ";

will return 0 but

SELECT "A" LIKE "A";

returns 1

answered Jul 02 '12 at 13:56

van

301
2
9

fancyPants · Answer 3 · 2012-07-02T13:57:45.100

2

Binary comparison is the magic word.

Binary Comparison in MySQL Manual

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

edited Jul 02 '12 at 13:57

answered Jul 02 '12 at 13:56

fancyPants

50,732
33
89
96

+1, but note that this may change other behaviour as well (like the comparison of Umlauts) – Pekka Jul 02 '12 at 14:03

MySQL making whitespace matter

3 Answers3