How Can I Store and Retrieve Binary Data in MySQL?

Storing Binary Data in MySQL

Storing binary data in MySQL can be achieved using the BLOB (Binary Large Object) data type. A BLOB column is specifically designed to handle binary data of various sizes.

Using the BLOB Data Type

To define a BLOB column in a table, use the following syntax:

CREATE TABLE table_name (
  binary_data BLOB
);

You can then insert binary data into the column using the following syntax:

INSERT INTO table_name (binary_data) VALUES (BINARY 'your_binary_data');

Example

Consider the following example:

CREATE TABLE photos (
  id INT NOT NULL AUTO_INCREMENT,
  photo_data BLOB,
  PRIMARY KEY (id)
);

INSERT INTO photos (photo_data) VALUES (BINARY 'image binary data');

Additional Notes

• There are different BLOB data types available, including TINYBLOB (up to 255 bytes), BLOB (up to 65,535 bytes), MEDIUMBLOB (up to 16,777,215 bytes), and LONGBLOB (up to 4,294,967,295 bytes).
• BLOB columns are stored off-row by default, improving performance for large data sets.
• BLOB data can be retrieved using the LOAD_FILE() or GET_LOCK() functions.

The above is the detailed content of How Can I Store and Retrieve Binary Data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!