云数据库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参数使用详解
    下一篇
    使用mysqldump导入GeneratedColumn报错问题