Mysql导入文本文件内数据到数据库表

有时候我们会需要把一些数据文件导入到Mysql数据库表中做处理，这时候我们可以使用Mysql提供的load data语句来完成。

一、load data 语法

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}] ...]

关键选项说明：

• LOCAL：从客户端获取文件，如果不加该参数则会到数据库所在服务器寻找文件；
• CHARACTER SET charset_name：指定数据文件的字符集；
• TERMINATED BY 'string'：字段分隔符，默认是\t；
• IGNORE number LINES：跳过文件开头不需要的行数；

二、常规使用方法

1.基础用法

导入用逗号分隔的CSV文件：

LOAD DATA LOCAL INFILE '/data/test_file.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ',';

2.处理有标题行的文件

导入用逗号分隔的CSV文件，跳过第一行标题行：

LOAD DATA LOCAL INFILE '/data/test_file.csv'
INTO TABLE test_table
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

3.指定文件对应列名

如果数据文件和数据表列不是顺序对应的，可以通过设置数据文件与列名对应关系调整：

LOAD DATA LOCAL INFILE '/data/test_file.csv'
INTO TABLE test_table
FIELDS TERMINATED BY '^'
(name, id, age);

三、Mysql数据库配置

使用load data 的时候，需要数据库服务器做对应的配置。

1.设置Mysql读取文件权限

如果需要从Mysql服务器读取文件（不加LOCAL参数），就需要设置secure_file_priv参数，指定Mysql可读取的文件目录地址。

在Mysql配置文件中新加以下配置：

[mysqld]
secure_file_priv=/var/lib/mysql-files/

设置完成后需要重启Mysql生效，可以通过下面命令查看当前状态：

SHOW VARIABLES LIKE "secure_file_priv";

2.设置允许客户端导入文件

需要Mysql服务器支持客户端导出功能，可以通过下面命令开启：

SET GLOBAL local_infile = ON;

或者在配置文件中加local_infile参数：

[mysqld]
local_infile=1

可以通过下面命令查看当前状态：

SHOW GLOBAL VARIABLES LIKE 'local_infile';

四、导入数据测试

首先创建一个测试表test：

CREATE TABLE `test` (
	`id` INT(10) NULL DEFAULT NULL,
	`name` VARCHAR(50) NULL DEFAULT NULL'
)

然后建立一个测试数据文件test.txt，文件内容如下：

1,zhangsan
2,lisi
3,wangwu
4,zhaoliu

登录mysql命令行：

mysql -uroot -p --local-infile=1

注：登录的时候需要加local_infile=1参数，否则会报错。

切换到测试库：

use kettle;

导入数据文件：

load data local  infile "C:\\Users\\bit\\Desktop\\test.txt"
into table `test`
fields terminated by ',';

注：Windows系统文件路径需要使用反斜杠\转义一下路径。

这样数据就导入到数据库表中了。

五、制作脚本自动导入

如果需要多次重复导入，我们可以把load data语句写到控制文件中，然后通过脚本读取操作，减少重复工作。

1.编写控制文件

控制文件我们一般使用ctl后缀名，新建一个test.ctl控制文件，将load data语句写入到这个文件中：

load data local  infile "C:\\Users\\bit\\Desktop\\test.txt"
into table `test`
character set utf8
fields terminated by ','
(id,name);

2.编写脚本文件

因为使用的是windows系统，所以用bat脚本，脚本内容如下：

@echo off
chcp 65001 >nul

REM ============= 参数修改成自己的 =============
set MYSQL_BIN=D:\mysql-8.0.33-winx64\bin\mysql.exe
set DB_USER=root
set DB_PASS=123456
set DB_NAME=kettle
REM =============================================

cd /d "%~dp0"

echo 开始批量导入数据（当前目录：%cd%）...
echo.

for %%f in (*.ctl) do (
    echo [%%f] 正在导入 ...
    "%MYSQL_BIN%" -u %DB_USER% -p%DB_PASS% --local-infile=1 -D %DB_NAME% < "%%f" >> import.log 2>&1
    
    if errorlevel 1 (
        echo [%%f] 导入失败，请检查日志！
    ) else (
        echo [%%f] 导入成功！
    )
    echo --------------------------------
)

echo 全部处理完毕！
pause

这个脚本可以执行当前目录下所有ctl控制文件内容，将对应数据导入到数据库表中。

3.执行脚本

脚本使用的时候保存成bat文件直接双击执行就可以了：

脚本可以根据实际情况自己调整使用。

扩展说明：

1.参考文档：https://dev.mysql.com/doc/refman/8.0/en/load-data.html