当要从文件中向数据库中一次插入多条数据时,可以使用load data 方式。实测19万条数据,使用本方法大概10s左右。单个使用insert,大概需要一天半。
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'//文件名称
[REPLACE | IGNORE]
INTO TABLE tbl_name//表名称
[FIELDS
[TERMINATED BY 'string']// 定义分割符号
[[OPTIONALLY] ENCLOSED BY 'char']//字段括起字符
[ESCAPED BY 'char']//转义字符
]
[LINES
[STARTING BY 'string']//行的起始符号
[TERMINATED BY 'string']//描述字段的分隔符,默认 情况下是tab字符(\t)
]
[IGNORE number LINES]//忽略开头number行
[(col_name_or_user_var,...)]
[SET col_name = expr,...]]
例子:
LOAD DATA LOCAL INFILE 'data.csv'
REPLACE INTO TABLE xx.table
FIELDS TERMINATED BY ',' ESCAPED BY '\\'
LINES STARTING BY ''
IGNORE 1 LINES
(user_id, brand_id,type,visit_datetime);
data.csv 中的表数据为: