MySQL LOAD DATA LOCAL INFILE快速插入大量数据

一.原理介绍

LOAD DATA LOCAL INFILE 是MYSQL JDBC 中自带的从文件读入数据，快速插入数据库的方法 ，通过执行满足第二部分所示的语法结构的sql语句，即可完成操作。在python中，可以使用pymysql中的cursor.excute()方法执行该sql语句。这将在第四部分中给出实例。

二.语法介绍

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

该LOAD DATA语句以非常高的速度将文本文件中的行读入表中。 LOAD DATA是补充 SELECT ... INTO OUTFILE。要将表中的数据写入文件，请使用 SELECT ... INTO OUTFILE。要将文件读回表中，请使用 LOAD DATA。两个语句的FIELDS和LINES子句的语法 相同。

更多的相关内容,大家可以参照官网

三.数据分隔符

1. fields关键字指定了文件记段的分割格式，如果用到这个关键字，MySQL剖析器希望看到至少有下面的一个选项：
   terminated by 描述字段的分隔符，默认情况下是tab字符（\t）
   enclosed by 描述的是字段的括起字符。
   escaped by 描述的转义字符。默认的是反斜杠（backslash：\ ）
2. lines 关键字指定了每条记录的分隔符默认为’\n’即为换行符 (不指定一个lines子句，缺省值默认写的如: lines terminated by’\n’)
3. [IGNORE number LINES] 忽略特定行数，CSV文件可以忽略掉第一行标题

四.实例

1.python pymysql执行简单的LOAD DATA LOCAL INFILE实例

import pymysql
mydb = pymysql.connect("ip","user", "password", "dbname",3306,local_infile=1)
mydb.set_charset('utf8')

mycursor = mydb.cursor()
mycursor.execute("select table_name from information_schema.tables where table_schema='huazhu';")

myresult = mycursor.fetchall()  # fetchall() 获取所有记录
for x in myresult:
    file_name="D:/Cipher/huazhu20201130/data/dat/"+x[0]+".dat"
    sql="LOAD DATA LOCAL INFILE '"+file_name+"' INTO TABLE "+x[0]+" FIELDS TERMINATED BY '|';"
    print(sql)
    mycursor.execute(sql)
    mydb.commit()  # 数据表内容有更新，必须使用到该语句
    print(mycursor.rowcount, "记录插入成功。")

2.复杂的LOAD DATA LOCAL INFILE实例

LOAD DATA LOCAL INFILE 'F:\\milo.csv' INTO TABLE test
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id, name)

五.速度对比

经过测试插入1w条数据时候与拼装批量插入语句时间差别不大，当插入数量达到10w出现了明显的时间差：
拼装批量插入语句花费时间：6.83s
LOAD DATA LOCAL INFILE实现大批量插入花费时间：1.23s
当表格的字段更多数据量更大出现的时间差就越大。

总结：当需要进行大批量数据插入的时候，可以优先考虑LOAD DATA LOCAL INFILE实现方式。