What's the difference between "VARCHAR BINARY" and "VARBINARY" in MySQL?

Question

I've created the following test table:

CREATE TABLE t (
   a VARCHAR(32) BINARY,
   b VARBINARY(32)
);

INSERT INTO t (a, b) VALUES ( 'test    ', 'test    ');
INSERT INTO t (a, b) VALUES ( 'test    \0', 'test    \0');

But this query indicated no difference between the two types:

SELECT a, LENGTH(a), HEX(a), b, LENGTH(b), HEX(b) FROM t;

a          LENGTH(a)  HEX(a)              b          LENGTH(b)  HEX(b)              
---------  ---------  ------------------  ---------  ---------  --------------------
test               8  7465737420202020    test               8  7465737420202020    
test               9  746573742020202000  test               9  746573742020202000  

Alms

Read the first paragraph of dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html – Panslavism

@Barmer I think you'll have to keep reading beyond the first paragraph to figure out the difference. If this isn't a duplicate question, I think we need a good answer to this over here on stackoverflow. You won't see differences with the query in the question, but to see how it matters, check this page of the manual: dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html There are some examples there. – Arndt

Answer 1

Here are the difference I was able to find reading the documentation :

VARCHAR BINARY

• The BINARY attribute cause the binary collation for the column character set to be used, and the column itself contains nonbinary character strings rather than binary byte strings.
• When BINARY values are stored, they are right-padded with the pad value to the specified length.
• You should consider the preceding padding and stripping characteristics carefully if you plan to use the BINARY data type for storing binary data and you require that the value retrieved be exactly the same as the value stored.

---

VARBINARY

• If strict SQL mode is not enabled and you try to assign a value that exceeds the column's maximum length, the value is truncated to fit and a warning is generated.
• There is no padding on insert, and no bytes are stripped on select. All bytes are significant in comparisons.
• Utilisation is preferable when the value retrieved must be the same as the value specified for storage with no padding.

Edlyn

Thank you for answering it. But the padding processing only exists in CHAR and BINARY, not in VARCHAR or VARBINARY. I've just found the answer in dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html. In summary, charset convertion and lettercase convertion are the main differences. – Alms

@Alms That is true, in the VARBINARY part, I specify that there is no padding. I only mention the padding for type VARCHAR + BINARY. – Ara

The first paragraph should be titled BINARY and not "VARCHAR BINARY" as it is referring to the BINARY data type, confusing already confused readers even more. – Saltatory

Answer 2

As the MySQL manual page on String Data Type Syntax explains, VARBINARY is equivalent to VARCHAR CHARACTER SET binary, while VARCHAR BINARY is equivalent to VARCHAR CHARACTER SET latin1 COLLATE latin1_bin (or some other non-binary character set with the corresponding binary collation; it depends on table settings):

Specifying the CHARACTER SET binary attribute for a character string data type causes the column to be created as the corresponding binary string data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB.

The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary (_bin) collation of the column character set (or of the table default character set if no column character set is specified).

So, VARBINARY stores bytes; VARCHAR BINARY stores character codes but sorts them like bytes (almost - see below).

What this means in practice is explained on the manual page The binary Collation Compared to _bin Collations:

• VARBINARY sorts by comparing byte by byte; VARCHAR BINARY compares the byte groups that correspond to characters (not much of a difference for most encodings)
• VARCHAR BINARY performs a character set conversion when assigning value from another column with a different encoding, or when the value is inserted/updated by a client with a different encoding; VARBINARY just takes the value as a raw byte string.
• Case conversion in SQL (ie. the LOWER / UPPER functions) has no effect on VARBINARY (bytes have no case).
• Trailing spaces will be usually ignored in VARCHAR BINARY comparisons (that is, 'x ' = 'x' will be true).

Mop