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两种原生数据导入方式有何区别及用法这篇文章，大家觉得怎么样？如果想要了解更多相关，可以继续关注我们的数据库板块。