时间：2021-07-01 10:21:17 帮助过：21人阅读
有这样一类需求, 将收集的数据, 处理为多个文件, 导入到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两种原生数据导入方式对比, 及使用
标签：mysql数据导入之load和source ...