我想从目录中的文件构建 mysql 插入脚本
我有别名v ='ls -l --time-style=+"%Y-%m-%d %H:%M:%S"'
该目录有大约 1000 个文件,我想提取这些文件*.afl
并将它们存储到一个表中,并使用由 awk 命令构建的脚本:
ls -1 *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(<FILE CREATION DATE>,\x27%Y-%m-%d\x27),,\x27\x27,\x27)" "File Name: "$0"\n"ENVIRON["here"]"\n"source"\x27"}'
创建这一行:
INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(<FILE CREATION DATE>,'%Y-%m-%d'),'','File Name: 2011 02 21 drdttl.afl
C:\Users\athena\Downloads\Project_1\00.MBT
Source: Parallel action
我被困在这里,因为我希望包含文件时间戳,所以这意味着我不能使用 ls -1。
我的知识深度已经见底了。
精明的用户有什么妙招吗?
我用过的答案
stat -c '%y %n' *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO action_diary (entry_date, entry_description,entry_details) VALUES (STR_TO_DATE(\x27"substr($0,1,18)"\x27,\x27%Y-%m-%d\x27),\x27\x27,\x27File Name: "substr($0,37)"\n"ENVIRON["here"]"\n"source"\x27"}'
答案1
我会使用 perl 而不是 awk 来执行此操作,因为:
perl 可以读取目录内容本身
readdir()
perl 有一个内置
stat()
函数,具有与 stat 命令类似的功能。以及一个非常好的 Date::Format 模块,用于格式化日期和时间
perl 还具有 DBI、DBD 和 DBD::mysql 模块来直接与数据库交互(例如将记录插入 mysql 本身)。
?
DBI 准备好的语句中的占位符全部摆脱在 SQL 命令字符串中使用转义和引用变量的痛苦。一个脚本就可以完成这一切 - 而且比处理 shell 引用或将环境变量传递给 awk 的麻烦要少得多。
#!/usr/bin/perl
use strict;
use Date::Format;
use DBI;
# Fill in your database details here.
my $database='';
my $hostname='';
my $port='';
my $user='';
my $password='';
# set up connection to database.
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $user, $password);
# set up sql statement
my $sth = $dbh->prepare('INSERT INTO action_diary (entry_date, entry_description, entry_details) VALUES (?,?,?)');
use Cwd;
my $cwd = getcwd;
# 'source' must be exported from the parent environment.
# alternatively, pass it as a command-line arg and read it from, e.g., $ARGV[0]
my $source=$ENV{'source'};
# find all .afl files in current dir, store with ctime in %files hash
# use `(stat($_))[9]` if you want the file's mtime rather than ctime.
opendir(DIR, '.') || die "Can't opendir .: $!\n";
foreach (readdir(DIR)) {
next unless (-f "./$_" && m/\.afl$/);
$files{$_} = (stat($_))[10];
};
closedir(DIR);
# sort the hash by value (timestamp)
foreach my $f (sort { $files{$a} <=> $files{$b} } keys %files) {
my $Y = time2str('%Y',$files{$f});
my $M = time2str('%m',$files{$f});
my $D = time2str('%d',$files{$f});
my $YMD = "$Y-$M-$D";
my $details = "File Name: $Y $M $D $f\n$cwd\n$source";
$sth->execute($YMD,'',$details);
}
$sth->finish();
$dbh->disconnect();
如果你只是想让脚本输出一系列可以保存到文件或通过管道传输到 mysql 的 SQL 语句,那就更简单了:
#!/usr/bin/perl
use strict;
use Date::Format;
use Cwd;
my $cwd = getcwd;
# 'source' must be exported from the parent environment.
# alternatively, pass it as a command-line arg and read it from, e.g., $ARGV[0]
my $source=$ENV{'source'};
# find all .afl files in current dir, store with ctime in %files hash
# use `(stat($_))[9]` if you want the file's mtime rather than ctime.
my %files=();
opendir(DIR, '.') || die "Can't opendir .: $!\n";
foreach (readdir(DIR)) {
next unless (-f "./$_" && m/\.afl$/);
$files{$_} = (stat($_))[10];
};
closedir(DIR);
my $FMT="INSERT INTO action_diary (entry_date, entry_description, entry_details) VALUES ('%s','%s','%s')\n";
# sort the hash by value (timestamp)
foreach my $f (sort { $files{$a} <=> $files{$b} } keys %files) {
my $Y = time2str('%Y',$files{$f});
my $M = time2str('%m',$files{$f});
my $D = time2str('%d',$files{$f});
my $YMD = "$Y-$M-$D";
my $details = "File Name: $Y $M $D $f\n$cwd\n$source";
# backslash-escape any quotes that may be in $details (i.e. from $f or $source).
# NOTE: very primitive. There are lots more characters that might need escaping
# or special handling than just a single-quote.
$details =~ s/'/\\'/g;
printf $FMT, $YMD,'',$details;
}
答案2
使用的建议来自穆鲁
stat -c '%y %n' *.afl| here="$(cygpath -w $PWD)" awk -v source="$source" '{print "INSERT INTO activity_diary (entry_date, entry_description, entry_details) VALUES (STR_TO_DATE(\x27"substr($0,1,19)"\x27,\x27%Y-%m-%d\x27),,\x27Monitoring Log\x27,\x27" "File Name: "substr($0,37)"\nFile Location: "ENVIRON["here"]"\n"source"\x27)"}' >> new_activity_data.sql