MySQL导入数据的三种方案

目标

如下是待入库的数据文件，共 41510 条记录。

[xuej@localhost Downloads]$ head vehicle_area_import_data.csv 
车牌号,省份,城市,区县,街镇,是否可服务
沪A123456,北京市,北京城区,东城区,东华门街道,是
沪A123456,北京市,北京城区,东城区,景山街道,是
沪A123456,北京市,北京城区,东城区,交道口街道,是
沪A123456,北京市,北京城区,东城区,安定门街道,是
沪A123456,北京市,北京城区,东城区,北新桥街道,是
沪A123456,北京市,北京城区,东城区,东四街道,是
沪A123456,北京市,北京城区,东城区,朝阳门街道,是
沪A123456,北京市,北京城区,东城区,建国门街道,是
沪A123456,北京市,北京城区,东城区,东直门街道,是

准备

创建数据库表

CREATE TABLE `vehicle_area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `plate_num` varchar(45) DEFAULT NULL,
  `province` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `district` varchar(45) DEFAULT NULL,
  `town` varchar(45) DEFAULT NULL,
  `available` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

方案

方案1：逐个导入脚本方案

SQL预览

[xuej@localhost Downloads]$ head vehicle_area_import_data_one_by_one.sql 
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','东华门街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','景山街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','交道口街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','安定门街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','北新桥街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','东四街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','朝阳门街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','建国门街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','东直门街道','是');
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES ('沪A123456','北京市','北京城区','东城区','和平里街道','是');

执行脚本1次（含手工录入密码）

[xuej@localhost Downloads]$ start_time=$(date +%s) && echo "StartTime: "$start_time && mysql -uroot -p demo < vehicle_area_import_data_one_by_one.sql && end_time=$(date +%s) && echo "EndTime: "$end_time && echo "Cost: "`expr $end_time - $start_time`" sec";
StartTime: 1695456616
Enter password: 
EndTime: 1695456685
Cost: 69 sec

执行脚本1次（免密执行）

[xuej@localhost Downloads]$ start_time=$(date +%s) && echo "StartTime: "$start_time && mysql demo < vehicle_area_import_data_one_by_one.sql && end_time=$(date +%s) && echo "EndTime: "$end_time && echo "Cost: "`expr $end_time - $start_time`" sec";
StartTime: 1695457612
EndTime: 1695457678
Cost: 66 sec

方案2：批量导入脚本方案

SQL预览

[xuej@localhost Downloads]$ head vehicle_area_import_data_in_batch.sql 
INSERT INTO `demo`.`vehicle_area` (`plate_num`,`province`,`city`,`district`,`town`,`available`) VALUES 
('沪A123456','北京市','北京城区','东城区','东华门街道','是'),
('沪A123456','北京市','北京城区','东城区','景山街道','是'),
('沪A123456','北京市','北京城区','东城区','交道口街道','是'),
('沪A123456','北京市','北京城区','东城区','安定门街道','是'),
('沪A123456','北京市','北京城区','东城区','北新桥街道','是'),
('沪A123456','北京市','北京城区','东城区','东四街道','是'),
('沪A123456','北京市','北京城区','东城区','朝阳门街道','是'),
('沪A123456','北京市','北京城区','东城区','建国门街道','是'),
('沪A123456','北京市','北京城区','东城区','东直门街道','是'),

执行脚本3次

mysql> source vehicle_area_import_data_in_batch.sql
Query OK, 41510 rows affected (0.32 sec)
Records: 41510  Duplicates: 0  Warnings: 0

mysql> source vehicle_area_import_data_in_batch.sql
Query OK, 41510 rows affected (0.35 sec)
Records: 41510  Duplicates: 0  Warnings: 0

mysql> source vehicle_area_import_data_in_batch.sql
Query OK, 41510 rows affected (0.31 sec)
Records: 41510  Duplicates: 0  Warnings: 0

方案3：加载本地csv文件脚本方案

执行脚本3次

mysql> LOAD DATA LOCAL INFILE '/home/xuej/Downloads/vehicle_area_import_data.csv' INTO TABLE vehicle_area CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 41510 rows affected, 65535 warnings (0.17 sec)
Records: 41510  Deleted: 0  Skipped: 0  Warnings: 83020

mysql> LOAD DATA LOCAL INFILE '/home/xuej/Downloads/vehicle_area_import_data.csv' INTO TABLE vehicle_area CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 41510 rows affected, 65535 warnings (0.20 sec)
Records: 41510  Deleted: 0  Skipped: 0  Warnings: 83020

mysql> LOAD DATA LOCAL INFILE '/home/xuej/Downloads/vehicle_area_import_data.csv' INTO TABLE vehicle_area CHARACTER SET utf8mb4 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Query OK, 41510 rows affected, 65535 warnings (0.20 sec)
Records: 41510  Deleted: 0  Skipped: 0  Warnings: 83020

性能比较

数据规模：41510 条记录

方案选项	耗时
方案1：逐个导入脚本方案	66 sec
方案2：批量导入脚本方案	0.35 sec
方案3：加载本地csv文件脚本方案	0.20 sec

小窍门

为了避免执行MySQL脚本时需要手工录入密码，可以执行如下命令。以后，就可以通过mysql直接免密执行MySQL脚本或直接登录MySQL数据库。

[xuej@localhost Downloads]$ mysql_config_editor set --user=root --host=localhost --port=3306 --password
Enter password: