MySQL資料的匯入

brucexia發表於2024-10-17
MySql

我們在帖子MySQL資料的匯出 - brucexia - 部落格園 (cnblogs.com)中講了MySQL資料的匯出，本文講講解MySQL資料的匯入。

MySQL資料的匯入包括使用LOAD DATA INFILE命令匯入和使用mysqlimport命令匯入。

使用LOAD DATA INFILE方式匯入文字檔案

MySQL允許將資料匯出到外部檔案，也可以從外部檔案匯入資料。MySQL提供了一些匯入資料的工具，包括LOAD DATA語句、source命令和mysql命令。LOAD DATA INFILE語句用於高速地從一個文字檔案中讀取行，並輸入一張表中。檔名稱必須為文字字串。

LOAD DATA語句的基本格式如下：

LOAD DATA  INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES]

在LOAD DATA語句中，關鍵字INFILE後面的filename檔案為匯入資料的來源；tablename表示待匯入的資料表名稱；[OPTIONS]為可選引數選項，OPTIONS部分的語法包括FIELDS和LINES子句，其可能的取值有：

FIELDS TERMINATED BY 'value'：設定欄位之間的分隔字元，可以為單個或多個字元，預設情況下為“\t”。

FIELDS [OPTIONALLY] ENCLOSED BY 'value'：設定欄位的包圍字元，只能為單個字元。如果使用了OPTIONALLY，則只有CHAR和VERCHAR等字元資料欄位被包圍。

FIELDS ESCAPED BY 'value'：控制如何寫入或讀取特殊字元，只能為單個字元，即設定跳脫字元，預設值為“\”。

LINES STARTING BY 'value'：設定每行資料開頭的字元，可以為單個或多個字元，預設情況下不使用任何字元。

LINES TERMINATED BY 'value'：設定每行資料結尾的字元，可以為單個或多個字元，預設值為“\n”。

IGNORE number LINES選項表示忽略檔案開始處的行數，number表示忽略的行數。執行LOAD DATA語句需要FILE許可權。

【例11.19】使用LOAD DATA語句將D:\person0.txt檔案中的資料匯入test_db資料庫的person表中，SQL語句如下：

LOAD DATA INFILE 'D:\person0.txt' INTO TABLE test_db.person;

匯入資料之前，要將person表中的資料全部刪除，即登入MySQL，使用DELETE語句刪除person表中的資料：

mysql> USE test_db;
Database changed;
mysql> DELETE FROM person;
Query OK, 10 rows affected (0.00 sec)
從person0.txt檔案中匯入資料，SQL語句如下：
mysql> LOAD DATA  INFILE 'D:\person0.txt' INTO TABLE test_db.person;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

 
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Suse    |  22 | dancer     |
|  3 | Mary    |  24 | Musician   |
|  4 | Willam  |  20 | sports man |
|  5 | Laura   |  25 | NULL       |
|  6 | Evans   |  27 | secretary  |
|  7 | Dale    |  22 | cook       |
|  8 | Edison  |  28 | singer     |
|  9 | Harry   |  21 | magician   |
| 10 | Harriet |  19 | pianist    |
+----+---------+----+-------------+

可以看到，語句執行成功之後，person0.txt檔案中的資料匯入person表中了。

【例11.20】使用LOAD DATA語句將D:\person1.txt檔案中的資料匯入test_db資料庫中的person表中，使用FIELDS選項和LINES選項，要求欄位之間使用“,”間隔，所有欄位值用雙引號括起來，定義跳脫字元為“\'”，每行記錄以“\r\n”結尾，SQL語句如下：

LOAD DATA INFILE 'D:\person1.txt' INTO TABLE test_db.person
FIELDS
TERMINATED BY ','
ENCLOSED BY '\"'
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';

匯入資料之前，使用DELETE語句將person表中的資料全部刪除，執行過程如下：

mysql> DELETE FROM person;
Query OK, 10 rows affected (0.00 sec)
從person1.txt檔案中匯入資料，執行過程如下：
mysql> LOAD DATA  INFILE 'D:\person1.txt' INTO TABLE test_db   .person
    -> FIELDS
    -> TERMINATED BY ','
    -> ENCLOSED BY '\"'
    -> ESCAPED BY '\''
    -> LINES
    -> TERMINATED BY '\r\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0

