原声链接mysql_MySQL两种原生数据导入方式对比, 及使用

最新推荐文章于 2024-06-05 10:59:04 发布

weixin_39634480

最新推荐文章于 2024-06-05 10:59:04 发布

阅读量172

点赞数

文章标签：原声链接mysql

本文链接：https://blog.csdn.net/weixin_39634480/article/details/113116339

版权

本文对比了MySQL的两种原生数据导入方式——`load data`和`source`，分析了它们在导入速度控制、错误处理和自增主键等方面的特性，并讨论了如何通过Bash脚本进一步控制数据导入过程，以降低主从复制延迟导致的监控报警问题。

摘要由CSDN通过智能技术生成

有这样一类需求,将收集的数据,处理为多个文件,导入到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 ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';