MySQL--loaddata infile、outfile into及mysqldump高效导入导出数据_mysql load outfile

【学习背景】

在日常工作和学习MySQL时，经常涉及到MySQL数据的导入和导出，分享几种常用又方便的方式：
（1）MySQL命令行source命令
（3）语法into outfile和load data infile
（3）MySQL目录bin下的mysqldump工具

本文将会介绍以及测试这几种MySQL导入导出数据的方式及使用注意事项，参数可能会比较多，大家可以学习最常用的就好，这里分享出来，希望能帮助到有需要的小伙伴~

进入正文~

学习目录
  • 测试数据
  • 一、命令source实现
    • 2.1 导入数据
      • 2.2 导出数据
  • 二、 into oufile和load data infile实现
    • 2.1 into outfile
        • 2.1.1 简单导出数据
          • 2.1.2 带格式导出数据
          • 2.1.3 导出注意事项
      • 2.2 load data infile
        • 2.2.1 简单导入数据
          • 2.2.2 带格式导入数据
  • 三、工具mysqldump实现
    • 3.1 导出
        • 3.1.1 数据库
          • 3.1.2 数据表
      • 3.2 导入数据

测试数据

本文以Windows下操作为例，Linux也是一样的方法，区别在于路径语法不同而已~
创建一个MySQL数据库test和数据表demo_info，方便进行测试~

