Home > Database > Mysql Tutorial > How do I insert and retrieve BLOB and CLOB files in MySQL?

How do I insert and retrieve BLOB and CLOB files in MySQL?

Linda Hamilton
Release: 2024-11-27 17:42:14
Original
988 people have browsed it

How do I insert and retrieve BLOB and CLOB files in MySQL?

Inserting BLOB and CLOB Files into MySQL

In your software, you aim to store various file types, including images, documents, presentations, and PDFs. To effectively manage this storage, it's necessary to understand how to handle BLOB (Binary Large Objects) and CLOB (Character Large Objects) files in MySQL.

BLOB and CLOB Types

BLOB and CLOB are data types in MySQL designed to store binary and character data that exceed the maximum length of the standard VARCHAR or TEXT data types. They allow you to store large amounts of unstructured or semi-structured data.

Inserting BLOB Files

To insert a BLOB file, you can use either the LOAD_FILE function or by converting the file contents to a hexadecimal string.

  • LOAD_FILE
INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));
Copy after login

This method loads the file contents from the specified path directly into the BLOB column.

  • Hexadecimal String
INSERT INTO table1 VALUES 
  (1, x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA86400000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082');
Copy after login

Here, the file contents are converted into a hexadecimal string and stored in the BLOB column.

Inserting CLOB Files

For CLOB data, you can use the following method to insert character data of considerable length:

INSERT INTO table1 VALUES (1, 'Your text data here');
Copy after login

Ensure that the column is defined as TEXT or CLOB to accommodate the large text content.

By implementing these methods, you can effectively store and retrieve BLOB and CLOB files in MySQL, enabling you to manage complex data types within your software application.

The above is the detailed content of How do I insert and retrieve BLOB and CLOB files in MySQL?. For more information, please follow other related articles on the PHP Chinese website!

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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template