MySQL匯出和匯入資料

資料庫匯出可用於將資料庫複製到另一個伺服器。可以將資料庫傳輸到在另一臺主機上執行的伺服器，這是最典型的資料匯出任務。也可以將資料傳輸到執行在同一主機上的不同伺服器。如果正在針對新版本 MySQL 測試伺服器，並且想使用生產伺服器中的實際資料，則可以執行此操作。還可以將資料裝入外部應用程式，資料匯出也可用於將資料從一個 RDBMS 傳輸到另一個 RDBMS 。

完成匯出和匯入操作的兩種最常用的方法是：

Ø   使用 SELECT ... INTO OUTFILE 將資料匯出到檔案

Ø   使用 LOAD DATA INFILE 語句從檔案中匯入資料

1.1.        使用 SELECT...INTO OUTFILE 匯出資料

可以對 SELECT 語句使用 INTO OUTFILE 子句，將結果集直接寫入檔案。要以這種方式使用 SELECT ，請將 INTO OUTFILE 子句置於 FROM 子句之前。

檔名稱指示輸出檔案的位置。 MySQL 會將檔案寫入伺服器主機上的指定路徑。輸出檔案具有以下特徵：檔案將寫入伺服器主機，而不是透過網路傳送到客戶機。檔案不能已存在。伺服器將在伺服器主機上寫入新檔案。

要執行 SELECT … INTO OUTFILE 語句，必須使用有 FILE 許可權的帳戶連線到伺服器。 MySQL 使用如下許可權建立檔案：執行 MySQL 程式的帳戶將擁有檔案、檔案對所有使用者可讀。

針對語句所選的每一行，檔案中都包含對應的一行。預設情況下，列值由製表符分隔，而行在換行符處終止。

         語法：

SELECT ... INTO OUTFILE 'file_name'

        [CHARACTER SET charset_name]

        [export_options]

 

export_options:

    [{FIELDS | COLUMNS}

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char']

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

 

1)        資料檔案格式說明符

SELECT...INTO OUTFILE 採用預設的資料檔案格式 TSV ，其中列值由製表符分隔，記錄由換行符終止。要使用 SELECT...INTO OUTFILE 寫入使用不同分隔符或終結符的檔案，請使用 FIELDS 和 LINES 子句指定輸出格式。

Ø   FIELDS 子句指定如何顯示列。

l   TERMINATED BY 指定欄位分隔符，預設情況下是製表符。

l   ENCLOSED BY 指定如何引住列值。預設設定為不使用引號（即，預設值為空字串）。

l   ESCAPED BY 指明當表示換行符或製表符之類的非列印字元時要使用的轉義符。預設轉義符是反斜槓 (\) 字元。

Ø   LINES TERMINATED BY 子句指定行分隔符，預設情況下是換行符。

MySQL 使用反斜槓來轉義特殊字元，所以必須將換行符和製表符之類的字元分別表示為“ \n ”和“ \t ”。同樣，要表示反斜槓字元，則必須將其轉義為如下所示：“ \\ ”。

 

2)        跳脫字元

命令列終結符包括換行符和回車 / 換行符對。預設的換行符終結符常見於 Linux 系統，而回車 / 換行符對常見於 Windows 系統。

ESCAPED BY

ESCAPED BY 子句僅控制資料檔案中值的輸出；它不會更改 MySQL 解釋語句中特殊字元的方式。例如，如果透過寫入 ESCAPED BY '@' 指定資料檔案轉義符為“ @ ”，並不表示您必須使用“ @ ”來轉義語句中其他的特殊字元。您必須使用 MySQL 的轉義符（反斜槓： \ ）來轉義語句中的特殊字元，使用 LINES TERMINATED BY '\r\n' （而不是 LINES TERMINATED BY '@r@n' ）之類的語法。

跳脫字元含義

\N NULL

\0 NULL （零）位元組

\b 退格

\n 換行

\r 回車

\s 空格

\t 製表符

\ ′ 單引號

\" 雙引號

\\ 反斜槓

以上所有跳脫字元可以單獨使用或者在較長的字串中使用，但 \N 除外，該序列只有在單獨出現時才用作 NULL 。

3)        用法示例

mysql> select * into outfile 't1.tsv' from t1;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

mysql> show variables like 'secure%';

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

| Variable_name    | Value                 |

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

| secure_auth      | ON                    |

| secure_file_priv | /var/lib/mysql-files/ |

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

2 rows in set (0.05 sec)

 

mysql> select * into outfile '/var/lib/mysql-files/t1.tsv' from t1;

Query OK, 7 rows affected (0.01 sec)

注意：如果配置了 secure_file_priv 則必須將匯出檔案匯出到該目錄，否則報錯 ERROR 1290 ；

[root]# cat /var/lib/mysql-files/t1.tsv

100     a

200     a

300     a

mysql> select * into outfile '/var/lib/mysql-files/t1a.tsv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

    -> from t1;

Query OK, 7 rows affected (0.00 sec)

[root]# cat t1a.tsv

100,"a"

200,"a"

300,"a"

 [root]#

 

1.2.        使用 LOAD DATA INFILE 匯入資料

LOAD DATA INFILE 語句將資料檔案中的值讀入表。 LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆向操作。如果要匯入的資料檔案包含使用製表符或逗號分隔的表資料，請使用 LOAD DATA INFILE 命令。此類檔案最重要的特徵是：

n   列值分隔符

n   行分隔符

