[MySQL] 匯出 & 匯入

使用 mysqldump 會產生 SQL 語法，以下是從官網介紹直接撈出資料的方式，用於大量匯入會比較快。

$ mysqldump db1 table1 > table1.sql
...
INSERT INTO `table1` VALUES ('0000-00-00 00:00:00',1,NULL,'apple'),('0000-00-00 00:00:00',2,'tw','apple');
...

$ mysql -u user1 table1 < table1.sql


mysql> SELECT * INTO OUTFILE '/tmp/table1.txt' FROM table1;

0000-00-00 00:00:00^I1^I\N^Iapple
0000-00-00 00:00:00^I2^Itw^Iapple

mysql> SELECT * INTO OUTFILE '/tmp/table1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM table1;

"0000-00-00 00:00:00",1,\N,"apple"
"0000-00-00 00:00:00",2,"tw","apple"


mysql> LOAD DATA INFILE 'table1.txt' REPLACE INTO TABLE table1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

參考資料：

• MySQL 5.5 Reference Manual 13.2.9.1 SELECT ... INTO Syntax
• MySQL 5.5 Reference Manual 13.2.6 LOAD DATA INFILE Syntax