黑哥，请进，我需要你的帮助！！！

侧面bt

现在要把错误日志文件里面的sql语句提出来，组合成正确的重装执行，日志文件的格式如下：

[time] 2010-07-05 10:45:54.765432
[tid] 6308
[file] ../src/DBOpr.cpp
[line] 38
[info] DB Error: Fail To Execute SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UpdatePeroid = 0 where id= 1164605496' at line 1
SQL: update table_29 set dname = 'sunsecond.com', IPNum = 1, IP = '114.80.162.3',  UpdateTime = '2010-07-05 10:45:54' UpdatePeroid = 0 where id = 116460549

[time] 2010-07-05 10:45:57.229553
[tid] 6310
[file] ../src/DBOpr.cpp
[line] 38
[info] DB Error: Fail To Execute SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UpdatePeroid = 0 where id= 14204116719' at line 1
SQL: update table_21 set dname = '15aaa.com', IPNum = 3, IP = '173.244.202.183
n173.244.202.184
173.244.202.189'
UpdateTime = '2010-07-05 10:45:57' UpdatePeroid = 0 where id = 1420411671

现在的问题是对于ip个数是1个的倒还好处理，因为都在一行内，但对于IP有多个的，由于IP这个字段是换\n分隔的，所以在日志文件里变成了多行，还在要还原为一行，比如第一条记录要还原成：
update table_21 set dname = '15aaa.com', IPNum = 3, IP = '173.244.202.183\n173.244.202.184\n173.244.202.189' UpdateTime = '2010-07-05 10:45:57' UpdatePeroid = 0 where id = 142041167

请问黑哥该怎么处理啊，用什么好，awk还是sed还是其它方法，谢了，对了，日志记录非常大，大概有10万条以上。

blackold

回复 1# 侧面bt


    大家都能帮忙啊。你这样写不好。

BangBull

回复 1# 侧面bt


      多贴点日志，黑哥才能帮你

bbgg1983

1. $ cat a
   
2. nfo] DB Error: Fail To Execute SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQ
   
3. L server version for the right syntax to use near 'UpdatePeroid = 0 where DomainHash = 1420411671916276923' at line 1
   
4. SQL: update table_21 set dname = '15aaa.com', IPNum = 3, IP = '173.244.202.183
   
5. n173.244.202.184
   
6. 173.244.202.189'
   
7. UpdateTime = '2010-07-05 10:45:57' UpdatePeroid = 0 where id = 1420411671
   
8. 
   
9. $ sed  '/IP/{:a;N;/UpdateTime/!ba;s/\n/ /g;}' a
   
10. nfo] DB Error: Fail To Execute SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQ
    
11. L server version for the right syntax to use near 'UpdatePeroid = 0 where DomainHash = 1420411671916276923' at line 1
    
12. SQL: update table_21 set dname = '15aaa.com', IPNum = 3, IP = '173.244.202.183 n173.244.202.184 173.244.202.189' UpdateT
    
13. ime = '2010-07-05 10:45:57' UpdatePeroid = 0 where id = 1420411671

复制代码

侧面bt

谢谢楼上的，可以解释一下吗，看不太懂。

lkk2003rty

1. awk '/^SQL/{printf "%s ",substr($0,index($0,"update"));while(getline && $0 !~ /^$/)printf "%s ",$0;printf "\n\n"}' file

复制代码

bbgg1983

回复 5# 侧面bt


    sed  '/IP/{:a;N;/UpdateTime/!ba;s/\n/ /g;}'
匹配到IP所在行，从这行开始，把下面的行都读到模式空间里，直到匹配到UpdateTime为止，以上做的，实际上就是把所有包含IP地址的行读进准备处理，以下的就是处理了：把换行符替换为一个空格。

blackold

回复 1# 侧面bt


    如果是gawk:

1. awk -v RS='at line[^\n]*\nSQL:|\n[ \t\n]*\n' '!(NR%2){$1=$1;print}' urfile

复制代码

iori809

我也写一个~
awk 'BEGIN{RS=""}{gsub(/.*SQL: |\n/," ");print}' test5.txt

bluesgone

怎么你的sql没有加上逗号，你应该先修改好应用程序才来做这些脚本吧。


我觉得还是先按模式grep出来这堆sql，然后用vi慢慢整理，

避免你用批处理又搞出其他问题来了。

建议：开始搞之前先备份一下数据库表。