Compare binary value in MySQL

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.

Related posts:

1. What are the difference between CHAR BINARY and BINARY type in MySQL
2. MySQL binary log files take all of my disk space
3. Auto remove expired binary log in MySQL
4. Create temporary table in MySQL
5. Setting the MySQL’s Binary Log Format