Binary vs Varbinary in MySQL: Key Differences and Usage

BINARY and VARBINARY in MySQL are data types used to store binary data. BINARY stores fixed-length binary strings, padding shorter values with zeros, while VARBINARY stores variable-length binary strings without padding.

Quick Comparison

Here is a quick comparison of BINARY and VARBINARY data types in MySQL.

Aspect	BINARY	VARBINARY
Storage Length	Fixed length	Variable length
Padding	Pads with 0x00 if input is shorter	No padding
Maximum Length	0 to 255 bytes	0 to 65,535 bytes (depending on row size and character set)
Use Case	When fixed size binary data is needed	When variable size binary data is needed
Storage Efficiency	May waste space if data is shorter	More efficient for varying data sizes
Comparison	Compared byte-by-byte including padding	Compared byte-by-byte without padding

Key Differences

BINARY is a fixed-length binary string type. If you store data shorter than the defined length, MySQL pads it with zero bytes (0x00) to reach the fixed size. This makes it suitable for data that always has the same size, like hashes or fixed-length keys.

VARBINARY stores variable-length binary data without padding. It only uses as much space as needed plus one or two bytes for length information. This is better for data that varies in size, like images or files.

Both types store raw bytes and are case-sensitive. They differ mainly in storage size behavior and padding. When comparing values, BINARY compares the full fixed length including padding, while VARBINARY compares only the actual stored bytes.

Code Comparison

mysql

CREATE TABLE binary_example (
  fixed_bin BINARY(5)
);

INSERT INTO binary_example (fixed_bin) VALUES ('abc');

SELECT HEX(fixed_bin) AS hex_value FROM binary_example;

Output

6162630000

Varbinary Equivalent

mysql

CREATE TABLE varbinary_example (
  var_bin VARBINARY(5)
);

INSERT INTO varbinary_example (var_bin) VALUES ('abc');

SELECT HEX(var_bin) AS hex_value FROM varbinary_example;

Output

616263

When to Use Which

Choose BINARY when you need to store binary data that always has the same length, such as fixed-size hashes or binary flags, and you want consistent storage size.

Choose VARBINARY when your binary data size varies, like storing images, files, or variable-length binary tokens, to save space and avoid unnecessary padding.

Key Takeaways

BINARY stores fixed-length binary data with zero padding.

VARBINARY stores variable-length binary data without padding.

Use BINARY for fixed-size data and VARBINARY for variable-size data.

VARBINARY is generally more storage efficient for varying data sizes.

Comparison of BINARY includes padding bytes; VARBINARY compares only actual data.