DB：MySQL 5.6.16

CentOS：CentOS release 6.3 (Final)

當insert語句通過空格跨行輸入的時候，如何提取完整的insert語句！

創建一個空表：

mysql> create table yoon as select * from sakila.actor where 1=0;

Query OK, 0 rows affected (0.06 sec)

Records: 0? Duplicates: 0? Warnings: 0

查看表名：

mysql> show tables;

+----------------+

| Tables_in_yoon |

+----------------+

| yoon? ? ? ? ? |

+----------------+

1 row in set (0.00 sec)

查看數據：

mysql> select * from yoon;

Empty set (0.00 sec)

查看表結構：

mysql> desc yoon;

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| Field? ? ? | Type? ? ? ? ? ? ? ? | Null | Key | Default? ? ? ? ? | Extra? ? ? ? ? ? ? ? ? ? ? |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

| actor_id? ? | smallint(8) unsigned | NO? |? ? | 0? ? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| first_name? | varchar(45)? ? ? ? ? | NO? |? ? | NULL? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| last_name? | varchar(45)? ? ? ? ? | NO? |? ? | NULL? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? ? ? ? ? ? |

| last_update | timestamp? ? ? ? ? ? | NO? |? ? | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------------+----------------------+------+-----+-------------------+-----------------------------+

4 rows in set (0.00 sec)

夸行方式插入測試數據：

mysql> insert into yoon

->

-> values

->

-> (1,'YOON','HANK',2006-02-15 04:34:33)

->

-> ;

查看mysql-binlog日志：

[root@hank-yoon data]# ls

auto.cnf? ? ? ? ? ? hank? ? ? ibdata1? ib_logfile0? ib_logfile2? mysql-bin.000043? mysql-bin.000045? performance_schema? test? yoon.sql

binlog-rollback.pl? hank.sql? ibdata2? ib_logfile1? mysql? ? ? ? mysql-bin.000044? mysql-bin.index? sakila? ? ? ? ? ? ? yoon

將binlog數據轉換到yoon.sql：

[root@hank-yoon data]# mysqlbinlog mysql-bin.000045 > yoon.sql

過濾出insert語句：

[root@hank-yoon data]# more yoon.sql | grep --ignore-case -E 'insert' -A2 -B2 | grep yoon > hank.sql

查看insert語句，發現跨行插入的SQL語句截至到insert into yoon就結束：

[root@hank-yoon data]# cat hank.sql

insert into yoon (first_name,last_name) select first_name,last_name from hank

insert into yoon

通過以下命令，可以查看完整的SQL語句，即使是跨行插入，分好(;)都給你帶上：

[root@hank-yoon data]# sed? -n? "/insert into yoon/,/;/p"? yoon.sql? |sed? 's#\/\*!\*\/##'

insert into yoon (first_name,last_name) select first_name,last_name from hank

;

insert into yoon

values

(1,'YOON','HANK','2006-02-15 04:34:33')

;