What are the difference between CHAR BINARY and BINARY type in MySQL

Answer:

For CHAR BINARY, the BINARY keyword does not cause the column to store binary value, instead, it cause the column to use binary collation only. The actual character set used depend on the system.

For BINARY column, the actual data is treated as binary, so no charset and collation rules apply.

Reference: http://dev.mysql.com/doc/refman/5.1/en/binary-varbinary.html

Trailing spaces of char in MySQL

Answer:

MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.

E.g.

1. Create a table

mysql> CREATE TABLE t1 ( 
    c CHAR(10), 
    v VARCHAR(10) 
) ENGINE = INNODB;

2. Insert some data

mysql> INSERT INTO t1 VALUES ('foo', 'bar');

3. Select the data back

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM t1 WHERE c = 'foo   ';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

As you can see, the trailing spaces makes no difference.

Reference: http://dev.mysql.com/doc/refman/5.1/en/char.html