<pre name="code" class="html"># User@Host: zjzc_app[zjzc_app] @ [10.171.243.55] Id: 1621705
# Query_time: 10.666552 Lock_time: 0.000392 Rows_sent: 15 Rows_examined: 24829551
SET timestamp=1452566321;
SELECT
*
FROM
(SELECT
c.sn,
c.userNick,
c.mobilePhone,
DATE_FORMAT(c.registerTime, '%Y/%m/%d') registerTime,
cp.personName,
cp.personCardNo,
cp.gender,
cp.personAuthStatus,
IF(1407 = asm.clientManagerSn
OR 1407 IS NULL, cei.policyAreaSn, NULL) AS policyAreaSn,
IF(1407 = asm.clientManagerSn
OR 1407 IS NULL, area.areaName, NULL) AS policyAreaName,
(SELECT
IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)
FROM
ProductRepayment pr, Product p
WHERE
pr.productSn = p.sn
AND pr.clientSn = c.sn
AND pr.status = '1'
AND DATEDIFF(p.realValueEndDate, '2016-01-12') <= 7
AND DATEDIFF(p.realValueEndDate, '2016-01-12') >= 0) investBalance,
(SELECT
IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) + cai.availableBalance + cai.frozenWithDraw + cai.frozenPay
FROM
ProductRepayment
WHERE
clientSn = c.sn AND status = '1') totalBalance,
(cai.frozenWithDraw + cai.frozenPay) frozenBalance,
cai.availableBalance
FROM
Client c
LEFT JOIN ClientExtraInfo cei ON c.sn = cei.clientSn
LEFT JOIN Area area ON cei.policyAreaSn = area.sn
LEFT JOIN AssignClientManager asm ON asm.clientSn = c.sn, ClientPersonalInfo cp, ClientAssetInfo cai
WHERE
c.sn = cp.clientSn AND asm.status = '1'
AND c.sn = cai.clientSn
AND c.sn IN (SELECT
asm.clientSn
FROM
AssignClientManager asm
WHERE
asm.clientManagerSn IN (1407)
AND asm.status = '1')) t
ORDER BY sn DESC
LIMIT 1020 , 15;
表达式:
(?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.171.243.55"
]
],
"id": [
[
"Id: 1621705"
]
],
"Query_time": [
[
"10.666552"
]
],
"lock_time": [
[
"0.000392"
]
],
"rows_sent": [
[
"15"
]
],
"rows_examined": [
[
"24829551"
]
],
"timestamp": [
[
"1452566321"
]
],
"query": [
[
"SELECT \n *\nFROM\n (SELECT \n c.sn,\n c.userNick,\n c.mobilePhone,\n DATE_FORMAT(c.registerTime, '%Y/%m/%d') registerTime,\n cp.personName,\n cp.personCardNo,\n cp.gender,\n cp.personAuthStatus,\n IF(1407 = asm.clientManagerSn\n OR 1407 IS NULL, cei.policyAreaSn, NULL) AS policyAreaSn,\n IF(1407 = asm.clientManagerSn\n OR 1407 IS NULL, area.areaName, NULL) AS policyAreaName,\n (SELECT \n IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0)\n FROM\n ProductRepayment pr, Product p\n WHERE\n pr.productSn = p.sn\n AND pr.clientSn = c.sn\n AND pr.status = '1'\n AND DATEDIFF(p.realValueEndDate, '2016-01-12') <= 7\n AND DATEDIFF(p.realValueEndDate, '2016-01-12') >= 0) investBalance,\n (SELECT \n IFNULL(SUM(capitalBalance), 0) + IFNULL(SUM(yieldBalance), 0) + cai.availableBalance + cai.frozenWithDraw + cai.frozenPay\n FROM\n ProductRepayment\n WHERE\n clientSn = c.sn AND status = '1') totalBalance,\n (cai.frozenWithDraw + cai.frozenPay) frozenBalance,\n cai.availableBalance\n FROM\n Client c\n LEFT JOIN ClientExtraInfo cei ON c.sn = cei.clientSn\n LEFT JOIN Area area ON cei.policyAreaSn = area.sn\n LEFT JOIN AssignClientManager asm ON asm.clientSn = c.sn, ClientPersonalInfo cp, ClientAssetInfo cai\n WHERE\n c.sn = cp.clientSn AND asm.status = '1'\n AND c.sn = cai.clientSn\n AND c.sn IN (SELECT \n asm.clientSn\n FROM\n AssignClientManager asm\n WHERE\n asm.clientManagerSn IN (1407)\n AND asm.status = '1')) t\nORDER BY sn DESC\nLIMIT 1020 , 15;"
]
]
}
如果去掉(?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"
]
],
"USERNAME": [
[
"zjzc_app"
]
],
"clientip": [
[
"10.171.243.55"
]
],
"IPV6": [
[
null
]
],
"IPV4": [
[
"10.171.243.55"
]
],
"id": [
[
"Id: 1621705"
]
],
"Query_time": [
[
"10.666552"
]
],
"BASE10NUM": [
[
"10.666552",
"0.000392",
"15",
"24829551",
"1452566321"
]
],
"lock_time": [
[
"0.000392"
]
],
"rows_sent": [
[
"15"
]
],
"rows_examined": [
[
"24829551"
]
],
"timestamp": [
[
"1452566321"
]
],
"query": [
[
"SELECT \n *"
]
]
}
在和 codec/multiline 搭配使用的时候，需要注意一个问题，grok 正则和普通正则一样，
默认是不支持匹配回车换行的。就像你需要 =~ //m 一样也需要单独指定，具体写法是在表达式开始位置加 (?m) 标记。
