本文详细介绍了MySQL中数据的导入与导出操作。在MySQL 5.7及以上版本，导入数据时文件需位于特定路径下。数据导入涉及创建对应表结构、使用LOAD DATA INFILE命令，并注意字段分隔符和数据类型匹配。通过示例演示了如何导入系统文件到数据库表，以及在导入过程中遇到的问题及解决方案，如调整表结构以避免数据范围错误。此外，还讲解了数据导出，包括使用SELECT INTO OUTFILE命令将查询结果保存到文件，以及自定义字段和行间隔符。
========================================================================
【【 数据库 03day 】】
数据导入，导出
数据导入：把系统文件的内容存储到数据库的表里。
把文件的内容存储到数据库的表里，对这个文件是有要求的 ，有规律的 有一定的格式。
步骤：
1.创建一个存储文件内容的表，（这个表 的字段，什么类型的 约束 是根据你要存储数据文件内容的而建的）
2.执行导入数据的sql命令 （格式一定要记住）
3. 在mysql 5.7这个版本的这个软件里面 它在导入数据的时候 它要求系统文件必须在默认的导入数据的路径下才可以，5.7之前的版本是没有这个要求的。
show variables like "secure_file_priv"; （看一下mysql的变量variables 像 。。。。）
设置搜索路径
• 查看默认使用目录及目录是否存在
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)
### 如果你想把系统文件 存储到数据库表里；首先你要先把那个文件拷贝到 /var/lib/mysql-files/ 路径下 才可以 默认时候你装软件包的时候，路径已经创建好了！ 这个路径目录是可以修改的。
----------------------------
数据导入
• 基本用法
– LOAD DATA INFILE “ 目录名 / 文件名”
INTO TABLE 表名
FIELDS TERMINATED BY “ 分隔符”
LINES TERMINATED BY “\n”;
• 注意事项
– 字段分隔符要与文件内的一致
– 指定导入文件的绝对路径
– 导入数据的表字段类型要与文件字段匹配
– 禁用 SElinux
## 示例演示：：--->> 把/etc/passwd 导入到数据库中
1 ## 把需要导入数据库 的文件 拷贝到 指定的路径下！
[root@host50 mysql-files]# ls /etc/passwd
/etc/passwd
[root@host50 mysql-files]# head /etc/passwd ## 看一下这个文件的规律， 每一行都是7列 都是用： 号分隔的！ 每一行就像表中的一条记录。
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
[root@host50 mysql-files]#
[root@host50 mysql-files]# pwd
/var/lib/mysql-files
[root@host50 mysql-files]# cp /etc/passwd ./ ## 把目标文件拷贝到 该路径下！！
[root@host50 mysql-files]# head -2 passwd ## 看一下文件内容的格式，及相关的规律！
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
[root@host50 mysql-files]#
2## 建表！！
mysql> use db1;
Database changed
mysql> ##： 根据passwd内容创建一个表
mysql> create table user(
-> name char(30),
-> password char(1),
-> uid smallint(2),
-> gid smallint(2),
-> comment char(100),
-> homedir char(150),
-> shell char(50),
-> index(name)
-> );
Query OK, 0 rows affected (0.39 sec)
mysql> show create table user; ## 查看一下创建表的内容！
--------------------------------------------------------------------------------
-------------+
| user | CREATE TABLE `user` (
`name` char(30) DEFAULT NULL,
`password` char(1) DEFAULT NULL,
`uid` smallint(2) DEFAULT NULL,
`gid` smallint(2) DEFAULT NULL,
`comment` char(100) DEFAULT NULL,
`homedir` char(150) DEFAULT NULL,
`shell` char(50) DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> desc user; ## 看一下表结构 ！
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | char(30) | YES | MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | smallint(2) | YES | | NULL | |
| gid | smallint(2) | YES | | NULL | |
| comment | char(100) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql>
补充：：：：：：
把目标文件拷贝到 指定的路径下
[root@host50 mysql-files]# cp /etc/passwd /var/lib/mysql-files/ ## 用系统命令拷贝！
在mysql数据库登录模式下执行系统命令！ 在前面加 system 结束的时候不用加；号
mysql> system cp /etc/passwd /var/lib/mysql-files/
mysql> system ls /var/lib/mysql-files/
passwd
----------------
3## 导入 导入 ":" passwd这个文件中每一行中列的间隔符就是冒号 行的间隔符 每一行都有\n换行符。
mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n";
ERROR 1264 (22003): Out of range value for column 'uid' at row 30 ## 报错 第30行报错！
mysql>
[root@host50 mysql-files]# sed -n '30p' passwd ## 看一下 30行
nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
[root@host50 mysql-files]#
mysql> select * from user; ## 看一下什么也没有导入 此时就应该修改一下user表结构
Empty set (0.00 sec)
————##解决错误 此时就应该修改一下user表结构——————
mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name | char(30) | YES | MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | smallint(2) | YES | | NULL | |
| gid | smallint(2) | YES | | NULL | |
| comment | char(100) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
mysql> alter table user
-> modify
-> uid int(2),
-> modify gid int(2);
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user;
+----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| name | char(30) | YES | MUL | NULL | |
| password | char(1) | YES | | NULL | |
| uid | int(2) | YES | | NULL | |
| gid | int(2) | YES | | NULL | |
| comment | char(100) | YES | | NULL | |
| homedir | char(150) | YES | | NULL | |
| shell | char(50) | YES | | NULL | |
+----------+-----------+------+-----+---------+-------+
7 rows in set (0.00 sec)
## 再次导入 成功！！！
mysql> load data infile "/var/lib/mysql-files/passwd" into table user fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.04 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
[root@host50 mysql-files]# wc -l passwd ##看一下文件有多少行！
41 passwd
mysql> select * from user; ##看一下表结构 也是41行！
-----------------------------------+---------------------------+----------------+
41 rows in set (0.00 sec)
4## 添加行号 为了方便管理表 可以添加行号！！
mysql> alter table user
-> add
-> id int(2) primary key ##行号字段 一般叫id int数值类型的，宽度是2 设为主键
-> auto_increment ## 自增长
-> first; ## 放在所有字段前面！
mysql> select * from user; ## 查看 可以看到每一行前面都有行号！
## 为什么不刚开始建表的时候就添加行号字段； 不可以 不可以 因为导入数据的时候，源文件内容没有行号，是根据文件内容建表的， 必须导入数据之后才添加行号的！！
## 精确查看！！ 添加行号方便管理！
mysql> select * from user where id=15; ##看一下第15行
mysql> select * from user where id<=3; ## 看一下前三行
-----------------
设置搜索路径(续 1 )
• 修改目录及查看修改结果 （可以不用修改！）
## 默认导入数据的时候 是指定的路径搜索文件的目录。 也可以根据自己 自定义导入文件的时候 文件的存储路径！
mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
mysql>
[root@host50 ~]# vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6
secure_file_priv="/dirdata" ## 自定义存储路径
#
：wq
[root@host50 ~]# mkdir /dirdata ## 系统中要建立相应的文件夹路径！
[root@host50 ~]#
[root@host50 ~]# chown mysql /dirdata ## 更改文件夹的所有者!
[root@host50 ~]# getenforce
Permissive
[root@host50 ~]# systemctl stop mysqld
[root@host50 ~]# systemctl start mysqld
[root@host50 ~]# cp /etc/passwd /dirdata/
[root@host50 ~]# cd /dirdata/
[root@host50 dirdata]# ls
passwd
[root@host50 dirdata]#
mysql> load data infile "/dirdata/passwd" into table user fields terminated by ":" lines terminated by "\n";
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: db1
ERROR 1366 (HY000): Incorrect integer value: 'root' for column 'id' at row 1 ##报错！ 第一行行号字段，字符与数据类型不符
mysql>
## 把第一行 行号字段删除！！
mysql> alter table user drop id;
Query OK, 41 rows affected (0.72 sec)
Records: 41 Duplicates: 0 Warnings: 0
## 重新导入数据（只要导入的数据和表结构不冲突，你想导几次就导几次）
mysql> load data infile "/dirdata/passwd" into table user fields terminated by ":" lines terminated by "\n";
Query OK, 41 rows affected (0.11 sec)
Records: 41 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql> select * from user;
。。。。。。
-----------------------------------+---------------------------+----------------+
82 rows in set (0.00 sec)
## 再添加行号
mysql> alter table user add id int(2) primary key auto_increment first;
mysql> select * from user;
------------------------------------------------------
##### 生产环境中 什么时候需要数据导入
想批量把数据存储导数据库中的用的时候
注意：你要导入的文件的内容，必须是有规律的，如果杂乱无章 是无法导入的。
++++++++++++++ 数据导出 +++++++++++++++
++++++++++++++ 数据导出 +++++++++++++++
导出：： 表记录存储到系统文件里
什么是表记录 就是表中的一行一行的数据，不包括字段名的。字段名是无法导出的
sql 查询 你查询到什么结果 就把什么存储到系统文件里，默认你查询的结果是输出到屏幕上的。
如果不想输出到屏幕上
那么--->
sql 查询 into outfile “目录/ 文件名” （这个目录就是系统指定的目录。 就是看 secure_file_priv=后面跟的路径 “文件名”是导出自定义的，不用事先建立）
mysql> show variables like "secure_file_priv";
+------------------+-----------+
| Variable_name | Value |
+------------------+-----------+
| secure_file_priv | /dirdata/ |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
如果更改了就通过[ root@host50 ~]# head /etc/my.cnf 查看路径！
@@## 导出什么内容 取决于你查询的结果！
---------------
• 基本用法
– SELECT 查询 .. ..
INTO OUTFILE “ 目录名 / 文件名”
FIELDS TERMINATED BY “ 分隔符”
LINES TERMINATED BY “\n”;
• 注意事项
– 导出的内容由 SQL 查询语句决定
– 禁用 SElinux
示例演示01：：--->>>> 把库db1中user表中的数据全部 导出，保存到系统中 命名为user1.txt。
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
mysql> select * from user
-> into outfile
-> "/dirdata/user1.txt";
Query OK, 82 rows affected (0.00 sec)
mysql>
查询结果；；
[root@host50 ~]# cd /dirdata/
[root@host50 dirdata]# ls
passwd user1.txt
[root@host50 dirdata]# ll user1.txt
-rw-rw-rw-. 1 mysql mysql 4461 8月 23 14:46 user1.txt
[root@host50 dirdata]# vim user1.txt
示例演示02：：--->>>> 把库db1中user表中的id,name,password这三列的数据 导出，保存到系统中 命名为user2.txt。
mysql> select id,name,password from user into outfile "/dirdata/user1.txt";
ERROR 1086 (HY000): File '/dirdata/user1.txt' already exists ## user1.txt 已经存在了。
mysql>
mysql>
mysql> select id,name,password from user into outfile "/dirdata/user2.txt";
Query OK, 82 rows affected (0.01 sec)
[root@host50 dirdata]# ls
passwd user1.txt user2.txt
[root@host50 dirdata]#
[root@host50 dirdata]# cat user2.txt
1 root x
2 bin x
3 daemon x
4 adm x
5 lp x
6 sync x
7 shutdown x
..... ....
示例演示03：：--->>>> 把库db1中user表中的id,name,password这三列的数据前三行 导出，保存到系统中 命名为user3.txt。
mysql> select id,name,password from user where id <=3 into outfile "/dirdata/user3.txt";
Query OK, 3 rows affected (0.00 sec)
mysql>
[root@host50 dirdata]# ls
passwd user1.txt user2.txt user3.txt
[root@host50 dirdata]# cat user3.txt
1 root x
2 bin x
3 daemon x
~
~
数据导出的时候， 默认 字段的间隔符 往文件里面存的时候是一个Tab键的距离；默认 记录的（行的）间隔符是换行符号。如user3.txt
可以自定义 字段的间隔符，记录的间隔符。
示例演示:: --->> 指定字段的间隔符 为 ###
mysql> select id,name,password from user into outfile "/dirdata/user4.txt"
-> fields terminated by "###";
Query OK, 82 rows affected (0.00 sec)
mysql>
[root@host50 dirdata]# vim user4.txt
1###root###x
2###bin###x
3###daemon###x
4###adm###x
5###lp###x
6###sync###x
7###shutdown###x
示例演示:: --->> 指定字段的间隔符 为 ### 记录（行的）的间隔符号为!!!
mysql> select id,name,password from user where id <=3 into outfile "/dirdata/user5.txt"
-> fields terminated by ";"
-> lines terminated by "!!!";
Query OK, 3 rows affected (0.01 sec)
[root@host50 dirdata]# ls
passwd user1.txt user2.txt user3.txt user4.txt user5.txt
[root@host50 dirdata]# vim user5.txt
1;root;x!!!2;bin;x!!!3;daemon;x!!!
## 什么时候需要把数据导出！！
我想把表里面的数据打印一份 这时候就需要把数据到出到一个文件里 才能打印。 就是需要批量数据库里面的数据的时候
---------------------------------------------------------------
扫一扫
1224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
