Mysql - 使用load file进行标准格式的文件导入

1、修改参数

set global sync_binlog=1000;
set global innodb_flush_log_at_trx_commit=2;

set sql_log_bin=0;
set autocommit=0;

vi my.cnf
secure_file_priv=/soft

2、load file语法

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] 
    [LOCAL]                                --LOCAL：表示文件放在客户端主机上，从客户端读取文本文件；如果没指定，则表示从服务器主机读取文本文件  
    INFILE 'file_name'
    [REPLACE | IGNORE]                     --REPLACE：与唯一键重复的行将被覆盖更新。IGNORE（默认）：与唯一键重复的行将被忽略。
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']           --字段分隔符，每个字段使用什么隔开，默认是\t
        [[OPTIONALLY] ENCLOSED BY 'char']  --文本限定符，默认是null
        [ESCAPED BY 'char']                --转义符，默认是\
    ]
    [LINES                                 
        [STARTING BY 'string']
        [TERMINATED BY 'string']           --记录分隔符，每行使用符号隔开，默认使用\n'
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

3、注意事项

• 1、字段中的空值 null 需要使用 \N 表示
• 2、字段类型如果是datetime，应该严格把控相应文本数据的格式，建议采用类似这种 yyyy-MM-dd HH:mm:ss否则难以保证数据导入的正确性
• 3、使用LOAD之前,需在MySQL中先创建目标表,建议添加一个自增ID列,方便统计记录数,字段顺序和CSV列的顺序保持一致
• 4、字段类型为datatime，非正常日期(yyyy-mm-dd)，会在导入提示导入失败，可以使用STR_TO_DATE函数进行转换

4、创建表

CREATE TABLE test.user_info (
id  int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
name  varchar(36) NULL COMMENT '姓名',
age  int NULL COMMENT '年龄',
address  varchar(255) NULL COMMENT '地址',
create_date  datetime NULL COMMENT '创建时间',
PRIMARY KEY (id)
);

5、命令

LOAD DATA INFILE '/soft/user_info.csv' replace INTO TABLE test.user_info 
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
LINES  TERMINATED BY '\n'
IGNORE 1 LINES
(id,name,age,address,@create_date)
set create_date = STR_TO_DATE(@create_date,'%d/%m/%Y %H:%i');

## 输出如下：
MySQL [test]> system ls -ltr /soft/user_info*
-rw-r--r-- 1 root root 29249 Oct 27 16:43 /soft/user_info.csv
MySQL [test]> show variables like 'secure%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| secure_file_priv | /soft/ |
+------------------+--------+
1 row in set (0.00 sec)

MySQL [test]> LOAD DATA INFILE '/soft/user_info.csv' replace INTO TABLE test.user_info 
    -> CHARACTER SET utf8mb4
    -> FIELDS TERMINATED BY ','
    -> LINES  TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id,name,age,address,@create_date)
    -> set create_date = STR_TO_DATE(@create_date,'%d/%m/%Y %H:%i');
Query OK, 1000 rows affected (0.05 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

MySQL [test]> select count(*) from test.user_info;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

7、基于innodb引擎，参数设置不同情况下的效率

MySQL [test]> set session autocommit=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set global sync_binlog=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set global innodb_flush_log_at_trx_commit=1;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> truncate table user_info;
Query OK, 0 rows affected (0.23 sec)

[root@mysql815 ~]# systemctl restart mysql

MySQL [test]> LOAD DATA INFILE '/soft/user_info.csv' replace INTO TABLE test.user_info 
    -> CHARACTER SET utf8mb4
    -> FIELDS TERMINATED BY ','
    -> LINES  TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id,name,age,address,@create_date)
    -> set create_date = STR_TO_DATE(@create_date,'%d/%m/%Y %H:%i');
Query OK, 1000 rows affected (0.11 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0



MySQL [test]> set session autocommit=off;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set global sync_binlog=1000;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> LOAD DATA INFILE '/soft/user_info.csv' replace INTO TABLE test.user_info 
    -> CHARACTER SET utf8mb4
    -> FIELDS TERMINATED BY ','
    -> LINES  TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (id,name,age,address,@create_date)
    -> set create_date = STR_TO_DATE(@create_date,'%d/%m/%Y %H:%i');
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

8、主键按照设定值自增长

## truncate table后，会自动将auto_increment重置为0，delete table后，不会修改increment的值
## 当mysql为innodb引擎时，重启mysql会自动填充空白的primary key(8.0不会填充)
alter table user_info auto_increment=1000;