Home >Database >Mysql Tutorial >How Can I Store and Retrieve Binary Data in MySQL?

How Can I Store and Retrieve Binary Data in MySQL?

Susan Sarandon
Susan SarandonOriginal
2024-12-04 01:52:10662browse

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn