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

SQLplusDB

已于 2024-02-09 12:54:50 修改

阅读量7.1k

点赞数 6

分类专栏： MySQL入门与精通文章标签：数据库 mysql SQL

于 2023-06-17 14:47:41 首次发布

本文链接：https://blog.csdn.net/lukeUnique/article/details/131219847

版权

MySQL入门与精通专栏收录该内容

24 篇文章 1 订阅

订阅专栏

文章目录

【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 Variable	secure_file_priv
Scope	Global
Dynamic	No
SET_VAR Hint Applies	No
Type	String
Default Value	platform specific
Valid Values	empty 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用户有访问该路径的权限。
如果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)