当前位置：首页 > news >正文

MySql 通过 LOAD DATA INFILE 导入大量数据

news 2025/1/8 12:53:33

背景：要在本地Mysql导入几十万-百万条数据非常耗时，从网上找到通过load data infile 方式可以快速导入大量数据，就动手尝试了。结果就是不太适合复杂的格式数据导入，比如字段多，数据格式多等，但对于简单的表导入大量数据还是很实用的，因此记录下使用方法。

一、mysql开启文件导入功能

方法1：在命令行模式下输入以下命令

SET GLOBAL local_infile = 1;
SET GLOBAL file_upload_max_size = '100M'; -- 设置最大文件上传大小为100MB(可选)

方法2：在my.ini文件 [mysqld] 计入以下内容

注：mysql8 的my.ini 文件目录在\ProgramData\MySQL\MySQL Server 8.0\下

[mysqld]
file_upload_max_size = 100M
local_infile = 1

二、准备导入的数据文件

一般使用csv格式，字段间以逗号隔开

如果导入数据里有中文，可能会导入失败，可以按一下方式尝试解决：

1.导入的数据文件格式错误：将文件编码格式改为UTF-8

或者

2.在my.ini设置数据库默认的编码格式

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4

导入时提示不能执行导入文件：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

方法1：在my.ini配置文件中，将secure_file_priv的值设置为空字符串：secure_file_priv = ""，修改后需要重启MySQL服务。

方法2：将导入文件放到mysql 的upload目录下：\ProgramData\MySQL\MySQL Server 8.0\Uploads（该方法在mysql8下适用，不确定其他版本是否可行）

三、执行导入命令

导入文件准备好后，mysql -uroot -pxxxx进入mysql

1.use 数据库名

2.执行以下命令

LOAD DATA INFILE 'D:\\ProgramData\\MySQL\\MySQL Server xxx.csv'INTO TABLE 表名 FIELDS TERMINATED BY ',' ----导入文件里字段间隔符LINES TERMINATED BY '\r\n'----每行以换行符结束（linux系统换行符为\n）ENCLOSED BY '"' ----表示字段被双引号包围（可选，如果字段没有被符号包围，可不写这句）IGNORE 1 LINES----如果用标题忽略第一行(colum1,column2...,columnN);----导入的字段名

补充：导入字段格式化

1.使用set命令：

（1）日期字段格式化（带毫秒数的时间yyyy-MM-dd HH:mm:ss 000去掉毫秒数）

(id,@var_create_time)

SET create_time= IF(@var_create_time= 'NULL', NULL,STR_TO_DATE(LEFT(@var_create_time,19), '%Y-%m-%d %H:%i:%s')));

（2）空字段插入NULL

serialName = NULLIF(@serial_name, 'NULL'),

2.数据表中某个字段的类型为整型，在导入文件里该字段为NULL，导入时会以字符串‘NULL’插入导致报错

将导入文件里该字段值NULL 替换为\N，导入时就会以数据库的NULL值插入

查看全文

http://www.mrgr.cn/news/82972.html