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

How Can I Effectively Store and Retrieve Binary Data in MySQL?

DDD
Release: 2024-12-14 06:17:16
Original
772 people have browsed it

How Can I Effectively Store and Retrieve Binary Data in MySQL?

Working with Binary Data in MySQL

Storing binary data in MySQL is a common requirement in various applications. The specific data type to use for this purpose is BLOB (Binary Large Object).

BLOB Data Type

BLOB is a special column type designed to handle binary data. It can store large volumes of binary data, including images, videos, documents, or any other type of non-textual content.

Creating a BLOB Column

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

CREATE TABLE table_name (
  column_name BLOB NOT NULL
);
Copy after login

The NOT NULL constraint ensures that the column cannot contain empty values.

Inserting Binary Data into BLOB

You can insert binary data into a BLOB column using the following methods:

  • PHP: Use the mysqli_stmt_bind_param() function with the MYSQLI_TYPE_BLOB datatype.
  • Python: Use the MySQLdb module with the set_binary() method.
  • SQL: Use the INSERT statement with the LOAD_FILE() function.

Example

The following PHP code demonstrates how to insert a binary image into a BLOB column:

$con = mysqli_connect('host', 'username', 'password', 'dbname');
$stmt = mysqli_prepare($con, "INSERT INTO table_name (column_name) VALUES (?)");
$image = file_get_contents('image.png');
mysqli_stmt_bind_param($stmt, "b", $image);
mysqli_stmt_execute($stmt);
Copy after login

Retrieving Binary Data from BLOB

To retrieve binary data from a BLOB column, use the same methods as for inserting. Be sure to handle the data as binary, as it is not stored as text in MySQL.

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

source:php.cn
Statement of this Website
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
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template