文章目录

• 【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)
            

            关于local_infile参数

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

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

            Command-Line Format	–local-infile
            System Variable	local_infile
            Scope	Global
            Dynamic	Yes
            SET_VAR Hint Applies	No
            Type	Boolean
            Default Value	OFF

            注意：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

             
             画册设计网站  如何注册公司公众号  企业网站多少钱一年  优设设计网站  中国建设银行官方网站登录  镇江网站建设设计