Introduction to MySQL VARBINARY Data Type

VARBINARY is a data type in MySQL database used to store binary data, such as images, sounds, videos, etc. Unlike the BLOB type, VARBINARY type does not specify a maximum length, but adjusts the storage space dynamically based on actual needs.

Syntax

VARBINARY type is used to store binary data, and the syntax is as follows:

VARBINARY[(M)]

where M represents the maximum length of the column. If M is not specified, the VARBINARY type can store data with a maximum length of 65,535 bytes.

Use Cases

VARBINARY data type is suitable for storing binary data, such as images, sounds, videos, etc. It can also be used to store other types of data, such as encrypted data, hash values, identifiers, etc.

Examples

Example 1: Storing Image Data

We can use VARBINARY data type to store image data. Let’s say we have a table named images with two columns id and data, we can create the table with the following command:

CREATE TABLE images (
    id INT PRIMARY KEY,
    data VARBINARY(65535)
);

Next, we can insert some image data into the images table using an INSERT statement. Here is an example:

INSERT INTO images (id, data) VALUES (1, LOAD_FILE('/path/to/image.jpg'));

The above command will load image data from the specified file path and insert it into the images table.

If we want to retrieve image data from the table, we can use the following command:

SELECT data FROM images WHERE id = 1;

Example 2: Storing Encrypted Data

We can use VARBINARY data type to store encrypted data. Let’s say we have a table named users with two columns id and password, we can create the table with the following command:

CREATE TABLE users (
    id INT PRIMARY KEY,
    password VARBINARY(255)
);

Next, we can insert some encrypted password data into the users table using an INSERT statement. Here is an example:

INSERT INTO users (id, password) VALUES (1, UNHEX(SHA2('password', 256)));

The above command will encrypt the string 'password' using the SHA-256 algorithm and insert it into the users table.

If we want to retrieve password data from the table, we can use the following command:

SELECT HEX(password) FROM users WHERE id = 1;

The above command will return the password data in hexadecimal string representation.

Conclusion

VARBINARY data type is a data type used to store variable-length binary data. It can be used to store various types of binary data, including images, sounds, videos, etc. Unlike the BLOB data type, VARBINARY data type can store data with a maximum length of 65,535 bytes and has faster processing speed in memory. When designing a database, VARBINARY data type is a very useful choice for storing variable-length binary data.