Jul 192010
Compare binary value in MySQL
Answer:
Assume you have the following table.
mysql> CREATE TABLE t (
c BINARY(3)
) ENGINE = INNODB;
And you insert some sample value...
mysql> INSERT INTO t VALUES ('fo');
mysql> INSERT INTO t VALUES ('foo');
Finally you select it
mysql> SELECT * FROM t WHERE c = 'foo';
+------+
| c |
+------+
| foo |
+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t WHERE c = 'fo';
Empty set (0.00 sec)
Why you cannot find the result in the second query? Because the column is BINARY, the trailing space(s) is not ignored like this.
You need to query like...
mysql> SELECT * FROM WHERE c = 'fo\0';
+------+
| c |
+------+
| fo |
+------+
1 row in set (0.00 sec)
As you can see, you need to append the the 0x00 (the zero byte). at the end of the character.