CODEWITHSUNDEEP
mysqlshellblobbulkinsert
Yuriy Tigiev
Yuriy Tigiev

Reputation: 187

How to use shell script for Bulk Insert/Update Images in a MySql DB

I have directory with images (*.jpg) files I have mysql db and table items(id, description, images) with data. I would like upload images into table items. Each file name of image equals of each id from table item. File should be upload if images is null, or images content not equal file content with same id.

How to use shell script for Bulk Insert/Update Images in a MySql DB ? Could you someone provide sample code of shell script?

Upvotes: 1

Views: 1349

Answers (1)

Sasha Pachev
Sasha Pachev

Reputation: 5336

You can use the LOAD_FILE() function (http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_load-file) in your INSERT statement, or even with LOAD DATA INFILE if you use user variables.

Example:

CREATE TABLE t1 (id int not null primary key, img longblob);
CREATE TABLE t2 (id int not null primary key);
INSERT INTO t2 VALUES (1),(2),(3);
INSERT INTO t1 
SELECT id,LOAD_FILE(CONCAT('/var/www/images/',t2.id,'.jpg')) FROM t2;

The file will need to be world-readable, the user will need to have the FILE privilege, and the directory might be restricted by the server setting secure_file_priv. You also need to make sure max_allowed_packed is bigger than the size of your largest file.

To load from a directory, you can do something like this (tested code, full example including table creation for clarity):

#! /bin/bash

dir=/home/sasha
ext=jpg
chmod a+r $dir/*.$ext
mysql -uroot test <<eot
drop table if exists t1;
create table t1 (name varchar(128), data longblob); 
eot
ls -1 $dir/*.$ext | perl -e 'print "insert into t1(name,data) values ".join(",",map {chop;$f="\"".$_."\""; "($f,load_file($f))"} <>);' | mysql -uroot test

The above code loads all files with the extension jpg in /home/sasha into table test.t1.

Edit: To address your concern about using Perl. You can assume that Perl is installed on the Ubuntu system where you have MySQL server as it is one of the dependencies for the mysql-server-5.5 (and likely newer versions as well). You can verify this with the command below:

dpkg-query -p mysql-server-5.5 | grep Depends

If that is not good enough, you could use Awk instead for the parsing, but I do not see a point.

Upvotes: 2

Related Questions