How to insert BLOB and CLOB files in MySQL?

问题:

I want to store images and .docx/.doc, .pptx/.ppt, .pdf files using the front end of my software. I don't understand how to implement this and how to insert the BLOB and CLOB files into the table. Please help.

I am using Kubuntu 11.04, MySQL5, Qt 4.7.3.

回答1:

Two ways:

1 - Use a LOAD_FILE function -

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

2 - Insert file as hex string, e.g. -

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

回答2:

INSERT INTO MY_TABLE(id, blob_col) VALUES(1, LOAD_FILE('/full/path/to/file/myfile.png')

LOAD_FILE has many conditions attached to it. From the MySQL documentation:

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

Also, there there are bugs with LOAD_FILE in Linux. See http://bugs.mysql.com/bug.php?id=38403 for the bug, and MySQL LOAD_FILE returning NULL for workarounds. On Ubuntu 12.04, MySQL 5.5.32, this works for me:

1. Copy file to /tmp
2. Change ownership to mysql user chown mysql:mysql /tmp/yourfile
3. Log into mysql as mysql root user so you are sure you have FILE privilege
4. Run your insert statement

回答3:

Or you could merely use the MySQL Workbench, select the rows, last rows, insert a row without the blob, then just right click and select "Load Value From File".

回答4:

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

won't work but

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

should (assuming data.png exists in the local directory)