所有文档

          云数据库 RDS

          local_infile参数使用详解

          背景

          在数据库的使用过程中,经常需要进行批量的数据导入操作,MySQL数据库批量数据导入可以使用LOAD DATA命令,实现大批量数据的快速入库需求。

          问题现象

          在使用LOAD DATA命令可能会遇到如下报错情况:

          • 使用 LOAD DATA LOCAL INFILE 命令批量导入数据时,报错:

            ERROR 1148 (42000): The used command is not allowed with this MySQL version.

          • 使用 LOAD DATA INFILE 命令导入数据时,报错:

            ERROR 1045 (28000): Access denied for user 'xxxx'@'%' (using password: YES)

          原因分析

          第一种报错是受参数设置的影响:

          • MySQL服务端参数:local_infile 用于控制MySQL Server是否允许使用LOAD DATA LOCAL INFILE命令导入存放于客户端的数据文件。
          • MySQL客户端参数:--local-infile 用于控制MySQL Client是否允许使用LOAD DATA LOCAL INFILE命令导入存放于客户端的数据文件。

          第二种报错是命令和账号权限不匹配导致的:

          • LOAD DATA LOCAL INFILE 命令可以导入客户端的数据文件,使用具有普通写权限的账号即可。
          • LOAD DATA INFILE 命令可以导入服务端的数据文件,需要使用具备FILE权限的账号(出于安全考虑不建议放开FILE权限)。

          解决方案

          • 第一种报错解决方案:确保MySQL客户端登录时开启参数--local_infile,且确保MySQL服务端开启参数local_infile。
          • 第二种报错解决方案:使用LOAD DATA命令时注意添加LOCAL关键字。

          案例复现

          1. 创建测试表

            CREATE TABLE tb_01 (
            id int(11) NOT NULL AUTO_INCREMENT,
            age int(11) NOT NULL DEFAULT '0',
            name varchar(64) NOT NULL DEFAULT '',
            PRIMARY KEY (id)
            ) ENGINE=InnoDB;
          2. 构造测试数据

            Age Name
            15 Zhangsan
            28 Lisi
            21 Wangwu
            11 Zhaoliu
            33 Dingyi
          3. 测试结果
          • 场景1:正常情况

            执行如下SQL命令:

            LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' (age,name);

            输出结果:

            1.png

          • 场景2:MySQL客户端关闭参数--local-infile

            登录MySQL客户端:

            bin/mysql -h HOST -P PORT -u USER -p PASSWORD --local-infile=0

            执行如下SQL命令:

            LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age,name);

            输出结果:

            2.png

          • 场景3:MySQL服务端关闭参数local_infile

            3.png

            执行如下SQL命令:

            LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age,name);

            输出结果:

            4.png

          • 场景4:去掉LOCAL关键字

            执行如下SQL命令:

            LOAD DATA INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age,name);

            输出结果:

            5.png

          总结建议

          • 百度云RDS默认开启local_infile参数,如果需要限制使用LOAD DATA命令,可以通过RDS控制台【参数设置】页面关闭该参数。
          • 使用LOAD DATA命令需要注意添加LOCAL关键字,避免执行报错。
          上一篇
          innodb_large_prefix参数使用详解
          下一篇
          lower_case_table_names参数使用详解