MySQL Binary Types: Store Hashes, UUIDs and Raw Bytes

BINARY(n) and VARBINARY(n) are like CHAR and VARCHAR, but they hold bytes, not characters. There is no character set and no collation: comparison is byte by byte, so it is case-sensitive and exact.

query.sql

SQL

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sha256 BINARY(32) NOT NULL
);
INSERT INTO files (name, sha256) VALUES
    ('report.pdf', UNHEX('9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08'));
SELECT name, HEX(sha256) AS sha256_hex, LENGTH(sha256) AS stored_bytes FROM files;

Output

OUTPUT

+------------+------------------------------------------------------------------+--------------+
| name       | sha256_hex                                                       | stored_bytes |
+------------+------------------------------------------------------------------+--------------+
| report.pdf | 9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08 |           32 |
+------------+------------------------------------------------------------------+--------------+
1 row in set

A SHA-256 hash is 32 bytes. Written as hex text it needs 64 characters. Storing it as BINARY(32) takes half the space, and the index is half as big. Convert with UNHEX() when inserting and HEX() when displaying.

Use cases

Value	Column	Notes
SHA-256 hash	`BINARY(32)`	Or `CHAR(64)` if readability matters more than space
MD5	`BINARY(16)`	MD5 is not safe for security
UUID	`BINARY(16)`	See UUIDs as Keys
IPv4 address	`INT UNSIGNED` with `INET_ATON()`
IPv6 or IPv4	`VARBINARY(16)` with `INET6_ATON()`

query.sql

SQL

1
2
3

CREATE TABLE visits (id INT AUTO_INCREMENT PRIMARY KEY, ip VARBINARY(16) NOT NULL);
INSERT INTO visits (ip) VALUES (INET6_ATON('203.0.113.7')), (INET6_ATON('2001:db8::1'));
SELECT INET6_NTOA(ip) AS address, LENGTH(ip) AS bytes FROM visits;

Output

OUTPUT

+-------------+-------+
| address     | bytes |
+-------------+-------+
| 203.0.113.7 |     4 |
| 2001:db8::1 |    16 |
+-------------+-------+
2 rows in set

Passwords are not stored in these columns as plain text

Store a password hash produced by your application’s password function (bcrypt or Argon2, for example, in PHP password_hash()). It is a text string of about 60 to 100 characters, so VARCHAR(255) is right. Never use MySQL’s MD5(), SHA1() or PASSWORD() for passwords (Security Basics).

Binary comparison

query.sql

SQL

1
2

SELECT 'a' = 'A' AS text_ci,
       CAST('a' AS BINARY) = CAST('A' AS BINARY) AS binary_cmp;

Output

OUTPUT

+---------+------------+
| text_ci | binary_cmp |
+---------+------------+
|       1 |          0 |
+---------+------------+
1 row in set

Forcing binary comparison is a way to make one query case-sensitive. (Older code writes BINARY 'a' as an operator. That form is deprecated since MySQL 8.0.27 and gives a warning, so use CAST(... AS BINARY).) Prefer a case-sensitive collation on the column when that is the rule you want (Character Sets and Collations).

BINARY pads, VARBINARY does not

BINARY(n) pads shorter values with 0x00 bytes, so 'ab' becomes 0x616200.... Use it for fixed-length values only. For a variable length, use VARBINARY.

lightbulb

Binary is for machines

If a human has to read or type the value, a text column is friendlier. Use BINARY where size and speed matter, such as keys, hashes and addresses.