Summary: in this tutorial, you will learn how to use the MySQL VARBINARY
data 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 VARBINARY
column, 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.