(?m)使用实例

最新推荐文章于 2021-10-28 14:47:35 发布

scan724

最新推荐文章于 2021-10-28 14:47:35 发布

阅读量159

点赞数

本文链接：https://blog.csdn.net/zhaoyangjian724/article/details/52399101

版权

示例sql:
 # User@Host: zjzc_app[zjzc_app] @  [10.22.18.164]  Id: 6069153
 # Query_time: 153.908486  Lock_time: 0.000113 Rows_sent: 27  Rows_examined: 430083120
 SET timestamp=1472662954;
 SELECT t.*  FROM
 	    (
 			SELECT 
 			t1.sn AS clientSn,
 			t1.userNick,
 			t1.mobilePhone,
 			t3.personName,
 			t2.availableBalance,
 			(SELECT IFNULL(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount,
 			(SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn AND t.status IN ('1','2','3','4') ) AS withdrawAmount,
 			( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1') 
 			  + 
 			  (SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1')  
 			) AS investAmount,
 			( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2') 
 			  + 
 			  (SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2')  
 			) AS yieldAmount,
 			(SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS cashCouponAmount
 			FROM  Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t999
 			WHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn
 	    ) t  WHERE (t.rechargeAmount + t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;


grok 表达式:

\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: %{NUMBER:rows_sent}\s

+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*


输出:

{
  "user": [
    [
      "zjzc_app"
    ]
  ],
  "clientip": [
    [
      "10.252.148.164"
    ]
  ],
  "id": [
    [
      "Id: 6069153"
    ]
  ],
  "Query_time": [
    [
      "153.908486"
    ]
  ],
  "lock_time": [
    [
      "0.000113"
    ]
  ],
  "rows_sent": [
    [
      "27"
    ]
  ],
  "rows_examined": [
    [
      "430083120"
    ]
  ],
  "timestamp": [
    [
      "1472662954"
    ]
  ],
  "query": [
    [
      "SELECT t.*  FROM"
    ]
  ]
}

此时没有在开头加上(?m)

/******************************************
(?m)\s*# User@Host:\s+\S+\[%{USER:user}\]\s+@\s+\[%{IP:clientip}\]\s+(?<id>(\S+\s+)*\S+)\s*#\s+Query_time:\s+%{NUMBER:Query_time}\s+Lock_time: %{NUMBER:lock_time}\s+Rows_sent: 

%{NUMBER:rows_sent}\s+Rows_examined: %{NUMBER:rows_examined}\s*
\s*SET\s+timestamp=%{NUMBER:timestamp};\s*(?<query>(\s*\S+\s*).*)\s*


{
  "user": [
    [
      "zjzc_app"
    ]
  ],
  "clientip": [
    [
      "10.252.148.164"
    ]
  ],
  "id": [
    [
      "Id: 6069153"
    ]
  ],
  "Query_time": [
    [
      "153.908486"
    ]
  ],
  "lock_time": [
    [
      "0.000113"
    ]
  ],
  "rows_sent": [
    [
      "27"
    ]
  ],
  "rows_examined": [
    [
      "430083120"
    ]
  ],
  "timestamp": [
    [
      "1472662954"
    ]
  ],
  "query": [
    [
      "SELECT t.*  FROM\n \t    (\n \t\t\tSELECT \n \t\t\tt1.sn AS clientSn,\n \t\t\tt1.userNick,\n \t\t\tt1.mobilePhone,\n \t\t\tt3.personName,\n \t\t\tt2.availableBalance,\n \t\t\t(SELECT IFNULL

(SUM(amount) , 0) FROM ClientRechargeOrder t WHERE t.clientSn= t1.sn AND t.status ='2') AS rechargeAmount,\n \t\t\t(SELECT IFNULL(SUM(amount) , 0) FROM ClientWithDrawOrder t WHERE t.clientSn= t1.sn 

AND t.status IN ('1','2','3','4') ) AS withdrawAmount,\n \t\t\t( (SELECT IFNULL(SUM(capitalBalance) , 0) FROM ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '1') \n \t\t\t  + \n \t\t\t  

(SELECT IFNULL(SUM(capitalBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '1')  \n \t\t\t) AS investAmount,\n \t\t\t( (SELECT IFNULL(SUM(yieldBalance) , 0) FROM 

ProductRepayment t WHERE t.clientSn= t1.sn AND t.status= '2') \n \t\t\t  + \n \t\t\t  (SELECT IFNULL(SUM(yieldBalance) , 0) FROM VirtualProductOrder t WHERE t.clientSn= t1.sn AND t.status= '2')  \n 

\t\t\t) AS yieldAmount,\n \t\t\t(SELECT IFNULL(SUM(t0.amount) , 0) FROM ClientCoupon t,Coupon t0 WHERE t.clientSn= t1.sn AND t.status = '2' AND t.couponSn = t0.sn AND t0.type IN (1,2)) AS 

cashCouponAmount\n \t\t\tFROM  Client t1 , ClientAssetInfo t2 , ClientPersonalInfo t999\n \t\t\tWHERE t1.sn = t2.clientSn AND t1.sn = t3.clientSn\n \t    ) t  WHERE (t.rechargeAmount + 

t.yieldAmount + t.cashCouponAmount - t.withdrawAmount - t.investAmount - t.availableBalance) != 0;\n"
    ]
  ]
}