将csv文件导入MySQL中

创建数据表

导入CSV格式文件，首先保证表存在

?

1 2 3 4 5

CREATE TABLE `nizong_1e_data`  (   `user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,   `age` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,   INDEX `phone`(`user_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

上传数据文件

查看数据库安全导入目录，将数据文件上传至安全目录

?

1 2 3 4 5 6 7

mysql> show variables like '%secure_file_priv%';       +------------------+-----------------------+ | Variable_name    | Value                 | +------------------+-----------------------+ | secure_file_priv | /tmp/ | +------------------+-----------------------+ 1 row in set (0.00 sec)

查看数据库字符集

?

1 2 3 4 5 6 7

mysql> show variables like 'character_set_database'; +------------------------+---------+ | Variable_name          | Value   | +------------------------+---------+ | character_set_database | utf8mb4 | +------------------------+---------+ 1 row in set (0.00 sec)

将csv文件传入安全目录下然后再进行导入操作

 

导入数据

?

1 2 3 4

load data infile '/tmp/5KW_PIR.csv' into table nizong_5kw_data character set utf8mb4 fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n';

问题列表

1、现象：执行命令后，查看数据表仅有一行数据

定位：检查第一行数据是否与真实数据完全匹配，第一行最后列字段是否录入了第二行数据

方法：行分隔符使用错误，比如hive导出的csv的行分隔符为\n

 

如果建表的时候加入索引，导入数据过大就会导致插入时间过长

加索引：

第一个千万 耗时两个半小时

第二个千万 耗时八个小时四十分钟

第三个千万 耗时十六个小时+

无索引：

五千万 耗时七分钟

一亿 三个半小时

千万级以上的数据导入时建议不要加索引

 

创建无索引的表

?

1 2 3 4 5

CREATE TABLE `400w_nizong_data`  (   `user_id` varchar(255) ,   `age` varchar(255)    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

导入数据后添加索引

?

1	alter table nizong_1e_data add index idx_user_id(user_id);