create database if not exists test default character set utf8 collate utf8_general_ci;
use test;
-- 创建测试表
create table test.demo_info(
	id int(7) primary key not null auto\_increment,
	name varchar(255) not null,
	sex char(1) not null,
	age int(3)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

alter table test.demo_info comment '测试表';
alter table test.demo_info modify column id int(7) not null auto\_increment comment 'ID';
alter table test.demo_info modify column name varchar(255) not null comment '姓名';
alter table test.demo_info modify column sex char(1) not null comment '性别:1-男,0-女';
alter table test.demo_info modify column age int(3) comment '年龄';

一、命令source实现

2.1 导入数据

（1）准备insert.sql内容如下：

use test;
insert into test.demo_info(name,sex,age) values('张一','1',21);
insert into test.demo_info(name,sex,age) values('张二','0',22);
insert into test.demo_info(name,sex,age) values('张三','1',23);

**存放路径：**C:/Users/Administrator/Desktop/insert.sql

（2）先登录到MySQL命令行
打开cmd命令窗口，登录到MySQL命令行：
$ cd C:\Program Files\MySQL\MySQL Server 5.7\bin
$ mysql -hlocalhost -uroot -p --default-character-set=utf8
输入密码：
mysql >

（3）执行source命令导入数据：
mysql> use test;
mysql> show tables;
mysql> select * from demo_info;
mysql> source C:/Users/Administrator/Desktop/insert.sql;

注意如果你数据库没有设置字符集为utf8，并且在连接时也没有指定--default-character-set=utf8连接，那么会导致插入中文数据时乱码，提示如下：
在这里插入图片描述
乱码原因是，默认客户端连接编码为GBK
mysql> use test;
mysql> show variables like '%character%';
在这里插入图片描述
中文乱码情况的解决方案，如果不想在连接时指定字符集为utf8，可以修改mysql的配置my.ini（my.cnf）指定字符集为utf8，重启mysql服务生效~

[client]
default-character-set=utf8
[mysql]
character-set-server=utf8
[mysqld]
default-character-set=utf8

2.2 导出数据

命令source导出数据主要是通过执行导出数据的SQL语句，本质还是使用into outfile语法来实现，这里先简单直接使用下~

（1）准备select.sql内容如下：

use test;
select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

**select.sql存放路径：**C:/Users/Administrator/Desktop/select.sql

（2）执行source命令导出数据：
mysql> source C:/Users/Administrator/Desktop/select.sql;

不过，别高兴太早，一般都会报错的，提示如下：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

原因是--secure-file-priv安全路径问题，具体往下进入到into outfile章节了解~

二、 into oufile和load data infile实现

2.1 into outfile

2.1.1 简单导出数据

导出数据通过into outfile语法实现，导入数据通过load data infile语法实现~

（1）前提条件说明
授权用户file权限：
mysql > select * from mysql.user where user='root' \G;
mysql > update mysql.user set File_priv='Y' where user='root';
mysql > select * from mysql.user where user='root' \G;
mysql > flush privileges;

如果没有授予用户的File_priv权限为Y，into outfile导出文件时会报错：
ERROR 1 (HY000): Can’t create/write to file ‘C:\Users\Administrator\Desktop\demo_info.txt’ (Errcode: 13 - Permission denied)

配置安全路径：

MySQL使用into outfile语法导出数据时，只能导出数据文件到secure-file-priv指定的安全路径下~
查看安全路径命令mysql> show variables like '%secure%';
在这里插入图片描述
可以看到参数secure_file_priv对应的路径即为MySQL安全路径：
但是Windows下路径问题，有一个小坑，容易误导人，就是这里show显示的路径是单反斜杠\，但实际用的时候要么变成双反斜杠\\，要么改成单斜杠/，才能使用into outfile语法正常导出，否则会报错~
在这里插入图片描述
如果指定导出文件路径不是安全路径下的，则会报错：
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

简单导出测试下（非安全路径，如桌面）：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

报错提示如下：
在这里插入图片描述
简单导出测试下（安全路径）

select \* from test.demo_info into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/demo\_info.txt';

正常导出demo_info.txt数据文件（注意Windows下路径不要用单反斜杠\）
在这里插入图片描述
（2）配置安全路径
如果不想用默认安全路径，可以修改参数--secure-file-priv为自定义路径，修改MySQL配置文件，一般默认的配置文件路径为：
Windows：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
Linux：/etc/my.cnf

安全路径在[mysqld]组下找到参数secure_file_priv进行配置即可~
在这里插入图片描述
这里我修改为空字符串""：

secure-file-priv=""

空字符串""表示不限制导出路径，不过需要是mysql用户有读写权限的目录，例如Linux下，你不能直接导出到/root/目录下，肯定是没权限创建数据文件的~~

（3）导出数据
简单导出测试：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

发现导出到桌面居然不成功，其他MySQL安装目录和D盘都可以，C盘下都不行~
在这里插入图片描述
解决方案是按快捷键：Win 快速搜索：服务关键字，找到mysql服务，右键查看属性~
在这里插入图片描述
切换账户为本地系统账户并勾选允许服务与桌面交互~
在这里插入图片描述
应用并重启mysql服务生效~
在这里插入图片描述
重新简单导出测试，导出到桌面成功：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.txt';

在这里插入图片描述

2.1.2 带格式导出数据

通过前面简单导出数据得到数据文件demo_info.txt，可以看到导出的数据占用的空间比较大

7	张一	1	21
8	张二	0	22
9	张三	1	23

如果字段的数据比较长，数据量比较大，会很浪费空间，因此需要对into outfile导出的数据文件进行格式化：
（1）MySQL命令行>

select \* from demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.del' character set utf8 fields terminated by 0x0f;

导出的数据空间完全紧密，不浪费任何空间，实际使用这种方式的非常多：在这里插入图片描述
（2）终端命令行：

mysql -hlocalhost -uroot -p test -e "select \* from test.demo\_info into outfile 'C:/Users/Administrator/Desktop/demo\_info.del' character set utf8 fields terminated by 0x0f"

into outfile参数说明：

参数说明
character set utf8字符集utf8，防止中文乱码，需要放在fields前面，否则报错
fields域，后面常用字段有terminated/optionally/escaped
terminated by 'string'设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines设置每条记录的开头starting和结尾字符terminated
lines starting by 'char'设置每条记录的开头字符，默认空字符串''
lines terminated by 'char'设置每条记录的结尾字符默认换行符'\n'

使用enclosed by参数示例：

select \* from demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info2.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"';

在这里插入图片描述

使用escaped by参数示例：
例如，把张三的名字后面加个特殊符号换行符\n

update test.demo_info set name='张一\n' where id=7;

在这里插入图片描述
再执行导出命令：

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info3.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"';

在这里插入图片描述

使用lines参数示例：

update test.demo_info set name='张一' where id=7;

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info4.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

观察每条记录的首尾数据格式：

在这里插入图片描述

2.1.3 导出注意事项

（1）存在问题：
Linux环境下，由于使用MySQL语法into outfile导出的数据文件时，数据文件只能保存在MySQL数据库服务端，那么会导致在集群模式下，当应用和数据库分别部署在两台不同的服务器时，会存在应用无法读取到数据文件的问题~

MySQL服务器M：/batchfile/mysql/data/test/demo_info.del;
应用服务器A： 批量程序，可能会通过shell脚本想要加载demo_info.del数据文件~
应用服务器B： 批量程序，可能会通过shell脚本想要加载demo_info.del数据文件~

（2）解决方案：
可以通过mount挂在指定目录/batchfile/为共享盘目录，实现服务器A、B、M都能拥有该目录下的数据文件的读写访问权限~

具体mount命令的使用方式，可以查询百度学习下~

2.2 load data infile

2.2.1 简单导入数据

（1）数据文件
前面通过into outfile简单导出得到demo_info.txt：

7	张一	1	21
8	张二	0	22
9	张三	1	23

（2）导入数据

load data infile 'C:/Users/Administrator/Desktop/demo\_info.txt' into table demo_info character set utf8;

在这里插入图片描述

2.2.2 带格式导入数据

导入del数据文件（加载服务端文件）：
命令行mysql>

load data infile 'C:/Users/Administrator/Desktop/demo\_info.del' into table demo_info character set utf8 fields terminated by 0x0f;

load data infile参数说明：

参数说明
character set utf8字符集utf8，防止中文乱码，需要放在fields前面，否则报错
fields域，后面常用字段有terminated/optionally/escaped
terminated by 'string'设置字段数据之间的分隔符，如最常用的分隔符0x0f
optionally enclosed by 'char'设置字段非数值的数据，使用什么符号引起，如英文双引号"
escaped by 'char'字段数据存在特殊符号使用的转移符，默认是反斜杠\，如还可以指定为双引号"
lines设置每条记录的开头starting和结尾字符terminated
lines starting by 'char'设置每条记录的开头字符，默认空字符串''
lines terminated by 'char'设置每条记录的结尾字符默认换行符'\n'
(字段1,字段2,字段3)指定字段导入数据，注意放在整个语句最后，放前面，会报错

其实除了指定字段的参数，其他参数大多只需要跟into outfile导出参数一样，导出时有的参数，load data infile导入时该有的参数也加上就好~

比如into outfile导出最复杂的情况如下（分隔符为0x0f、非数值双引号"扩起、特殊转义符使用双引号"转义、每条记录开头是start及结尾是end\n）得到数据文件demo_info_complex_data.del

update test.demo_info set name='张一\n' where id=7;

select \* from test.demo_info into outfile 'C:/Users/Administrator/Desktop/demo\_info\_complex\_data.del' character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

可以看到demo_info_complex_data.del内容如下：
在这里插入图片描述

那么要导入demo_info_complex_data.del对应的load data infile语法完整SQL语句为：

load data infile 'C:/Users/Administrator/Desktop/demo\_info\_complex\_data.del' into table demo_info character set utf8 fields terminated by 0x0f optionally enclosed by '"' escaped by '"' lines terminated by 'end\n' starting by 'start ';

其实很简单，把into outfile导出数据时character后面的参数直接copy过来就行~
在这里插入图片描述

Linux终端命令：

mkdir -p /batchfile/mysql/data/test/
mysql -hlocalhost -uroot -p test -e "load data infile '/batchfile/mysql/data/test/demo\_info.del' into table demo\_info character set utf8 fields terminated by 0x0f"

导入del数据文件（加载客户端本地LOCAL文件）：
命令行mysql>

load data LOCAL infile 'C:/Users/Administrator/Desktop/demo\_info.del' into table demo_info character set utf8 fields terminated by 0x0f;

Linux终端命令：

mysql -hlocalhost -uroot -p test -e "load data LOCAL infile '/batchfile/mysql/data/test/demo\_info.del' into table demo\_info character set utf8 fields terminated by 0x0f"

注意：如果MySQL服务端在Linux，load data infile默认是加载服务端路径的数据文件，指定LOCAL表示加载的是客户端的本地数据文件~

三、工具mysqldump实现

MySQL 自带mysqldump 工具，工具文件在bin目录下，不仅可以导出和导入表数据，还可以选择性的导出库表（整库、多库、单库、多表、单表）结构，是数据库备份的方途径之一~
同样本文以Windows下为例，Linux区别在于路径不同~
操作本地：mysqldump -u数据库用户 -p xxx
操作远程：mysqldump -hIP地址 -P端口号 -p xxx

3.1 导出

3.1.1 数据库

打开cmd命令窗，进入到bin目录下：

cd C:\Program Files\MySQL\MySQL Server 5.7\bin

（1）导出所有数据库（结构+数据）

mysqldump -uroot -p --all-databases > C:/Users/Administrator/Desktop/all_databases.sql

（2）导出指定数据库（结构+数据）

mysqldump -uroot -p --databases test > test.sql

也可以指定多个数据库（结构+数据）

mysqldump -u root -p --databases test test2 > test_test2.sql
3.1.2 数据表

（1）导出指定数据表（结构+数据）

mysqldump -u root -p --set-gtid-purged=OFF test demo_info > demo_info.sql

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/965968.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

基于LMStudio本地部署DeepSeek R1

基于LMStudio本地部署DeepSeek R1

DeepSeek R1 DeepSeek R1是由DeepSeek团队开发的一款高性能AI推理模型，其开源版本包括完整的DeepSeek R1 671B权重，以及基于其蒸馏出的多个小型模型。 DeepSeek R1通过蒸馏技术将推理模式迁移到更小的模型中，显著提升了这些模型的推理能力。…
阅读更多...
#渗透测试#批量漏洞挖掘#Splunk Enterprise for Windows 任意文件读取漏洞（ CVE-2024-36991）

#渗透测试#批量漏洞挖掘#Splunk Enterprise for Windows 任意文件读取漏洞（ CVE-2024-36991）

免责声明 本教程仅为合法的教学目的而准备，严禁用于任何形式的违法犯罪活动及其他商业行为，在使用本教程前，您应确保该行为符合当地的法律法规，继续阅读即表示您需自行承担所有操作的后果，如有异议，请立即停…
阅读更多...
读书笔记--分布式架构的异步化和缓存技术原理及应用场景

读书笔记--分布式架构的异步化和缓存技术原理及应用场景

本篇是在上一篇的基础上，主要对分布式应用架构下的异步化机制和缓存技术进行学习，主要记录和思考如下，供大家学习参考。大家知道原来传统的单一WAR应用中，由于所有数据都在同一个数据库中，因此事务问题一般借助数据库事…
阅读更多...
【提示词工程】探索大语言模型的参数设置：优化提示词交互的技巧

【提示词工程】探索大语言模型的参数设置：优化提示词交互的技巧

在与大语言模型（Large Language Model, LLM）进行交互时，提示词的设计和参数设置直接影响生成内容的质量和效果。无论是通过 API 调用还是直接使用模型，掌握模型的参数配置方法都至关重要。本文将为您详细解析常见的参数设置及其应用场景，帮助您更高效地利用大语言模型。 …
阅读更多...
（七）QT——消息事件机制＆绘图＆文件

（七）QT——消息事件机制＆绘图＆文件

目录 前言 消息事件机制 (Event System) 绘图 (Graphics & Drawing) 绘图设备 Qt 提供的主要绘图设备 Qt 主要绘图设备的特点 各个绘图设备的详细介绍 文件处理 (File Handling) 总结 前言 QT 是一个非常强大的图形用户界面（GUI）开发框架&…
阅读更多...
ChatGPT提问技巧：行业热门应用提示词案例-文案写作

ChatGPT提问技巧：行业热门应用提示词案例-文案写作

ChatGPT 作为强大的 AI 语言模型，已经成为文案写作的得力助手。但要让它写出真正符合你需求的文案，关键在于如何与它“沟通”，也就是如何设计提示词（Prompt）。以下是一些实用的提示词案例，帮助你解锁 ChatG…
阅读更多...
C++服务端开发注意事项总结

C++服务端开发注意事项总结

文章目录 一、架构设计1. 选择合适的网络框架2. 确定并发模型3. 模块化设计 二、性能优化1. 优化内存管理2. 减少锁的使用3. 优化网络通信 三、安全性1. 输入验证2. 使用安全的通信协议3. 防止拒绝服务攻击（DoS） 四、可维护性1. 日志记录2. 代码注释3. 单…
阅读更多...
idea中git的简单使用

idea中git的简单使用

提交，推送直接合并 合到哪个分支就到先切到哪个分支
阅读更多...
Kubernetes 中 BGP 与二层网络的较量：究竟孰轻孰重？

Kubernetes 中 BGP 与二层网络的较量：究竟孰轻孰重？

如果你曾搭建过Kubernetes集群，就会知道网络配置是一个很容易让人深陷其中的领域。在负载均衡器、服务通告和IP管理之间，你要同时应对许多变动的因素。对于许多配置而言，使用二层（L2）网络就完全能满足需求。但边界网关协议（BGP）—— 支撑互联网运行的技术 —— 也逐渐出…
阅读更多...
LSSVM最小二乘支持向量机多变量多步光伏功率预测（Matlab）

LSSVM最小二乘支持向量机多变量多步光伏功率预测（Matlab）

代码下载：LSSVM最小二乘支持向量机多变量多步光伏功率预测（Matlab） LSSVM最小二乘支持向量机多变量多步光伏功率预测 一、引言 1.1、研究背景与意义 随着全球能源危机和环境问题的日益严重，可再生能源的开发利用成为了世界各国…
阅读更多...
docker容器运行时忘了加自动重启命令了，之后如何添加自动重启命令，使其随开机自动重启

docker容器运行时忘了加自动重启命令了，之后如何添加自动重启命令，使其随开机自动重启

要让已有的Docker容器在系统重启后自动启动，可以通过以下步骤设置其重启策略： 步骤 1：查找容器名称或ID docker ps -a找到目标容器的ID或名称。 步骤 2：更新容器的重启策略 使用 docker update 命令直接修改容器的重启策略&am…
阅读更多...
第16章 Single Thread Execution设计模式（Java高并发编程详解：多线程与系统设计）

第16章 Single Thread Execution设计模式（Java高并发编程详解：多线程与系统设计）

简单来说， Single Thread Execution就是采用排他式的操作保证在同一时刻只能有一个线程访问共享资源。 1.机场过安检 1.1非线程安全 先模拟一个非线程安全的安检口类，旅客(线程)分别手持登机牌和身份证接受工作人员的检查，示例代码如所示。…
阅读更多...
深度学习：解码智能的“数字炼金术”

深度学习：解码智能的“数字炼金术”

深度学习：解码智能的“数字炼金术” 1943年，当神经科学家沃伦麦卡洛克和数学家沃尔特皮茨在论文中首次提出人工神经元模型时，他们或许没有想到，这个简单的数学公式会在80年后掀起改变人类文明的技术革命。深度学习作为这场革命的…
阅读更多...
让文物“活”起来，以3D数字化技术传承文物历史文化！

让文物“活”起来，以3D数字化技术传承文物历史文化！

文物，作为不可再生的宝贵资源，其任何毁损都是无法逆转的损失。然而，当前文物保护与修复领域仍大量依赖传统技术，同时，文物管理机构和专业团队的力量相对薄弱，亟需引入数字化管理手段以应对挑战。 积木易搭…
阅读更多...
pytest-xdist 进行多进程并发测试

pytest-xdist 进行多进程并发测试

在自动化测试中，运行时间过长往往是令人头疼的问题。你是否遇到过执行 Pytest 测试用例时，整个测试流程缓慢得让人抓狂？别担心，pytest-xdist 正是解决这一问题的利器！它支持多进程并发执行，能够显著加快测试…
阅读更多...
广度优先搜索（BFS）算法详解——以走迷宫问题为例

广度优先搜索（BFS）算法详解——以走迷宫问题为例

引言：当算法遇见迷宫 想象你置身于一个复杂的迷宫，如何在最短时间内找到出口？这个问题不仅存在于童话故事中，更是计算机科学中经典的路径搜索问题。本文将带你通过走迷宫问题，深入理解广度优先搜索（BFS&am…
阅读更多...
kubeadm构建k8s源码阅读环境

kubeadm构建k8s源码阅读环境

目标 前面看了minikube的源码了解到其本质是调用了kubeadm来启动k8s集群，并没有达到最初看代码的目的。 所以继续看看kubeadm的代码，看看能否用来方便地构建源码调试环境。 k8s源码编译 kubeadm源码在k8s源码库中，所以要先克隆k8s源码。之…
阅读更多...
BFS算法篇——广度优先搜索，探索未知的旅程（上）

BFS算法篇——广度优先搜索，探索未知的旅程（上）

文章目录 前言一、BFS的思路二、BFS的C语言实现1. 图的表示2. BFS的实现 三、代码解析四、输出结果五、总结 前言 广度优先搜索（BFS）是一种广泛应用于图论中的算法，常用于寻找最短路径、图的遍历等问题。与深度优先搜索（DFS&…
阅读更多...
baigeiRSA

baigeiRSA

baigeiRSA 打开附件有两个： 1.import libnumfrom Crypto.Util import numberfrom secret import flag​size 128e 65537p number.getPrime(size)q number.getPrime(size)n p*q​m libnum.s2n(flag)c pow(m, e, n)​print(n %d % n)print(c %d % c)​​2.n…
阅读更多...
脚本一键生成管理下游k8s集群的kubeconfig

脚本一键生成管理下游k8s集群的kubeconfig

一、场景 1.1 需要管理下游k8s集群的场景。 1.2 不希望使用默认的cluster-admin权限的config. 二、脚本 **重点参数： 2.1 配置变量。 1、有单独namespace的权限和集群只读权限。 2、自签名的CA证书位置要正确。 2.2 如果配置错误，需要重新…
阅读更多...
最新文章

Copyright @ 2022~2023