How do you insert BLOB and CLOB files into MySQL?

Inserting BLOB and CLOB Files into MySQL

Storing non-textual data, such as images and documents, in a database can be a valuable asset to any software application. MySQL provides mechanisms to efficiently handle this type of data through BLOB (Binary Large OBject) and CLOB (Character Large OBject) datatypes.

There are two primary approaches to insert BLOB and CLOB files into MySQL tables:

1. Using the LOAD_FILE Function:

This method allows you to directly load a file into a BLOB or CLOB field using the LOAD_FILE() function. Here's an example:

INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));

This command loads the contents of the data.png file into the BLOB field with ID 1 in the table1 table.

2. Inserting as Hex String:

Alternatively, you can convert the file into a hexadecimal string and insert it into the database. This method is more portable, as it doesn't require the database server to have access to the original file. To do this:

• Convert the file to a hexadecimal string using a tool like xxd.
• Insert the string into the BLOB or CLOB field using the following syntax:

INSERT INTO table1 VALUES
  (1, x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082');

This method inserts the contents of the same data.png file as a hexadecimal string into the BLOB field.

Which method you choose depends on your specific requirements and preferences. The LOAD_FILE() function offers simplicity, while inserting as a hex string provides greater portability. Both methods effectively store non-textual data in MySQL tables for subsequent retrieval and manipulation.

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