How to insert BLOB and CLOB files in MySQL?

Question

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.

Devart · Answer 1 · 2014-06-13T12:45:30.457

43

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');

edited Jun 13 '14 at 12:45

answered May 24 '12 at 06:56

Devart

119,203
23
166
186

3

A 3rd option is close to the 2nd one and reads as `INSERT INTO table1 VALUES (1, 0x89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082);`. – glglgl May 24 '12 at 07:59
The following is not working: INSERT INTO table1 VALUES(1, LOAD_FILE(data.png)); – user1411472 May 27 '12 at 09:51
1

Check if the file exists, check the path, check the file size, is it more or less them `max_allowed_packet`. Do you have FILE privilege? What type of BLOB field, is it enough to store the file? – Devart May 28 '12 at 06:30
The output shows that a value has been inserted into the table but while retrieving it shows NULL – user1411472 May 28 '12 at 15:31
1

I would also be very thankful if anyone can help me with the concept of downloading files from database. I mean for example if i upload some .pdf or .jpg files then how can a client download it? – user1411472 May 28 '12 at 15:46
insert into pic values(LOAD_FILE("home/pkr/Desktop/DSC09566.JPG")); Query OK, 1 row affected (0.05 sec) – user1411472 May 28 '12 at 15:49
Output-select * from pic; +------+ | Pic | +------+ | NULL | | NULL | | NULL | | NULL | | NULL | +------+ 5 rows in set (0.00 sec) – user1411472 May 28 '12 at 15:49
The LOAD_FILE function is working good. Maybe you have some warnings, try to see them - SHOW WARNINGS. – Devart May 29 '12 at 11:09
Doesn't the filename need to be in quotes? – Lightness Races in Orbit May 27 '14 at 11:46
Yes, it should be quoted. – Devart Jun 13 '14 at 12:45

score 16 · Answer 2 · edited May 23 '17 at 11:54

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:

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

The `secure_file_priv` hint saved my day. Use `mysql> SELECT @@secure_file_priv;` to see the actual value — Frank Neblung, Feb 02 '18 at 14:23

score 8 · Answer 3 · edited Feb 06 '21 at 15:36

8

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".

edited Feb 06 '21 at 15:36

Kawu

13,647
34
123
195

answered Apr 29 '15 at 14:30

Rodd Johnson

99
1
1

1

THIS! Exactly what I needed but didnt know exist. Blame me. – Panini Luncher Oct 28 '19 at 20:52
Bad solution, completely relying on third party tool – Enerccio Aug 22 '22 at 12:18

score 2 · Answer 4 · edited Jun 02 '13 at 00:15

2

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)

edited Jun 02 '13 at 00:15

dreamcrash

47,137
25
94
117

answered Jun 01 '13 at 23:57

Chris Hobbs

21
1

score 2 · Answer 5 · answered Jul 19 '19 at 13:05

for those People who are getting "Column 'image' cannot be null" error while saving Blob through query :-

Open your MySql Command Line Client and login with root user and type

mysql> SHOW VARIABLES LIKE "secure_file_priv";

this will show you the secure path used by MySql to access the files. something like

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

you can either paste files inside this folder or change the "secure_file_priv" variable value to "empty string" so that it can read file from anywhere.

score 0 · Answer 6 · answered Jun 01 '20 at 15:47

If you are using mysql workbench, just right click on the field (cell) and select 'load value from file' option and then browse to the file and click open and then click on apply. It will automatically generate query like this

UPDATE `dbname`.`tablename` SET `columnname` = ? WHERE (`row` = '1');

How to insert BLOB and CLOB files in MySQL?

6 Answers6

Linked

Related