MySQL入門--導出和導入數據

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);