mysql中的資料匯入與匯出

標籤：mysql   知識   

為了普及mysql的基本知識，特意弄了這個章節，主要是發現第一次接觸的人都不知道怎麼弄，或者看不懂，所以這裡就詳細說下吧

============================================================

資料匯入

1.mysqlimport命令列匯入資料

在使用mysqlimport命令匯入資料時，資料來源檔案名稱要和目標表一致，不想改檔案名稱的話，可以複製一份建立臨時檔案，樣本如下。

建立一個文本users.txt，內容如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/59/wKiom1VG8VvyJSnwAAB6YYoyAIw669.jpg" title="1.png" alt="wKiom1VG8VvyJSnwAAB6YYoyAIw669.jpg" />

建立一個表users

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/55/wKioL1VG8q_hcDpfAACUWsehht4443.jpg" title="2.png" alt="wKioL1VG8q_hcDpfAACUWsehht4443.jpg" />

使用mysqlimport將users.txt中資料匯入users表

PS F:\> mysqlimport -u root -p123456 zz --default-character-set=gbk --fields-terminated-by=‘,‘ f:\users.txtzz.users: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0-----------------------------驗證----------------------------------mysql> select * from users\G*************************** 1. row ***************************   id: 1003 name: 王五email: [email protected]*************************** 2. row ***************************   id: 1001 name: 張三email: [email protected]*************************** 3. row ***************************   id: 1002 name: 李四email: [email protected]

分列，使用--fields-terninated-by參數來指定每列的分隔字元，例如：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/59/wKiom1VG8ZexZtkoAAFBhQ7oYUg243.jpg" title="4.png" alt="wKiom1VG8ZexZtkoAAFBhQ7oYUg243.jpg" />

如果列值中出現了分隔字元，例如 1004"#李#白"#"[email protected]"

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by=‘#‘ --fields-enclosed-by=\"  f:\users.txt

如果遇到一條記錄有多行，則可以使用--lines-terminated-by=name來指定行的結束符

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by=‘#‘ --fields-enclosed-by=\"  --lines-terminated-by=‘xxx\n‘ f:\users.txt

2.使用Load Data語句匯入資料

Load Data 語句的使用文法如下：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name‘    [REPLACE | IGNORE]    INTO TABLE tbl_name    [CHARACTER SET charset_name]    [{FIELDS | COLUMNS}        [TERMINATED BY ‘string‘]        [[OPTIONALLY] ENCLOSED BY ‘char‘]        [ESCAPED BY ‘char‘]    ]    [LINES        [STARTING BY ‘string‘]        [TERMINATED BY ‘string‘]    ]    [IGNORE number {LINES | ROWS}]    [(col_name_or_user_var,...)]    [SET col_name = expr,...]

剛開始看到這個文法嚇了一跳，這麼長，其實沒這麼複雜，一般只需記住LOAD DATA INFILE file_name INTO TABLE tb_name這個即可，樣本：

首先建立一個表sql_users，利用上面的users表複製一下

