Is there a difference between Binary(16) and VarBinary(16) in MySQL

Question

Nhibernate seems to have a problem with varbinary(16) when tring to represent Guids. I would have thought that varbinary(16) and binary(16) equate to the same thing.

Answer 1

binary(16) is fixed length. It always uses 16 bytes of storage for each row, padding any extra bytes with 0x00 or 0x20 (depending on the version of MySQL) and stripping them on SELECT. varbinary uses a variable amount of space--whatever is needed to store the data on that row.

If your data are always exactly 16 bytes, there is no difference. Realistically, there probably isn't a difference anyway with a column that small.

Answer 2

MySQL Reference Manual: The BINARY and VARBINARY Types

"When BINARY values are stored, they are right-padded with the pad value to the specified length...For VARBINARY, there is no padding on insert and no bytes are stripped on select."

Answer 3

To expand on this, the difference between VAR_TYPE_ and TYPE (where TYPE is something like CHAR, INT, BINARY, etc.), is that the non-VAR "version" will get padded to the full length that you specify (if necessary). VAR- won't get padded, which is what makes it suitable for storing text comments and the like.