19,612
社区成员
发帖
与我相关
我的任务
分享
#!/bin/bash
IFS=$'\n'
LINE_HEADER="INSERT INTO bonusinfo VALUES ("
for line in $(cat file); do
[[ "$line" =~ ^INSERT\ INTO\ bonusinfo\ VALUES\ \( ]] && \
{
IFS=','
value_list=(${line:30})
IFS=$'\n'
LEN=${#value_list[@]}
SQL=""
for ((i=0; i < LEN; i++)); do
[[ "${value_list[i]}" =~ \'wx20 ]] && \
{
SQL="$SQL${value_list[i]},${value_list[((i+1))]}"
break
} || \
SQL="${SQL}${value_list[i]},"
done
echo "${LINE_HEADER}${SQL});" >> dest.sql
} || \
echo ${line} >> dest.sql
done
用sed就更简单:
sed "/INSERT INTO bonusinfo/{s/\(^.*'wx20[^,]*\s*,[^,]*\)\s*,.*/\1);/p}"file
但其实并没有解决问题。
看楼主的SQL文件,肯定不止一个表要处理
SQL脚本肯定也要处理CREATE语句,否则CREATE的时候就错了
做sql parser谈不上技术,咱们老老实实跟着洋人屁股后面认真操作这种就别叫技术了。
# /usr/bin/env python
# -*- coding:utf-8 -*-
source_file = open('source.sql', 'r')
output_file = open('output.sql', 'w')
LINE_HEADER = 'INSERT INTO bonusinfo VALUES ('
for line in source_file:
if line.startswith(LINE_HEADER):
values_list = line[30:].split(', ')
pos = filter(lambda x:x[1].startswith("'wx2"), enumerate(values_list))[0][0] + 2
values_list = values_list[:pos]
sql = LINE_HEADER + ','.join(values_list) + ');\n'
output_file.write(sql)
print sql
else:
output_file.write(line)
output_file.close()
source_file.close()
结果大概是这样的
。。。。。。。
INSERT INTO bonusinfo VALUES ('155','1322',null,'2','2','200.00','0.00','恭喜发财,大吉大利!','1','WX20160203071213502170','200.00','2','1',to_date('2016-02-03 07:12:13','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:13:11','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:12:26','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203071213502165','2','wx20160203071216a28641d6810853298982','1004270253201602033061770482');
INSERT INTO bonusinfo VALUES ('158','11326',null,'2','3','300.00','0.00','恭喜发财,大吉大利!','1','WX20160203072010709938','300.00','2','1',to_date('2016-02-03 07:20:10','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:21:11','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:20:25','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072010709555','2','wx2016020307201222590117350713812158','1006530253201602033061789875');
INSERT INTO bonusinfo VALUES ('161','454660',null,'2','1','1000.00','0.00','吃饭睡觉','1','WX20160203072035936415','1000.00','2','1',to_date('2016-02-03 07:20:35','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:20:59','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:20:46','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072035936988','2','wx2016020307203728e84367ac0202349937','1005280253201602033061794641');
INSERT INTO bonusinfo VALUES ('164','2546',null,'2','3','1000.00','0.00','恭喜发财,大吉大利!','1','WX20160203072158588549','1000.00','2','1',to_date('2016-02-03 07:21:58','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:22:39','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:22:11','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072158588679','2','wx20160203072200124685902f0583544905','1010010253201602033059900363');
INSERT INTO bonusinfo VALUES ('167','2546',null,'1','1','100.00','100.00','恭喜发财,大吉大利!','1','WX20160203072337531977','100.00','2','1',to_date('2016-02-03 07:23:37','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:24:04','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:23:51','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072337531869','2','wx20160203072342530cab947e0688696979','1010010253201602033061791985');
INSERT INTO bonusinfo VALUES ('170','454660',null,'2','2','300.00','0.00','恭喜发财,大吉大利!','1','WX20160203072503218998','300.00','2','1',to_date('2016-02-03 07:25:03','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:25:49','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:25:40','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072503218489','2','wx2016020307250533fabe996f0266289668','1005280253201602033059922012');
INSERT INTO bonusinfo VALUES ('173','2546',null,'2','2','500.00','0.00','恭喜发财,大吉大利!','1','WX20160203072529435469','500.00','2','1',to_date('2016-02-03 07:25:29','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:25:56','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:25:40','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072529435500','2','wx201602030725319ffbbe243c0332934972','1010010253201602033059919860');
INSERT INTO bonusinfo VALUES ('176','59',null,'2','2','300.00','0.00','恭喜发财,大吉大利!','1','WX20160203072807384486','300.00','2','1',to_date('2016-02-03 07:28:07','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:29:57','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:28:32','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072807384621','2','wx20160203072809c5095842e90662931854','1005770253201602033059917159');
INSERT INTO bonusinfo VALUES ('179','195841',null,'2','2','200.00','0.00','恭喜发财,大吉大利!','1','WX20160203072818611703','200.00','2','1',to_date('2016-02-03 07:28:18','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:30:16','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:28:37','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203072818611578','2','wx201602030728207a2b0de8890854165257','1007050253201602033061814639');
INSERT INTO bonusinfo VALUES ('182','403',null,'2','3','500.00','0.00','恭喜发财,大吉大利!','1','WX20160203073002277883','500.00','2','1',to_date('2016-02-03 07:30:02','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:30:34','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:30:20','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203073002277912','2','wx201602030730049982147c130633805796','1008030253201602033059936421');
INSERT INTO bonusinfo VALUES ('185','2546',null,'2','3','300.00','0.00','恭喜发财,大吉大利!','1','WX20160203073046053457','300.00','1','1',to_date('2016-02-03 07:30:46','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:30:47','yyyy-mm-dd hh24:mi:ss'),null,null,'300.00','HB20160203073046053417','1','wx20160203073048e987a47cc60486472829',null);
INSERT INTO bonusinfo VALUES ('188','454660',null,'1','2','200.00','100.00','15219491987','1','WX20160203073113584332','200.00','3','1',to_date('2016-02-03 07:31:13','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-04 02:00:35','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:31:23','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-04 02:00:35','yyyy-mm-dd hh24:mi:ss'),'200.00','HB20160203073113584450','3','wx20160203073115845bb1f2530999235474','1005280253201602033061815321');
INSERT INTO bonusinfo VALUES ('191','2546',null,'2','1','100.00','0.00','恭喜发财,大吉大利!','1','WX20160203073129901159','100.00','2','1',to_date('2016-02-03 07:31:29','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:31:48','yyyy-mm-dd hh24:mi:ss'),to_date('2016-02-03 07:31:40','yyyy-mm-dd hh24:mi:ss'),null,'0.00','HB20160203073129901703','2','wx201602030731311451fe61b60185849180','1010010253201602033061814556');
。。。。。。。sed -i "s/^\..long.*\.$/" "/2g" file
老实说,需求我没看懂。 1.insert里的用什么模式判断long字段?是通过内容,还是要通过create部分判断long字段的位置,然后再跳到insert里判断? 2.是去掉多个long,只留下一个,还是第一个long后所有字段都删除 sed的正则对嵌套括号、嵌套引号的判断是很麻烦的,不支持断言,回溯、递归等复杂匹配,所以一旦要匹配这种 "a, '1,2,3','(a,(b,c,('a,b,c',(c,'a,b')))), 1, 2', c, d",间隔用逗号,表达式里也有逗号,而且表达式多重嵌套是几乎搞不定的 所以shell “=~”、grep、sed、awk这些仅仅用来匹配相对简单的文本,复杂的还是要用perl、python 上一个需求很明确,所有pattern写起来比这个容易。 一看就是Oracle的插入脚本(还有其他数据,大小7G),但是同一个表内不能有多个long字段,所以想把insert行中的long字段只保留一个,其他的都删除,即每个insert语句都截止到红色的字段部分,红色部分到后面括号里的内容全删除。
sed -i "s/^\..long.*\.$/" "/2g" file
一看就是Oracle的插入脚本(还有其他数据,大小7G),但是同一个表内不能有多个long字段,所以想把insert行中的long字段只保留一个,其他的都删除,即每个insert语句都截止到红色的字段部分,红色部分到后面括号里的内容全删除。