mysql 匯出匯入資料 -csv

標籤：選擇   select   padding   匯入   records   val   nes   span   war   

MySql資料庫匯出csv檔案命令：

mysql> select first_name,last_name,email from account into outfile ‘e://output1.csv‘ fields terminated by ‘,‘optionally enclosed by ‘‘lines terminated by ‘/n‘;

csv檔案效果：

 

sunny	Grigoryan	[email protected]
Jon	Siegal	[email protected]
Joe	Siegal	[email protected]
alejandro	medina	[email protected]

 

 

cvs檔案匯入MySql資料庫命令: 
mysql> load data local infile ‘e://input1.csv‘ into table test1 fields termin
ated by ‘,‘lines terminated by ‘/n‘(first_name,last_name,email);
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 69  Deleted: 0  Skipped: 68  Warnings: 0

mysql> select * from test1;
+----+------------+-----------+--------------------------+
| id | first_name | last_name | email                    |
+----+------------+-----------+--------------------------+
 | 0 | sunny      | Grigoryan | [email protected]
+----+------------+-----------+--------------------------+

FIELDS TERMINATED BY ---- 欄位終止字元 
OPTIONALLY ENCLOSED BY ---- 封套符 
LINES TERMINATED BY ---- 行終止符

 

 

通過mysql用戶端shell串連到伺服器，選擇使用的資料庫，輸入sql代碼： 

select * from test_info   
into outfile ‘/tmp/test.csv‘   
fields terminated by ‘,‘ optionally enclosed by ‘"‘ escaped by ‘"‘   
lines terminated by ‘\r\n‘; 

裡面最關鍵的部分就是格式參數

這個參數是根據RFC4180文檔設定的，該文檔全稱Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中詳細描述了CSV格式，其要點包括：
(1)欄位之間以逗號分隔，資料行之間以\r\n分隔；

(2)字串以半形雙引號包圍，字串本身的雙引號用兩個雙引號表示。

通過執行上述代碼，便可以將需要的資料以csv格式匯出到執行的檔案中。

 

另外，MySQL中匯入CSV格式資料的sql代碼如下:

load data infile ‘/tmp/test.csv‘  
into table test_info   
fields terminated by ‘,‘  optionally enclosed by ‘"‘ escaped by ‘"‘  
lines terminated by ‘\r\n‘;

 

 

當csv檔案中的每一行記錄的列數小於資料庫表時，以下語句將十分有用：

load data infile "/tmp/appleAppV2_search_day_20110525.csv" into table apple_search_log fields terminated by ‘;‘ lines terminated by ‘/n‘(apptypeId,keyword,searchTime,model) ;

藍色字部分是亮點。如果資料表包含自增欄位，例如自增的ID， 這個語句將十分有用。

mysql 匯出匯入資料 -csv