n   用於引住值的字元（例如：引號）

n   檔案中是否指定了列名

n   匯入前是否有標頭指示要跳過的錶行

n   檔案在檔案系統中的位置

n   訪問檔案是否需要有相應許可權

n   列的順序

n   檔案和表中的列數是否匹配

語法：

LOAD DATA

    [LOW_PRIORITY | CONCURRENT] [LOCAL]

    INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [PARTITION (partition_name [, partition_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

        [, col_name_or_user_var] ...)]

    [SET col_name={expr | DEFAULT},

        [, col_name={expr | DEFAULT}] ...]

示例：

LOAD DATA INFILE '/tmp/City.txt' FIELDS TERMINATED BY ',' INTO TABLE City;

 

1)        跳過或轉換輸入資料

Ø   忽略資料檔案行

要忽略資料檔案的開始部分，可以使用 IGNORE n LINES 子句，其中， n 是一個整數，表示要忽略的輸入行數。當檔案以列名行（而不是資料值行）開始時，請使用此子句。

mysql> LOAD DATA INFILE '/tmp/City.txt'

-> INTO TABLE City IGNORE 2 LINES;

 

Ø   忽略或轉換列值

您可在列列表和可選的 SET 子句中提供使用者變數，該子句的語法類似於 UPDATE 語句中的 SET 子句。在將從檔案中讀取的資料值插入表中之前， LOAD DATA INFILE 將對其進行轉換，處理使用者變數中所包含的值。要將輸入資料列分配給使用者變數而不是表列，請以列列表的形式提供使用者變數的名稱。如果將列分配給 SET 表示式中未使用的使用者變數，則語句將忽略該列中的值，不會將其插入表中。

LOAD DATA INFILE '/tmp/City.txt'

INTO TABLE City ( @skip, @Name,CountryCode, @District, Population)

SET name=CONCAT(@Name,' ',@District);

在語句列的列表中指定使用者變數（而不是列名稱），透過使用 SET 子句（可選）轉換列值，該語句將忽略 SET 表示式中未使用的變數的值。

 

2)        重複記錄

使用 INSERT 或 REPLACE 語句向表新增新行時，可以控制語句對包含表中已有鍵的行的處理方法。可以允許語句生成錯誤，可以使用 IGNORE 子句放棄該行，也可以使用 ON DUPLICATE KEY UPDATE 子句修改現有的行。

LOAD DATA INFILE 提供了對重複行的相同級別控制，即透過使用兩個修飾符關鍵字 IGNORE （放棄包含重複鍵的行）和 REPLACE （替換為檔案中包含相同鍵的版本）；但是，其重複項處理行為根據資料檔案是位於伺服器主機上還是位於客戶機主機上而稍有不同，所以使用 LOAD DATA INFILE 時，必須考慮資料檔案的位置。

3)        從伺服器主機裝入檔案

裝入位於伺服器主機上的檔案時， LOAD DATA INFILE 對包含重複唯一鍵的行的處理方法如下：

Ø   預設情況下，輸入記錄造成重複鍵違規將產生一個錯誤；不會裝入資料檔案的剩餘部分。該點之前的已處理記錄將被裝入表中。

Ø   如果在檔名後提供 IGNORE 關鍵字，將忽略造成重複鍵違規的新記錄，並且語句不會生成錯誤。 LOAD DATA INFILE 將處理整個檔案，裝入所有不包含重複鍵的記錄，並放棄剩餘記錄。

Ø   如果在檔名後提供 REPLACE 關鍵字，造成重複鍵違規的新記錄將替換表中現存的包含重複鍵值的任何記錄。 LOAD DATA INFILE 將處理整個檔案，將檔案中的所有記錄裝入表中。

4)        從客戶機主機裝入檔案

從客戶機主機裝入檔案時，預設情況下 LOAD DATA INFILE 將忽略包含重複鍵的記錄。即，預設行為與指定 IGNORE 選項時相同。這是因為客戶機 / 伺服器協議不允許在傳輸開始後中斷從客戶機主機到伺服器的資料檔案傳輸，因此不方便在操作過程中中止操作。

5)        用法示例

mysql>use test

mysql> CREATE TABLE `t1` (

    ->   `f1` int(11) DEFAULT NULL,

    ->   `f2` varchar(20) DEFAULT NULL

    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    -> /*!50100 PARTITION BY HASH (f1)

    -> PARTITIONS 4 */ ;

Query OK, 0 rows affected (0.17 sec)

匯入預設格式 t1.tsv 檔案

mysql> LOAD DATA local INFILE '/var/lib/mysql-files/t1.tsv' IGNORE INTO TABLE t1;

Query OK, 7 rows affected (0.01 sec)

Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql>

mysql>

mysql> select * from t1;

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

| f1   | f2   |

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

|  100 | a    |

|  200 | a    |

|  300 | a    |

|  400 | a    |

|    1 | a    |

|  101 | a    |

|  111 | b    |

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

7 rows in set (0.00 sec)

 

匯入指定格式 t1.tsv 檔案

mysql> LOAD DATA local INFILE '/var/lib/mysql-files/t1a.tsv' IGNORE INTO TABLE t1

    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines ;

Query OK, 6 rows affected (0.00 sec)

Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

 

mysql>

示例：

LOAD DATA local INFILE '/Users/xxx/Downloads/loaddata.txt' IGNORE INTO TABLE testLoadData

FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines (username, age, description);