首页 > 代码库 > MySQL两种原生数据导入方式对比, 及使用
MySQL两种原生数据导入方式对比, 及使用
有这样一类需求, 将收集的数据, 处理为多个文件, 导入到MySQL数据库中对应的表内, 之后通过WEB应用展示给用户. 由于数据库是主从复制结构, 导入大量数据时, 复制延时比较大, 导致监控系统报警. 作为例行任务, 每次都报警, 确实让人心烦.
如何才能不报警呢… 大致有两个思路, 一个是从监控入手, 一个是尽量不让数据导入造成延时. 对于前者, 可调整数据库监控脚本, 导入数据过程有延时, 而不进行报警. 但有个问题, 对于使用从库对的应用, 可能由于延时, 获取不到正确的数据. 这样来看, 后者才是比较合理的解决思路.
要怎样导入数据呢, 首先要能控制导入速度, 这样才能尽量不延时; 其次遇到错误要终止导入, 以防当前错误影响后面的数据;最后要能提示出错的位置和原因, 以便修正.
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 ‘,‘ OPTIONALLYENCLOSED BY ‘"‘ LINES TERMINATED BY ‘\n‘;
本文出自 “感动自己” 博客，请务必保留此出处http://coveringindex.blog.51cto.com/12533373/1955157
MySQL两种原生数据导入方式对比, 及使用