Similarities and differences of varbinary, binary, char, varchar in MySQL

This is a creation in Article, where the information may have evolved or changed.

binary and varbinary types and char are similar to varchar types, except that they store binary data, that is, they contain byte streams rather than characters, they have binary character sets and sequences, their comparisons, and the sort is based on the values of bytes.

Binary and varbinary have the same maximum length and char as the varchar, except that they define the byte length , and char and varchar correspond to the character length .

Handling of trailing spaces when stored and removed

char (N) is used to store non-binary strings, when inserted, for less than N characters will automatically in the trailing space, query, the trailing space will be discarded

Vachar (N) is used to store non-binary strings, when inserted, for less than N characters of non-filled spaces, query, the trailing space will not be discarded

Binary (n) stores a binary string, inserted in, less than N bytes will automatically be added at the end of 0x00, when taken out, all the bytes are retained, return the length of the defined length of bytes, at the time of comparison, all the bytes are valid, and 0x00<space ( Space corresponds to 0x20)

varbinary in the insert will not go to fill the 0x00 byte, the query will not discard any bytes, when compared, all the bytes are valid, and 0x00<space (space corresponds to the 0x20)

When size comparison

In the comparison of char and varchar characters, the case is ignored with the last space, such as:

Mysql> Select ' A ' = ' a ', ' a ' = ' a ', ' a ' = ' a ';

+----------+---------+----------+

| ' A ' = ' a ' | ' A ' = ' a ' | ' A ' = ' a ' |

+----------+---------+----------+

|       1 |        1 | 1 |

+----------+---------+----------+

1 row in Set (0.00 sec)

In binary and varbinary byte comparisons, all information is not ignored, such as:

mysql> CREATE TABLE T (c BINARY (3));

Query OK, 0 rows affected (0.01 sec)

Mysql> INSERT into t SET C = ' a ';

Query OK, 1 row affected (0.01 sec)

Mysql> SELECT HEX (c), C = ' a ', c = ' a\0\0 ' from t;

+--------+---------+-------------+

| HEX (c) | c = ' a ' | c = ' A\0\0 ' |

+--------+---------+-------------+

|       610000 |           0 | 1 |

+--------+---------+-------------+

1 row in Set (0.08 sec)