【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法
作者:mmseoamin日期:2023-12-05

文章目录

  • 【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法
    • LOAD DATA INFILE的语法详细
    • 1.创建(选择)目标数据库和表
    • 2.将数据从 CSV 文件导入已创建的表
    • 常见错误和解决方法
      • 错误1:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
        • 原因
        • 解决方法
        • 关于secure_file_priv参数
          • 查看secure_file_priv参数设置
          • 修改secure_file_priv参数
          • 错误2:ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
            • 原因
            • 解决方法
            • 关于local_infile参数

              【免责声明】文章仅供学习交流,观点代表个人,与任何公司无关。

              编辑|SQL和数据库技术(ID:SQLplusDB)

              【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法

              在MySQL数据库中,可以使用LOAD DATA INFILE命令将数据从 CSV 文件导入已创建的表中。

              LOAD DATA INFILE的语法详细

              LOAD DATA INFILE的语法详细如下:

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

              参考:

              3.3.3 Loading Data into a Table

              https://dev.mysql.com/doc/refman/8.0/en/load-data.html

              6.1.6 Security Considerations for LOAD DATA LOCAL

              https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html

              具体步骤如下。

              1.创建(选择)目标数据库和表

              创建测试数据库testdb和相关的表

              mysql> show databases;
              +--------------------+
              | Database           |
              +--------------------+
              | information_schema |
              | mysql              |
              | performance_schema |
              | sakila             |
              | sys                |
              +--------------------+
              5 rows in set (0.05 sec)
              mysql> CREATE DATABASE testdb;
              Query OK, 1 row affected (0.10 sec)
              mysql> show databases;
              +--------------------+
              | Database           |
              +--------------------+
              | information_schema |
              | mysql              |
              | performance_schema |
              | sakila             |
              | sys                |
              | testdb             |
              +--------------------+
              6 rows in set (0.00 sec)
              mysql> use testdb;
              Database changed
              mysql> CREATE TABLE Student (
                  -> id INT PRIMARY KEY,
                  -> name VARCHAR(50)
                  -> );
              Query OK, 0 rows affected (0.35 sec)
              

              2.将数据从 CSV 文件导入已创建的表

              LOAD DATA INFILE 'students.csv'
              INTO TABLE student
              FIELDS TERMINATED BY ',' -- 字段分隔符
              ENCLOSED BY '"' -- 字段使用的引号
              ESCAPED BY '\' -- 转义符
              LINES TERMINATED BY '\n' -- 行分隔符
              IGNORE 1 ROWS; -- 忽略 CSV 文件第一行(表头)
              

              执行例:

              mysql>  show variables like 'secure_file_priv';
              +------------------+-------+
              | Variable_name    | Value |
              +------------------+-------+
              | secure_file_priv | F:\   |
              +------------------+-------+
              1 row in set, 1 warning (0.00 sec)
              mysql> LOAD DATA INFILE 'F:\\students.csv'
                  -> INTO TABLE student
                  -> FIELDS TERMINATED BY ',' -- 字段分隔符
                  -> ENCLOSED BY '"' -- 字段使用的引号
                  -> ESCAPED BY '\\' -- 转义符
                  -> LINES TERMINATED BY '\n' -- 行分隔符
                  -> IGNORE 1 ROWS; -- 忽略 CSV 文件第一行(表头)
              Query OK, 100 rows affected (0.09 sec)
              Records: 100  Deleted: 0  Skipped: 0  Warnings: 0
              mysql> select count(*) from student;
              +----------+
              | count(*) |
              +----------+
              |      100 |
              +----------+
              1 row in set (0.00 sec)
              

              常见错误和解决方法

              错误1:ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

              在将数据加载到MySQL数据库时发生如下错误

              mysql> LOAD DATA INFILE 'students.csv'
                  -> INTO TABLE student
                  -> FIELDS TERMINATED BY ',' -- 字段分隔符
                  -> ENCLOSED BY '"' -- 字段使用的引号
                  -> ESCAPED BY '\\' -- 转义符
                  -> LINES TERMINATED BY '\n' -- 行分隔符
                  -> IGNORE 1 ROWS; -- 忽略 CSV 文件第一行(表头)
              ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
              mysql>
              
              原因

              这个错误表示由于 MySQL中 --secure-file-priv 选项的设置,限制了用户从本地文件系统加载数据到服务器中。

              解决方法

              修改secure_file_priv参数为文件所在目录 或者 指定为空。

              关于secure_file_priv参数

              secure_file_priv是一个MySQL 全局(Global)系统变量,用于限制数据导入和导出操作,例如LOAD DATA和SELECT … INTO OUTFILE语句以及LOAD_FILE()函数的操作。只有拥有FILE权限的用户才能执行这些操作。

              secure_file_priv的设置包括三种情况:

               - 如果为空(secure-file-priv=“”),表示没有任何限制。这不是一个安全的设置。
               - 如果设置为目录的名称,表示服务器限制导入和导出操作仅使用该目录中的文件。服务器不会创建该目录,所以必须为存在的路径。
               - 如果设置为NULL(secure-file-priv=null),表示服务器禁用导入和导出操作。
              

              secure_file_priv的默认值根据安装的平台有所不同。

              secure_file_priv参数内容如下:

              Command-Line Format–secure-file-priv=dir_name
              System Variablesecure_file_priv
              ScopeGlobal
              DynamicNo
              SET_VAR Hint AppliesNo
              TypeString
              Default Valueplatform specific
              Valid Valuesempty string, dirname, NULL

              参考:

              https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv

              查看secure_file_priv参数设置

              可以通过以下命令查看 secure_file_priv 的当前设置值:

              SHOW VARIABLES LIKE 'secure_file_priv';
              

              例:

              mysql> show variables like 'secure_file_priv';
              +------------------+-------+
              | Variable_name    | Value |
              +------------------+-------+
              | secure_file_priv | F:\   |
              +------------------+-------+
              1 row in set, 1 warning (0.00 sec)
              
              修改secure_file_priv参数

              因为secure_file_priv是一个非持久化只读变量,不能通过SET命令进行修改。

              所以需要在MySQL配置文件my.cnf (Mac, Linux) 或者 my.ini (Windows) 中设置secure-file-priv参数。具体操作方法如下:

              打开MySQL配置文件my.cnf或my.ini。
              添加或修改secure-file-priv参数,例如:secure-file-priv=/var/lib/mysql-files。
              保存并关闭文件。
              重启MySQL服务器,使配置生效。
              	net stop mysql
              	net start mysql
              

              例:(Windows)

              【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法,在这里插入图片描述,第1张

              注意:确保设置的路径存在,并且MySQL用户有访问该路径的权限。

              如果secure_file_priv设置不正确,可能导致MySQL进程启动失败。

              错误2:ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.

              使用 LOAD DATA LOCAL INFILE,从客户端加载数据到数据库的时候,可能发生如下错误。

              --服务器端未启动LOAD DATA LOCAL INFILE功能
              ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
              --客户端未启动LOAD DATA LOCAL INFILE功能
              ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
              

              例:

              mysql>  LOAD DATA LOCAL INFILE 'F:\\students.csv'
                  ->  INTO TABLE student
                  ->  FIELDS TERMINATED BY ',' -- 字段分隔符
                  ->  ENCLOSED BY '"' -- 字段使用的引号
                  ->  ESCAPED BY '\\' -- 转义符
                  ->  LINES TERMINATED BY '\n' -- 行分隔符
                  ->  IGNORE 1 ROWS;
              ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
              mysql>
              
              原因

              这个错误是因为默认情况下,MySQL服务器禁止使用LOAD DATA LOCAL INFILE命令从客户端读取本地文件。

              解决方法

              1.服务器端设置local_infile,启用LOAD DATA LOCAL INFILE功能。

              修改local_infile为启用。

              例:

              mysql> show variables like 'local%';
              +---------------+-------+
              | Variable_name | Value |
              +---------------+-------+
              | local_infile  | OFF   |
              +---------------+-------+
              1 row in set, 1 warning (0.02 sec)
              mysql> set global  local_infile = 'ON';
              Query OK, 0 rows affected (0.00 sec)
              mysql> show variables like 'local%';
              +---------------+-------+
              | Variable_name | Value |
              +---------------+-------+
              | local_infile  | ON    |
              +---------------+-------+
              1 row in set, 1 warning (0.00 sec)
              

              2.客户端在连接MySQL服务器时添加–local-infile选项,启用LOAD DATA LOCAL INFILE功能。

              例:
              C:\Users\Administrator> mysql --local-infile=1 -u root -p
              mysql> use testdb
              Database changed
              mysql>  LOAD DATA LOCAL INFILE 'F:\\students.csv'
                  ->  INTO TABLE student
                  ->  FIELDS TERMINATED BY ',' -- 字段分隔符
                  ->  ENCLOSED BY '"' -- 字段使用的引号
                  ->  ESCAPED BY '\\' -- 转义符
                  ->  LINES TERMINATED BY '\n' -- 行分隔符
                  ->  IGNORE 1 ROWS;
              Query OK, 0 rows affected, 100 warnings (0.08 sec)
              Records: 100  Deleted: 0  Skipped: 100  Warnings: 100
              mysql> select count(*) from student;
              +----------+
              | count(*) |
              +----------+
              |      100 |
              +----------+
              1 row in set (0.05 sec)
              
              关于local_infile参数

              local_infile参数是一个MySQL系统变量,用于控制服务器端LOAD DATA语句的LOCAL功能。

              根据local_infile设置,服务器会拒绝或允许启用客户端LOCAL功能的客户端加载本地数据。

              Command-Line Format–local-infile
              System Variablelocal_infile
              ScopeGlobal
              DynamicYes
              SET_VAR Hint AppliesNo
              TypeBoolean
              Default ValueOFF

              注意:local_infile参数可以动态修改。

              参考:

              https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile

              6.1.6 Security Considerations for LOAD DATA LOCAL

              https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html