CODEWITHSUNDEEP
mysql
Mikhail
Mikhail

Reputation: 9007

MySQL making whitespace matter

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?

Upvotes: 9

Views: 2243

Answers (3)

Pekka
Pekka

Reputation: 449455

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.

Upvotes: 6

fancyPants
fancyPants

Reputation: 51888

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

Upvotes: 2

van
van

Reputation: 301

You may use LIKE

SELECT "A" LIKE "A ";

will return 0 but

SELECT "A" LIKE "A";

returns 1

Upvotes: 2

Related Questions