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

这篇具有很好参考价值的文章主要介绍了【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

【免责声明】文章仅供学习交流,观点代表个人,与任何公司无关。
编辑|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文章来源地址https://www.toymoban.com/news/detail-700130.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)
mysqlload命令,MySQL入门与精通,数据库,mysql,SQL
注意:确保设置的路径存在,并且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

到了这里,关于【MySQL】使用LOAD DATA INFILE命令加载数据文件到MySQL数据库的方法和常见错误及解决方法的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 使用 adb 命令修改 Android/data 目录下的文件(给碧蓝档案国服吃布丁)

    本文记录如何使用 adb 命令修改 Android/data 目录下的文件,然后给国服的碧蓝档案打上布丁🍮~ 今天下午刷着刷着微博就看到国服 BA 又又又发了和谐公告 ... 心情复杂。jpg 于是乎终于想起来得吃布丁🍮了,至于此次更新后布丁有没有用还未知,但还是先搞上 食用方法之前就出

    2024年03月24日
    浏览(47)
  • Hive(18):DML之Load加载数据

    1 背景 回想一下,当在Hive中创建好表之后,默认就会在HDFS上创建一个与之对应的文件夹,默认路径是由参数hive.metastore.warehouse.dir控制,默认值是/user/hive/warehouse。 要想让hive的表和结构化的数据文件产生映射,就需要把文件移到到表对应的文件夹下面,当然,可以在建表的时

    2024年02月13日
    浏览(68)
  • 如何使用Spring Data JPA简化MySQL数据访问

    本篇文章是 “一起学习mysql” 系列的第五篇文章,本篇文章我们学习一下Spring Data JPA的使用,在开始学习器,我们先来了解一下什么是JPA。 JPA的全称是Java Persistence API,是J2EE中的一条规范,它标准化了数据持久化API。在上一篇文章中,我们了解了如何使用MyBatis进行MySQL数据

    2024年02月15日
    浏览(66)
  • Hive数据加载方式(load、insert;普通表、分区表)

    介绍 Hive 数据加载方式(insert、load) 基础语法: load data [local] inpath \\\'/opt/module/datas/student.txt\\\' [overwrite] into table student[partition ] 参数说明: 1 load data: 表示加载数据 2 local: 表示从本地加载数据到 hive 表;否则从 HDFS 加载数据到 hive 表 3 inpath: 表示加载数据的路径 相对路径,例如

    2024年02月03日
    浏览(55)
  • 使用java命令运行class文件提示“错误:找不到或无法加载主类“的问题分析

    1.可能是编译的时候后缀带了.class:把后缀去掉; 2.可能是因为你的java文件带了package包,原因是:我们在包里运行文件,在运行文件的时候又找了一次包,可是我们已经在包里了,自然找不到主类了。 针对第二种错误解决方法有两种: 方法一:把java文件中的包删掉或者注释

    2024年02月15日
    浏览(54)
  • 使用ts-node命令运行ts文件时报错(Warning: To load an ES module, set “type“: “module“ in the package.json...)

    使用 ts-node 运行TS文件时报错。 错误信息1: 错误信息2: ts-node 版本: ^10.8.0 , typescript 版本: ^4.6.3 。 tsconfig.json 文件内容如下 在网上查找了一些资料,出现类似问题的有很多,链接如下: http://www.longant.cn/en/node/7289 https://stackoverflow.com/questions/62096269/cant-run-my-node-js-typescr

    2024年02月16日
    浏览(48)
  • docker使用load加载tar镜像时报错no such file or directory

    解决docker在使用load加载tar镜像时报错 open /var/lib/docker/tmp/docker-import-xxxxxxxxx/repositories: no such file or directory 在使用docker进行部署时,使用docker save指令对镜像打包成tar文件,在部署机器上使用docker load部署时报错,显示open /var/lib/docker/tmp/docker-import-xxxxxxxxx/repositories: no such file

    2024年02月12日
    浏览(46)
  • launch文件加载urdf到rviz和gazebo中报错 Invalid <param> tag: Cannot load command parameter [robot_description]

            在学习赵虚左老师的ros入门教程urdf一章时,使用 launch文件加载urdf到rviz和gazebo中的时候(命令如下) 意外出现报错: 这是我的launch文件,与老师的除了几个名称其他一样 它一直提示标签无效,我以为是拼写错误,但仔细检查了一遍后没找到问题,去网上查找也没找

    2024年02月14日
    浏览(39)
  • 如何在 MySQL 中使用命令行导入 SQL 文件?

    我有一个从 phpMyAdmin 导出的 .sql 文件。我想使用命令行将它导入到不同的服务器中。 我有一个 Windows Server 2008 R2 安装。我将 .sql 文件放在 C 盘 上,并尝试了此命令 它不工作。我收到语法错误。 我怎样才能毫无问题地导入这个文件? 我需要先创建一个数据库吗? 打造属于自

    2023年04月09日
    浏览(32)
  • 【深度学习】torch.utils.data.DataLoader相关用法 | dataloader数据加载器 | pytorch

    dataloader数据加载器属于是深度学习里面非常基础的一个概念了,基本所有的图像项目都会用上,这篇博客就把它的相关用法总结一下。 之所以要写这篇,是想分清楚len(data_loader)和len(data_loader.dataset) 这里加载的数据我们以Mnist手写数据集为例子 torchvision.datasets.MNIST是用来加载

    2024年02月16日
    浏览(54)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包