MySQL trims value in WHERE

Question

I didn't find a similar question maybe because I don't find the correct words (English is not my first language)

Problem

I have a varchar value that has a blank space at the end: "opt-193-381-markets " and when I do a SELECT with the value without the blank space, it returns the same result. It's like MySQL is trimming the value in the WHERE

Table definition:

mysql> desc message;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| FileId     | int(11)      | NO   | PRI | 0       |       |
| MessageKey | varchar(100) | NO   | PRI |         |       |
| NumericKey | tinyint(1)   | NO   |     | 0       |       |
| IsObsolete | tinyint(4)   | YES  | MUL | NULL    |       |
| notes      | text         | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Examples:

mysql> SELECT * FROM message WHERE MessageKey = "opt-193-381-markets";
+--------+----------------------+------------+------------+-------+
| FileId | MessageKey           | NumericKey | IsObsolete | notes |
+--------+----------------------+------------+------------+-------+
|    304 | opt-193-381-markets  |          0 |          1 | NULL  |
|    337 | opt-193-381-markets  |          0 |          1 | NULL  |
+--------+----------------------+------------+------------+-------+

Notice that the MessageKey has the value with the blank space at the end and I just asked for the value without that blank space.

mysql> SELECT * FROM message WHERE MessageKey = "opt-193-381-markets ";
+--------+----------------------+------------+------------+-------+
| FileId | MessageKey           | NumericKey | IsObsolete | notes |
+--------+----------------------+------------+------------+-------+
|    304 | opt-193-381-markets  |          0 |          1 | NULL  |
|    337 | opt-193-381-markets  |          0 |          1 | NULL  |
+--------+----------------------+------------+------------+-------+

Adding one more space

mysql> SELECT * FROM message WHERE MessageKey = "opt-193-381-markets  ";
+--------+----------------------+------------+------------+-------+
| FileId | MessageKey           | NumericKey | IsObsolete | notes |
+--------+----------------------+------------+------------+-------+
|    304 | opt-193-381-markets  |          0 |          1 | NULL  |
|    337 | opt-193-381-markets  |          0 |          1 | NULL  |
+--------+----------------------+------------+------------+-------+

Escaping the spaces

mysql> SELECT * FROM message WHERE MessageKey = "opt-193-381-markets\ \ ";
+--------+----------------------+------------+------------+-------+
| FileId | MessageKey           | NumericKey | IsObsolete | notes |
+--------+----------------------+------------+------------+-------+
|    304 | opt-193-381-markets  |          0 |          1 | NULL  |
|    337 | opt-193-381-markets  |          0 |          1 | NULL  |
+--------+----------------------+------------+------------+-------+

I've also tried with single quotes 'opt-193-381-markets'

Question

Why I cannot search for the exact value of MessageKey?

Thanks

try to use single qoutes instead of double quotes `'opt-193-381-markets '` — Jorge Campos, Nov 14 '13 at 14:11
and what version of MySQL is this? There are a bunch of articles related to this: http://stackoverflow.com/questions/12542889/mysql-5-5-losing-trailing-spaces-in-query and http://dev.mysql.com/doc/refman/5.0/en/char.html — AgRizzo, Nov 14 '13 at 14:14
see http://stackoverflow.com/questions/14703349/mysql-disable-auto-trim — Ramesh, Nov 14 '13 at 14:15

score 4 · Accepted Answer · edited Nov 14 '13 at 14:21

4

Use the binary comparison operator .

SELECT * FROM message WHERE BINARY(MessageKey) = 'opt-193-381-markets';

edited Nov 14 '13 at 14:21

Pablo Díaz Ogni

1,033
8
12

answered Nov 14 '13 at 14:13

BuysDB

143
8

Thank you!, This have worked. In minutes I'll give you the check – Pablo Díaz Ogni Nov 14 '13 at 14:17

MySQL trims value in WHERE

Problem

Question

1 Answers1