MySQL兩種原生數(shù)據(jù)導(dǎo)入方式有何區(qū)別及用法

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

MySQL中有2種原生的數(shù)據(jù)導(dǎo)入方式, load和source. 先看下兩種方式的過程和特點.

為演示方便, 使用測試表tb1, 表結(jié)構(gòu)如下:

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)

表中有若干測試數(shù)據(jù):

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表中數(shù)據(jù)導(dǎo)出成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的內(nèi)容是:

1,"aa",22

2,"bb",20

3,"cc",24

4,"dd",20

將tb1表TRUNCATE后, load導(dǎo)入:

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導(dǎo)入出錯時, 會終止導(dǎo)入過程, 提示出錯位置和原因, 但這個位置行并不能直接對應(yīng)到原文件中(應(yīng)為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表的數(shù)據(jù), 沒有變化:

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導(dǎo)入數(shù)據(jù)時, 遇到錯誤會立刻終止, 提示錯誤的位置和原因, 出錯之前的數(shù)據(jù)也不會導(dǎo)入.

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

接著看下source的表現(xiàn), 將tb1表中數(shù)據(jù)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的內(nèi)容是:

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導(dǎo)入:

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導(dǎo)入出錯時, 會終止導(dǎo)入過程, 提示出錯位置和原因:

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表的數(shù)據(jù), 發(fā)現(xiàn)報錯前的數(shù)據(jù)導(dǎo)入了:

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導(dǎo)入數(shù)據(jù)時, 遇到錯誤會立刻終止, 提示錯誤的位置和原因, 出錯之前的數(shù)據(jù)會被導(dǎo)入.

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

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

還有個自增主鍵的問題, 可將數(shù)據(jù)文件中的INSERT語句做如下處理, 去除主鍵字段, 或?qū)⑵渲翟O(shè)置為NULL:

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

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

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

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

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

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

看完MySQL兩種原生數(shù)據(jù)導(dǎo)入方式有何區(qū)別及用法這篇文章，大家覺得怎么樣？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。