How can binary data be stored in MySQL?

1 year ago
Ava Mitchell
1 minute

You can store binary data in MySQL using the BLOB (Binary Large Object) data type. BLOB is a binary data type that can store large binary data such as images, audio, videos, and other files.

When creating a table, you can use the BLOB data type to define columns for storing binary data, such as:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    binary_data BLOB
);

When inserting data, binary data can be inputted into a BLOB column in hexadecimal string format or directly as binary data. For example:

INSERT INTO my_table (id, binary_data) VALUES (1, 0x48656c6c6f20576f726c64);

or

INSERT INTO my_table (id, binary_data) VALUES (2, LOAD_FILE('path_to_file'));

The LOAD_FILE() function can be used to read a binary file and insert it into a BLOB column.

When querying data, you can use the SELECT statement to retrieve BLOB data and convert binary data to hexadecimal strings using the HEX() function. For example:

SELECT id, HEX(binary_data) FROM my_table;

This way, binary data can be stored and retrieved in MySQL.

bannerAds