Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

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.

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>