MySQL BINARY Data Type

Summary: in this tutorial, you will learn about MySQL BINARY data type and how to use it to store fixed-length binary data.

Introduction to MySQL BINARY data type

The BINARY data type is used to store fixed-length binary data. For example, you can use BINARY data type for columns that store hashes and checksums such as SHA-256 because these values have a fixed length.

To declare a column that uses the BINARY data type, you specify the maximum length of binary data it can hold:

column_name BINARY(size);Code language: SQL (Structured Query Language) (sql)

In this syntax, the size specifies the number of bytes that the column name can store.

Right-padding with 0x00 (zero bytes)

When you insert a binary value whose length is less than the specified length for the BINARY column, MySQL will automatically pad the value with zero bytes (0x00) on the right side to reach the defined size length.

No trailing byte removal for retrievals

When you retrieve a value from the BINARY column, MySQL does not remove any trailing zero bytes that were padded during insertion.

In other words, if you inserted a binary value and it was right-padded with zero bytes, those zero bytes will be present when you retrieve the data.

All bytes are significant in comparisons

When comparing BINARY value in the WHERE clause, ORDER clause, or DISTINCT, MySQL considers all bytes.

This means that even trailing zero bytes can affect the comparison results, and MySQL will not consider two BINARY values are equal unless all of their bytes match.

0x00 and space differ in comparisons

MySQL treats the zero bytes (0x00) and the space character (0x20) differently in comparisons.

If you have a BINARY column with values that contain zero bytes and space characters, MySQL will not consider these values to be equal.

Additionally, MySQL places null bytes before space characters in sorting operations (e.g., ORDER BY).

MySQL BINARY data type example

We’ll take an example of using the BINARY data type to store SHA-256 hashes.

First, create a table to store the SHA-256 hashes:

CREATE TABLE binary_demo(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data BINARY(32) -- 32 bytes for SHA-256
);Code language: SQL (Structured Query Language) (sql)

The binary_demo has two columns:

• id: An auto-incremented primary key column.
• data: A BINARY column with a fixed size of 32 bytes to store SHA-256 hashes.

Second, insert a SHA-256 hash into the table:

INSERT INTO binary_demo(data) 
VALUES (UNHEX(SHA2('Hello', 256)));Code language: SQL (Structured Query Language) (sql)

The SHA2('Hello', 256) computes the SHA-256 hash of the string ‘Hello’.

The UNHEX() function converts the hexadecimal representation of the SHA-256 hash into binary data before inserting it into the BINARY column.

Third, retrieve data from the BINARY column and convert the data back to its hexadecimal using the HEX() function:

SELECT HEX(data) 
FROM binary_demo WHERE id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

+----+--------------------------------------------------------------------+
| id | data                                                               |
+----+--------------------------------------------------------------------+
|  1 | 0x185F8DB32271FE25F561A6FC938B2E264306EC304EDA518007D1764826381969 |
+----+--------------------------------------------------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use BINARY data type to store fixed-length binary data such as hashes or UUID.