linux awk的用法

本文讲述linux awk命令的用法。awk用于对文件列的处理，即纵向处理，区别于sed(用于文件行，即横向处理)。可以对列实现切割，可以自定义分割字符，且可以在某些列之间插入自定义字符串。

根据给定对照关系生成mysql语句

我们可能有时候会遇到这类需求: 将数据库表中的某个字段的值改成另一个值，现在分别给了原值(存于file1文件)和新值(存于file2文件)，要求生成mysql更新语句。

1. 准备file1和file2

file1内容:

(py3.6) wangshibiao@wangshibiao:~/test/test_paste$ cat ./file1 
 2416 
 2417 
 2418 
 2419 
 2420 
 2421 
 2422 
 2423 
 2426 
 2427 
 2428 
 2457 
 2458 
 2459 
 2460 
 2461 
 2462 
 2463 
 2464 
 2465 
 2466 
 2467 
 2475 
 2476 
 2477 
 2478 
 2480 
 2485 
 2486 
 2488 
 2489 
 2490 
 2491 
 2492 
 2493 
 2494 
 2495 
(py3.6) wangshibiao@wangshibiao:~/test/test_paste$

file2内容:

(py3.6) wangshibiao@wangshibiao:~/test/test_paste$ cat ./file2  
 3206 
 3207 
 3208 
 3209 
 3210 
 3211 
 3212 
 3213 
 3214 
 3215 
 3216 
 3217 
 3218 
 3219 
 3220 
 3221 
 3222 
 3223 
 3224 
 3225 
 3226 
 3227 
 3228 
 3229 
 3230 
 3231 
 3232 
 3233 
 3234 
 3235 
 3236 
 3237 
 3238 
 3239 
 3240 
 3241 
 3242  
(py3.6) wangshibiao@wangshibiao:~/test/test_paste$

2. 合并2个文件的列，生成对应关系到文件result

(py3.6) wangshibiao@wangshibiao:~/test/test_paste$ paste ./file2_ ./file1_ > ./result
(py3.6) wangshibiao@wangshibiao:~/test/test_paste$ cat ./result 
 3206    2416 
 3207    2417 
 3208    2418 
 3209    2419 
 3210    2420 
 3211    2421 
 3212    2422 
 3213    2423 
 3214    2426 
 3215    2427 
 3216    2428 
 3217    2457 
 3218    2458 
 3219    2459 
 3220    2460 
 3221    2461 
 3222    2462 
 3223    2463 
 3224    2464 
 3225    2465 
 3226    2466 
 3227    2467 
 3228    2475 
 3229    2476 
 3230    2477 
 3231    2478 
 3232    2480 
 3233    2485 
 3234    2486 
 3235    2488 
 3236    2489 
 3237    2490 
 3238    2491 
 3239    2492 
 3240    2493 
 3241    2494 
 3242    2495 
(py3.6) wangshibiao@wangshibiao:~/test/test_paste$

3. 生成mysql语句

(py3.6) wangshibiao@wangshibiao:~/test/test_paste$ cat ./result | awk  '{print "update table1 set rid = "$1, " where rid = "$2}' | xargs -I '{}' echo {}';'
update table1 set rid = 3206  where rid = 2416;
update table1 set rid = 3207  where rid = 2417;
update table1 set rid = 3208  where rid = 2418;
update table1 set rid = 3209  where rid = 2419;
update table1 set rid = 3210  where rid = 2420;
update table1 set rid = 3211  where rid = 2421;
update table1 set rid = 3212  where rid = 2422;
update table1 set rid = 3213  where rid = 2423;
update table1 set rid = 3214  where rid = 2426;
update table1 set rid = 3215  where rid = 2427;
update table1 set rid = 3216  where rid = 2428;
update table1 set rid = 3217  where rid = 2457;
update table1 set rid = 3218  where rid = 2458;
update table1 set rid = 3219  where rid = 2459;
update table1 set rid = 3220  where rid = 2460;
update table1 set rid = 3221  where rid = 2461;
update table1 set rid = 3222  where rid = 2462;
update table1 set rid = 3223  where rid = 2463;
update table1 set rid = 3224  where rid = 2464;
update table1 set rid = 3225  where rid = 2465;
update table1 set rid = 3226  where rid = 2466;
update table1 set rid = 3227  where rid = 2467;
update table1 set rid = 3228  where rid = 2475;
update table1 set rid = 3229  where rid = 2476;
update table1 set rid = 3230  where rid = 2477;
update table1 set rid = 3231  where rid = 2478;
update table1 set rid = 3232  where rid = 2480;
update table1 set rid = 3233  where rid = 2485;
update table1 set rid = 3234  where rid = 2486;
update table1 set rid = 3235  where rid = 2488;
update table1 set rid = 3236  where rid = 2489;
update table1 set rid = 3237  where rid = 2490;
update table1 set rid = 3238  where rid = 2491;
update table1 set rid = 3239  where rid = 2492;
update table1 set rid = 3240  where rid = 2493;
update table1 set rid = 3241  where rid = 2494;
update table1 set rid = 3242  where rid = 2495;
(py3.6) wangshibiao@wangshibiao:~/test/test_paste$

该语句中用到了awk的字符串拼接，且结合了xargs命令(补充mysql语句末尾的分号)。