☰

shell脚本从存储在文本文件中的变量列表中生成“更新”sql命令

tag-icon • tag-icon shell-script awk
shell脚本从存储在文本文件中的变量列表中生成“更新”sql命令

我有一个文本文件“users.txt”，其中包含如下变量：

trialuser1,paidUser1,paidPasswd1,paidDate1
trialuser2,paidUser2,paidPasswd2,paidDate2
trialuser3,paidUser3,paidPasswd3,paidDate3
trialuser4,paidUser4,paidPasswd4,paidDate4
trialuser5,paidUser5,paidPasswd5,paidDate5
....
....
....

现在，我想制作一个 shell 脚本，它将创建一个 sql“updateusers.sql”文件，其中包含使用存储在上述文本文件中的变量的“Update”sql 语句：

update systemx.thetable set username='paidUser1',password='paidPasswd1',payment='paid',paidDate='paidDate1',token='init' where username='trialuser1'; 
update systemx.thetable set username='paidUser2',password='paidPasswd2',payment='paid',paidDate='paidDate2',token='init' where username='trialuser2'; 
update systemx.thetable set username='paidUser3',password='paidPasswd3',payment='paid',paidDate='paidDate3',token='init' where username='trialuser3'; 
update systemx.thetable set username='paidUser4',password='paidPasswd4',payment='paid',paidDate='paidDate4',token='init' where username='trialuser4'; 
update systemx.thetable set username='paidUser5',password='paidPasswd5',payment='paid',paidDate='paidDate5',token='init' where username='trialuser5'; 
....
....
....

答案1

#!/usr/bin/awk -f

BEGIN { 
    FS=",";
    fmt="update systemx.thetable set username='%s'," \
        "password='%s',payment='paid',paidDate='%s'," \
        "token='init' where username='%s';\n";
};

{ printf fmt, $2, $3, $4, $1 };

另存为，例如，samin.awk使用 使其可执行chmod +x samin.awk，然后：

$ ./samin.awk users.txt
update systemx.thetable set username='paidUser1',password='paidPasswd1',payment='paid',paidDate='paidDate1',token='init' where username='trialuser1';
update systemx.thetable set username='paidUser2',password='paidPasswd2',payment='paid',paidDate='paidDate2',token='init' where username='trialuser2';
update systemx.thetable set username='paidUser3',password='paidPasswd3',payment='paid',paidDate='paidDate3',token='init' where username='trialuser3';
update systemx.thetable set username='paidUser4',password='paidPasswd4',payment='paid',paidDate='paidDate4',token='init' where username='trialuser4';
update systemx.thetable set username='paidUser5',password='paidPasswd5',payment='paid',paidDate='paidDate5',token='init' where username='trialuser5';

答案2

awk对于一次一行处理文件并以某种方式转换它们确实是一个不错的选择；我是这样想出来的：

awk -F, '{print "update systemx.thetable set username='\''"$2"'\'',password='\''"$3"'\'',payment='\''paid'\'',paidDate='\''"$4"'\'',token='\''init'\'' where username='\''"$1"'\'';"}' users.txt

我假设您想要字段 4“paidDate5”，而不是静态日期“2017-08-17”。

这会将相应的文本打印到屏幕上；您可以将其重定向到 sql 脚本以便稍后执行。

答案3

awk解决方案：

awk -F, '{ printf("update systemx.thetable set username=\047%s\047,password=\047%s\047,
           payment=\047paid\047,paidDate=\047%s\047,token=\047init\047 
           where username=\047%s\047;\n",$2,$3,$4,$1) }' file

输出：

update systemx.thetable set username='paidUser1',password='paidPasswd1',payment='paid',paidDate='paidDate1',token='init' where username='trialuser1';
update systemx.thetable set username='paidUser2',password='paidPasswd2',payment='paid',paidDate='paidDate2',token='init' where username='trialuser2';
update systemx.thetable set username='paidUser3',password='paidPasswd3',payment='paid',paidDate='paidDate3',token='init' where username='trialuser3';
update systemx.thetable set username='paidUser4',password='paidPasswd4',payment='paid',paidDate='paidDate4',token='init' where username='trialuser4';
update systemx.thetable set username='paidUser5',password='paidPasswd5',payment='paid',paidDate='paidDate5',token='init' where username='trialuser5';

相关内容