从 MySQL-slow.log 文件中删除重复的行

Question

我有一个名为mysql-slow.log的文件。

我想删除重复的查询。

搜索行是选择帐户，accounts_cstm从帐户左加入accounts_cstm accounts.id = accounts_cstm.ID_C 其中 customer_id_c = '' 和删除=0 限制 0,1;

下面是示例文件

# Time: 180110 11:31:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.224965  Lock_time: 0.000052 Rows_sent: 1  Rows_examined: 92610
SET timestamp=1515564066;
SELECT b_invoice.*,b_invoice_cstm.* FROM b_invoice  LEFT JOIN b_invoice_cstm ON b_invoice.id = b_invoice_cstm.id_c  WHERE order_id_c = '212959' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:38:12
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.041713  Lock_time: 0.000048 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564492;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:39:02
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.124880  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564542;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:39:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.016269  Lock_time: 0.000026 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564558;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:40:11
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.261057  Lock_time: 0.000040 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564611;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:40:13
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.862533  Lock_time: 0.000050 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564613;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;

输出文件应该是

#Time: 180110 11:31:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.224965  Lock_time: 0.000052 Rows_sent: 1  Rows_examined: 92610
SET timestamp=1515564066;
SELECT b_invoice.*,b_invoice_cstm.* FROM b_invoice  LEFT JOIN b_invoice_cstm ON b_invoice.id = b_invoice_cstm.id_c  WHERE order_id_c = '212959' AND deleted=0 LIMIT 0,1;
# Time: 180110 11:38:12
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.041713  Lock_time: 0.000048 Rows_sent: 0  Rows_examined: 101355
SET timestamp=1515564492;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;

注意：查询前还要删除上 3 行

#Time
#User
#Query_time

用于删除单行的 mysql 命令是

sudo sed -i '/^SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;/d' mysql-slow.log

Answer 1

这个可能会帮助你:)

awk '/^SET/{ s = $0; next }/^SELECT/&& !(a[$0]++){ print s; print $0 }' infile

输出：

SET timestamp=1515564066;
SELECT b_invoice.*,b_invoice_cstm.* FROM b_invoice  LEFT JOIN b_invoice_cstm ON b_invoice.id = b_invoice_cstm.id_c  WHERE order_id_c = '212959' AND deleted=0 LIMIT 0,1;
SET timestamp=1515564492;
SELECT accounts.*,accounts_cstm.* FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c  WHERE customer_id_c = '' AND deleted=0 LIMIT 0,1;

第一部分/^SET/{ s = $0; next }查找以 SET 开头的每一行。当一行以 set 开头时，整行 ($0) 存储在变量 s 中。第二部分/^SELECT/&& !(a[$0]++){ print s; print $0 }'查找以 SELECT 开头且不在数组 a 中的行。如果一行不在 a 中，则将添加该行，并打印变量 s 和以 SELECT 开头的整行。