文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2023-08-17 23:01

关注

【免责声明】文章仅供学习交流,观点代表个人,与任何公司无关。
编辑|SQL和数据库技术(ID:SQLplusDB)

在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 changedmysql> 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 studentFIELDS 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: 0mysql> 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 statementmysql>

原因

这个错误表示由于 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

查看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 mysqlnet 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 sidesmysql>

原因

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

解决方法

服务器端设置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)

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

例:C:\Users\Administrator> mysql --local-infile=1 -u root -pmysql> use testdbDatabase changedmysql>  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: 100mysql> 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

6.1.6 Security Considerations for LOAD DATA LOCAL
https://dev.mysql.com/doc/refman/8.0/en/load-data-local-security.html

来源地址:https://blog.csdn.net/lukeUnique/article/details/131219847

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