語句執行成功後，使用SELECT語句檢視person表中的記錄，結果與【例11.19】的相同。

使用mysqlimport命令匯入文字檔案

使用mysqlimport命令可以匯入文字檔案，並且不需要登入MySQL客戶端。mysqlimport命令提供許多與LOAD DATA INFILE語句相同的功能，大多數選項直接對應LOAD DATA INFILE子句。使用mysqlimport命令需要指定所需的選項、匯入的資料庫名稱以及匯入的資料檔案的路徑和名稱。mysqlimport命令的基本語法格式如下：

mysqlimport -u root-p dbname filename.txt [OPTIONS]

dbname為匯入的表所在的資料庫名稱。注意，mysqlimport命令不指定匯入資料庫的表名稱，資料表的名稱由匯入檔名稱確定，即檔名作為表名，匯入資料之前該表必須存在。[OPTIONS]為可選引數選項，其常見的取值有：

--fields-terminated-by= 'value'：設定欄位之間的分隔字元，可以為單個或多個字元，預設情況下為“\t”。

--fields-enclosed-by= 'value'：設定欄位的包圍字元。

--fields-optionally-enclosed-by= 'value'：設定欄位的包圍字元，只能為單個字元，包括CHAR和VERCHAR等字元資料欄位。

--fields-escaped-by= 'value'：控制如何寫入或讀取特殊字元，只能為單個字元，即設定跳脫字元，預設值為“\”。

--lines-terminated-by= 'value'：設定每行資料結尾的字元，可以為單個或多個字元，預設值為“\n”。

--ignore-lines=n：忽視資料檔案的前n行。

【例11.21】使用mysqlimport命令將D盤目錄下的person.txt檔案內容匯入test_db資料庫中，欄位之間使用“,”分隔，字元型別欄位值用雙引號引起來，將跳脫字元定義為“?”，每行記錄以“\r\n”結尾，SQL語句如下：

C:\ >mysqlimport -u root -p test_db D:\person.txt --fields-terminated-by=, --fields-optionally-enclosed-by=\"--fields-escaped-by=?--lines-terminated-by=\r\n

上面的語句要在一行中輸入，語句執行成功後，將把person.txt中的資料匯入資料庫test_db中。

除了前面介紹的幾個選項之外，mysqlimport命令還支援許多別的選項，例如：

--columns=column_list, -c column_list：採用逗號分隔的列名作為其值。列名的順序指示如何匹配資料檔案列和表列。

--compress，-C：壓縮在客戶端和伺服器之間傳送的所有資訊（如果二者均支援壓縮）。

-d，--delete：匯入文字檔案前清空表。

--force，-f：忽視錯誤。例如，當某個文字檔案的表不存在時，繼續處理其他檔案。不使用--force時，如果表不存在，則mysqlimport退出。

--host=host_name，-h host_name：將資料匯入給定主機上的MySQL伺服器。預設主機是localhost。

--ignore，-i：參見--replace選項的描述。

--ignore-lines=n：忽視資料檔案的前n行。

--local，-L：從本地客戶端讀入輸入檔案。

--lock-tables，-l：處理文字檔案前鎖定所有表，以便寫入。這樣可以確保所有表在伺服器上保持同步。

--password[=password]，-p[password]：連線伺服器時使用的密碼。如果使用短選項形式（-p），則選項和密碼之間不能有空格。如果在命令列中--password或-p選項後面沒有密碼值，則提示輸入一個密碼。

--port=port_num，-P port_num：用於連線的TCP/IP埠號。

--protocol={TCP | SOCKET | PIPE | MEMORY}：使用的連線協議。

--replace和--ignore選項控制複製唯一鍵值已有記錄的輸入記錄的處理方式。如果指定--replace，新行替換有相同的唯一鍵值的已有行；如果指定--ignore，複製已有的唯一鍵值的輸入行將被跳過，不發生替換；如果不指定這兩個選項，當發現一個複製鍵值時會出現一個錯誤，並且忽視文字檔案的剩餘部分。

--silent，-s：沉默模式。只有出現錯誤時才輸出資訊。

--user=user_name，-u user_name：連線伺服器時MySQL使用的使用者名稱。

--verbose，-v：冗長模式。列印出程式操作的詳細資訊。

--version，-V：顯示版本資訊並退出。

相關文章