VARBINARY 

– learn how to store variable-length byte data. (1)

Summary: in this tutorial, you will learn how to use the MySQL VARBINARYdata type to store variable-length binary data.

Introduction to MySQL VARBINARY data type

The VARBINARY data type is used to store variable-length binary data. It is similar to the BINARY data type but allows you to store binary data of variable length.

The following shows how to define a VARBINARY column in a table:

column_name VARBINARY(max_length)Code language: SQL (Structured Query Language) (sql)

In this syntax, we define the column_name with the VARBINARY data type that can store up to max_length bytes. The maximum value for the max_length is 65,535 bytes, which is equivalent to 64KB.

In practice, you often use the VARBINARY data type for storing variable binary data such as small images, audio files, and other non-textual data.

Unlike the BINARY data type, When you insert data into a VARBINARYcolumn, MySQL does not pad zero bytes (0x00) if the length of the data is not equal to the max_length of the column. Additionally, MySQL will not strip any bytes when you retrieve data from a VARBINARY column.

If you sort VARBINARY data, MySQL treats zero bytes (0x00) and space differently in sorting operations such as ORDER BY and DISTINCT. It places the zero bytes (0x00) before the space.

When you insert data whose length exceeds the max_length, MySQL drops extra bytes and issues a warning if SQL strict mode is not enabled and an error if the SQL strict mode is enabled.

MySQL VARBINARY data type example

We’ll take an example of using the VARBINARY data type to define a column that stores data.

First, create a new table called varbinary_demo:

CREATE TABLE varbinary_demo(
   id INT AUTO_INCREMENT PRIMARY KEY,
   data VARBINARY(256)
);Code language: SQL (Structured Query Language) (sql)

The table varbinary_demo has two columns:

• id: Auto-incremented primary key column.
• data: VARBINARY data column.

Second, insert a new row into the varbinary_demo table:

INSERT INTO varbinary_demo(data)
VALUES('Hello');Code language: SQL (Structured Query Language) (sql)

Third, select data from the varbinary_demo table:

SELECT * FROM varbinary_demo;Code language: SQL (Structured Query Language) (sql)

Output:

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

Summary

• Use MySQL VARBINARY data type to define a column that can store variable binary data.