MySQL兩種原生數據導入方式有何區別及用法

不知道大家之前對類似MySQL兩種原生數據導入方式有何區別及用法的文章有無了解，今天我在這里給大家再簡單的講講。感興趣的話就一起來看看正文部分吧，相信看完MySQL兩種原生數據導入方式有何區別及用法你一定會有所收獲的。

MySQL中有2種原生的數據導入方式, load和source. 先看下兩種方式的過程和特點.

為演示方便, 使用測試表tb1, 表結構如下:

mysql> SHOW CREATE TABLE tb1\G

*************************** 1. row***************************

       Table:tb1

Create Table: CREATE TABLE `tb1` (

  `id`bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `username`varchar(20) NOT NULL DEFAULT '',

  `age`tinyint(3) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY(`id`),

  UNIQUE KEY`uniq_username` (`username`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

表中有若干測試數據:

mysql> SELECT * FROM tb1;

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

| id | username | age |

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

|  1 |aa      | 22 |

|  2 |bb      | 20 |

|  3 |cc      | 24 |

|  4 |dd      | 20 |

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

4 rows in set (0.00 sec)

將tb1表中數據導出成CSV格式的文件tb1.csv:

mysql> SELECT * FROM tb1 INTO OUTFILE"/tmp/tb1.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"' LINES TERMINATED BY '\n';

Query OK, 4 rows affected (0.00 sec)

tb1.csv的內容是:

1,"aa",22

2,"bb",20

3,"cc",24

4,"dd",20

將tb1表TRUNCATE后, load導入:

mysql> TRUNCATE TABLE tb1;

Query OK, 0 rows affected (0.02 sec)

mysql --login-path=mytest test --execute="LOADDATA INFILE '/tmp/tb1.csv' INTO TABLE tb1 FIELDS TERMINATED BY ',' OPTIONALLYENCLOSED BY '\"' LINES TERMINATED BY '\n'"

為測試報錯, 把tb1.csv文件修改為如下(第1, 4行):

9,"ff",22

2,"bb",20

3,"cc",24

14,"gg",25

load導入出錯時, 會終止導入過程, 提示出錯位置和原因, 但這個位置行并不能直接對應到原文件中(應為at line 2):

mysql --login-path=mytest test --execute="LOADDATA INFILE '/tmp/tb1.csv' INTO TABLE tb1 FIELDS TERMINATED BY ',' OPTIONALLYENCLOSED BY '\"' LINES TERMINATED BY '\n'"

ERROR 1062 (23000) at line 1: Duplicate entry '2'for key 'PRIMARY'

查看tb1表的數據, 沒有變化:

mysql> SELECT * FROM tb1;

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

| id | username | age |

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

|  1 |aa      | 22 |

|  2 |bb      | 20 |

|  3 |cc      | 24 |

|  4 |dd      | 20 |

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

4 rows in set (0.00 sec)

這里可看出, load導入數據時, 遇到錯誤會立刻終止, 提示錯誤的位置和原因, 出錯之前的數據也不會導入.

導入速度如何控制呢, 暫無辦法; 另外一點, load導入數據時, 要指定自增主鍵值, 這在數據表中已有數據的情況下, 可能會遇到麻煩.

接著看下source的表現, 將tb1表中數據dump成SQL文件tb1.sql(這里只需要INSERT語句):

mysqldump --login-path=mytest --add-drop-database--skip-add-drop-table --add-drop-trigger --complete-insert--default-character-set=utf8mb4 --events --skip-extended-insert --force--hex-blob --no-create-db --no-create-info --quick --result-file=tb1.sql--routines --single-transaction --triggers test tb1

tb1.sql的內容是:

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(1,'aa',22);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(2,'bb',20);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(3,'cc',24);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(4,'dd',20);

將tb1表TRUNCATE后, source導入:

mysql> TRUNCATE TABLE tb1;

Query OK, 0 rows affected (0.02 sec)

mysql --login-path=mytest test --execute="source/tmp/tb1.sql"

為測試報錯, 把tb1.sql文件修改為如下(第1, 4行):

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(9,'ff',22);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(2,'bb',20);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(3,'cc',24);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(14,'gg',25);

source導入出錯時, 會終止導入過程, 提示出錯位置和原因:

mysql --login-path=mytest test --execute="source/tmp/tb1.sql"

ERROR 1062 (23000) at line 2 in file:'/tmp/tb1.sql': Duplicate entry '2' for key 'PRIMARY'

查看tb1表的數據, 發現報錯前的數據導入了:

mysql> SELECT * FROM tb1;

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

| id | username | age |

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

|  1 |aa      | 22 |

|  2 |bb      | 20 |

|  3 |cc      | 24 |

|  4 |dd      | 20 |

|  9 |ff       | 22 |

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

5 rows in set (0.00 sec)

這里可看出, source導入數據時, 遇到錯誤會立刻終止, 提示錯誤的位置和原因, 出錯之前的數據會被導入.

再看下source是否解決了load存在的另外兩個問題:

如何控制數據導入速度, 可在SQL文件中加入SELECT SLEEP(N)暫停導入, 能起到緩解延時作用.

還有個自增主鍵的問題, 可將數據文件中的INSERT語句做如下處理, 去除主鍵字段, 或將其值設置為NULL:

INSERT INTO `tb1` (`username`, `age`) VALUES('ff',22);

INSERT INTO `tb1` (`id`, `username`, `age`) VALUES(NULL,'ff',22);

經過對比, 使用source可以更好控制數據的導入過程(另外, 對于使用MySQL命令行工具重定向導入, 如mysql < filename.sql, 該方式其實和source是一樣的).

選用source后, 還是會遇到延時等問題, 若想再進一步控制導入過程, 只能借助Bash腳本等加入檢測邏輯了, 如在導入下一個文件時, 先檢查若存在延時, 則腳本中sleep N暫停導入, 又若出現錯誤, 可通過郵件進行通知, 在腳本中可定義各種情況下的處理方式了. 稍后我也會整理Bash編程的最佳實踐，感興趣可關注訂閱號”數據庫最佳實踐”(DBBestPractice).

寫在最后, 前面測試load, 使用SELECT ... INTO OUTFILE將數據導出為CSV格式, 該方式導出少量數據, 還是非常方便的, 只是若數據中包含中文, 使用Excel打開若遇到亂碼, 可嘗試導出時, 指明字符集:

SELECT * FROM tb1 INTO OUTFILE "/tmp/tb1.csv" CHARACTER SET GBK FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

看完MySQL兩種原生數據導入方式有何區別及用法這篇文章，大家覺得怎么樣？如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。