帮忙分析下这个日志，抽取其中的SQL语句

wendy_85

我想把红色标示的SQL抽取出来，也有update语句delete语句。 SQL语句也有换行的情况，抽取之后，SQL语句必须由多行变为一行。


LOG:  statement: SELECT c.relname FROM sys_catalog.sys_namespace u, sys_catalog.sys_class c WHERE u.oid = c.relnamespace AND u.nspname = UPPER('PUBLIC') AND c.relname = UPPER('KDB_BIGINT')
LOG:  statement: INSERT INTO TEST.PUBLIC.KDB_BIGINT (KDB_ID, KDB_BIGINT1) VALUES (8, 9223372036854775807)
LOG:  statement: INSERT INTO TEST.PUBLIC.KDB_BIGINT (KDB_ID, KDB_BIGINT1) VALUES (9, 12345)
LOG:  statement: INSERT INTO TEST.PUBLIC.KDB_BIGINT (KDB_ID, KDB_BIGINT1) VALUES (10, -922337203685477580
LOG:  statement: ROLLBACK
LOG:  close _PLAN0EE4CFC0
LOG:  statement:  
LOG:  statement: set DateStyle to 'ISO'
LOG:  statement: show implicit_savepoint
LOG:  statement: show server_encoding
LOG:  statement: set client_encoding to 'GBK'
LOG:  statement: show case_sensitive
LOG:  statement: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
LOG:  statement: BEGIN
LOG:  statement: select * from KDB_BIGINT_IDENTITY order by  KDB_ID
LOG:  statement: ROLLBACK
LOG:  statement: set client_encoding to 'GBK'
LOG:  statement: select * from
        KDB_FLOAT
        where KDB_ID =1;
LOG:  statement: select * from
        TEST_MERGE_INTO_DEST;
LOG:  statement: set client_encoding to 'GBK'
NOTICE:  CheckPoint Start at: 2011-03-30 14:08:01
NOTICE:  CheckPoint Sync Start at: 2011-03-30 14:08:03
NOTICE:  CheckPoint Done at: 2011-03-30 14:08:03
ERROR:  syntax error at or near "." at character 18
STATEMENT:  select a.KDB_ID a.KDB_INT1
        from KDB_INT a, KDB_FLOAT b
        where a.KDB_ID=b.KDB_ID;
LOG:  statement: select a.KDB_ID,a.KDB_INT1
        from KDB_INT a, KDB_FLOAT b
        where a.KDB_ID=b.KDB_ID;
FATAL:  terminating connection due to administrator command
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  shutting down

baoyu05

1. [root@fc9 shell]# sed -r  -n  '/statement:\s*select/{:a;/statement:.*(LOG|FATAL)/!{N;ba};s/.*: (.*)(LOG|FATAL).*/\1/;s/\n/ /g;s/\s+/ /g;p}' d
   
2. select * from KDB_BIGINT_IDENTITY order by KDB_ID
   
3. select * from KDB_FLOAT where KDB_ID =1;
   
4. select a.KDB_ID,a.KDB_INT1 from KDB_INT a, KDB_FLOAT b where a.KDB_ID=b.KDB_ID;
   
5. [root@fc9 shell]#
   

复制代码

网中淫

金牙哥好猛

yinyuemi

awk:

1. awk -vRS="LOG:  statement|FATAL" '/: select/{print gensub(/\n| +|:/," ","g",$0)}'  file  
   
2.   select * from KDB_BIGINT_IDENTITY order by KDB_ID
   
3.   select * from   KDB_FLOAT  where KDB_ID =1;
   
4.   select * from   TEST_MERGE_INTO_DEST;
   
5.   select a.KDB_ID,a.KDB_INT1  from KDB_INT a, KDB_FLOAT b  where a.KDB_ID=b.KDB_ID;
   

复制代码

doujikai

看了半天愣是没怎么明白，sed里“{:"这个是做什么用的？

yinyuemi

回复 5# doujikai


    应该分开看
"{" 和 ":a"

doujikai

还是不明白，楼上的能推荐一些sed的资料不，我研究下，呵呵

doujikai

：a在sed里代表什么意思？

yinyuemi

回复 8# doujikai


    http://bbs.chinaunix.net/viewthr ... &from=favorites

doujikai

终于看懂了，：a应该是做个标记，ba是循环直到/statement:.*(LOG|FATAL)/为止，然后再把换行去掉，把空格换成一个空格，应该就成了，果然很牛！！