mysql> create table sql_users as select * from users;Query OK, 1 row affected (0.06 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> truncate table sql_users;Query OK, 0 rows affected (0.00 sec)mysql> select * from sql_users;Empty set (0.00 sec)

文本sql_users.txt

1004#李白#[email protected]1005#杜牧#[email protected]1006#杜甫#[email protected]1007#蘇軾#[email protected]

利用LOAD DATA INFILEE語句匯入資料

mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users fields terminated by ‘#‘;Query OK, 4 rows affected (0.00 sec)Records: 4  Deleted: 0  Skipped: 0  Warnings: 0mysql> select * from sql_users;+------+------+--------------------+| id   | name | email              |+------+------+--------------------+| 1004 | 李白 | [email protected]| 1005 | 杜牧 | [email protected]| 1006 | 杜甫 | [email protected]| 1007 | 蘇軾 | [email protected]  |+------+------+--------------------+4 rows in set (0.00 sec)

 如果在匯入資料時，遇到字串無法識別時，一般都是字元集有問題，使用charset選項即可解決

mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users  fields terminated by ‘#‘;ERROR 1366 (HY000): Incorrect string value: ‘\xC0\xEE\xB0\xD7‘ for column ‘name‘ at row 1--------------------------------字元集不一樣-----------------------mysql> load data infile ‘f:\sql_users.txt‘ into table sql_users  character set gbk fields terminated by ‘#‘;Query OK, 4 rows affected (0.03 sec)Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE命令預設要匯入資料存放在服務上，如果要匯入用戶端的資料，可以指定LOCAL，那麼mysql將從用戶端讀取資料，這樣的方式會比伺服器上操作要慢一點，因為用戶端的資料需要通過網路傳輸到伺服器。

mysql> load data local infile ‘f:\sql_users.txt‘ into table sql_users  fields terminated by ‘#‘;

如果需要忽略與主索引值重複的記錄值或者替換重複值，可以使用IGNORE或REPLACE選項，但是LOAD DATA INFILE命令文法中有兩處IGNORE關鍵字，前面一個是用來此功能的，後面一個用來指定需要忽略的前N條記錄。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/59/wKiom1VG8e3jvNrVAAInfPim2Hc600.jpg" title="5.png" alt="wKiom1VG8e3jvNrVAAInfPim2Hc600.jpg" />

如果不想匯入資料檔案的前N行，使用IGNORE N LINES來處理

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/55/wKioL1VG84CjiNb_AAGOj2zGNhE857.jpg" title="6.png" alt="wKioL1VG84CjiNb_AAGOj2zGNhE857.jpg" />

如果在資料檔案中記錄行頭有某些字元，又不想被匯入，可以使用LINES STARTING BY來解決，但是如果某行記錄不包含這些字元的話，那麼這行記錄也會被忽略。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/6C/59/wKiom1VG8jLAEhrBAAJO-qUkBb0151.jpg" title="7.png" alt="wKiom1VG8jLAEhrBAAJO-qUkBb0151.jpg" />

資料檔案為Excel檔案的處理，首先將Excel檔案儲存為CSV格式，這樣欄位間都是用逗號隔開的，再進行處理。

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/55/wKioL1VG87_hEyfXAAJH9ojE6Ec208.jpg" title="8.png" alt="wKioL1VG87_hEyfXAAJH9ojE6Ec208.jpg" />

資料檔案列值中有特殊符號，使用enclosed by來處理。例如，列值中有分隔字元

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/55/wKioL1VG893ya5IFAAI8hvPpk3E453.jpg" title="8.png" alt="wKioL1VG893ya5IFAAI8hvPpk3E453.jpg" />

資料匯入時分行符號的問題，在上面的樣本中，有幾個資料匯入到表中後，查詢時結果顯示有點彆扭，不知大家注意到了沒。

在Windows系統中，文字格式設定的分行符號有"\r+\n"組成，而在linux系統中，分行符號是"\n"。因此出出現上述問題，解決方案就是指定分行符號LINES TERMINATED BY。

mysql> LOAD DATA INFILE ‘F:\stu.csv‘ INTO TABLE stu CHARACTER SET GBK FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\r\n‘ IGNORE 1 LINES;

表的列數多餘資料檔案中的列數，解決方案就是指定要匯入到表的欄位，如下所示

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/6C/59/wKiom1VG8pPBmfY6AAKwc3y6i-E654.jpg" title="8.png" alt="wKiom1VG8pPBmfY6AAKwc3y6i-E654.jpg" />

如果是表的列數少於資料檔案中的列數呢，解決辦法可以指定使用者變數來接收多餘的列值，如下

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/6C/55/wKioL1VG9CKBG5RVAAI02wZwTwA553.jpg" title="8.png" alt="wKioL1VG9CKBG5RVAAI02wZwTwA553.jpg" />

如果表的列數與資料檔案的不同，且某些欄位類型都不一致，那怎麼解決呢？方法如下：

------------------文本----------------------

PS F:\> MORE .\stu.csv

學號,姓名,班級

4010404,祝小賢,"A1012",20,male,資訊學院

4010405,肖小傑,"A1013",22,female,外院

4010406,鐘小喜,"A1014",24,male,會計學院

4010407,鐘小惠,"A1015",26,female,商學院

--------------------處理-------------------------

mysql> desc stu;   //表結構

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

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

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

| sno    | int(11)     | NO   | PRI | NULL    |       |

| sname  | varchar(30) | YES  |     | NULL    |       |

| class  | varchar(20) | YES  |     | NULL    |       |

| age    | int(11)     | YES  |     | NULL    |       |

| gender | tinyint(4)  | YES  |     | NULL    |       |

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

rows in set (0.01 sec)

mysql> LOAD DATA INFILE ‘F:\stu.csv‘ INTO TABLE stu CHARACTER SET GBK FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘ LINES TER

MINATED BY ‘\r\n‘ IGNORE 1 LINES (SNO,SNAME,CLASS,AGE,@GENDER,@x) SET GENDER=IF(@GENDER=‘MALE‘,1,0);

Query OK, 4 rows affected (0.09 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM STU;

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

| sno     | sname  | class | age  | gender |

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

| 4010404 | 祝小賢 | A1012 |   20 |      1 |

| 4010405 | 肖小傑 | A1013 |   22 |      0 |

| 4010406 | 鐘小喜 | A1014 |   24 |      1 |

| 4010407 | 鐘小惠 | A1015 |   26 |      0 |

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

rows in set (0.00 sec)

資料匯出

資料匯出比較簡單，只要會SELECT ...INTO OUTFILE語句即可，例如

mysql> SELECT * FROM STU INTO OUTFILE "F:\stu_bak.txt"  CHARACTER SET GBK FIELDS TERMINATED BY ‘##‘ LINES TERMINATED BY‘\r\n‘;

Query OK, 4 rows affected (0.00 sec)

-------------------------------stu_bak.txt-----------------------

PS F:\> MORE .\stu_bak.txt

4010404##祝小賢##A1012##20##1

4010405##肖小傑##A1013##22##0

4010406##鐘小喜##A1014##24##1

4010407##鐘小惠##A1015##26##0

還有一個SELECT...INTO DUMPFILE，這個語句也是將資料匯出到檔案，但是不能格式化語句，如FIELDS,LINES這些，它是將資料原汁原味輸出到檔案。但是只能輸出一個記錄，用處不大。

mysql中的資料匯入與匯出