如何使用shell脚本在MySql DB中批量插入/更新图像

Question

我有带有图像(*.jpg)文件的目录，我有mysql和带有数据的表项(id、description、images)。我想上传图片到表格项目。图像的每个文件名等于表项中的每个id。如果图像为null，或者图像内容与相同id的文件内容不相等，则应上载文件。

如何使用shell脚本在MySql DB中批量插入/更新映像？您能提供shell脚本的示例代码吗？

Answer 1

您可以在LOAD_FILE()语句中使用INSERT函数(load-file)，如果使用用户变量，甚至可以使用LOAD DATA INFILE。

示例：

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;

该文件需要具有世界可读性，用户需要拥有FILE特权，并且目录可能受到服务器设置secure_file_priv的限制。您还需要确保max_allowed_packed大于最大文件的大小。

要从目录加载，您可以执行类似的操作(经过测试的代码，完整的示例，包括为清晰起见创建表)：

#! /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

上面的代码将所有扩展名为jpg的/home/sasha文件加载到表test.t1中。

编辑:解决您对使用Perl的关注。您可以假设Perl安装在有MySQL服务器的Ubuntu系统上，因为它是mysql-server-5.5的依赖项之一(也可能是较新的版本)。您可以使用下面的命令验证这一点：

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

如果这还不够好，您可以使用Awk来进行解析，但我看不出有什么意义。