数据导出

数据导出

把表记录存储到系统文件里

命令格式:
SQL查询 into outfile "目录名/文件名"
"/mydata/库名_文件名.txt";

SQL查询 into outfile "目录名/文件名"
fields terminated by "分隔符" 一般很少用这个
lines terminated by "\n" 一般很少用这个

注意：
导出的内容由SQL查询语句决定
导出的是表中的记录，不包括字段名
禁用selinux

将db3库user表的记录导出，存为/mydata/db3_user.txt文件

mysql> select * from db3.user into outfile "/mydata/db3_user.txt";
mysql> system ls /mydata
db3_user.txt
mysql> system cat -b /mydata/db3_user.txt -b是列出序号
1 1 root x 0 0 root /root /bin/bash
2 2 bin x 1 1 bin /bin /sbin/nologin
... ...

mysql> select name,uid,shell from db3.user;
+---------------------+-------+----------------+
| name | uid | shell |
+---------------------+-------+----------------+
| root | 0 | /bin/bash |
| bin | 1 | /sbin/nologin |
... ...

mysql> select name,uid,shell from db3.user where id<=5;
+--------+------+---------------+
| name | uid | shell |
+--------+------+---------------+
| root | 0 | /bin/bash |
| bin | 1 | /sbin/nologin |
| daemon | 2 | /sbin/nologin |
| adm | 3 | /sbin/nologin |
| lp | 4 | /sbin/nologin |
+--------+------+---------------+

mysql> select name,uid,shell from db3.user where id<=5 into outfile "/mydata/db3_user2.txt";
mysql> system cat -b /mydata/db3_user2.txt
1 root 0 /bin/bash
2 bin 1 /sbin/nologin
... ...

mysql> select name,uid,shell from db3.user where id<=5 into outfile "/mydata/db3_user3.txt" fields terminated by "#";
mysql> system cat -b /mydata/db3_user3.txt;
1 root#0#/bin/bash 不同字段间的分隔符变成了#
2 bin#1#/sbin/nologin
... ...

mysql> select name,uid,shell from db3.user where id<=5 into outfile "/mydata/db3_user4.txt" lines terminated by "!!!";
mysql> system cat -b /mydata/db3_user4.txt;
1 root 0 /bin/bash!!!bin 1 /sbin/nologin!!!daemon 2 /sbin/nologin!!!adm 3 /sbin/nologin!!!lp 4 /sbin/nologin!!!mysql>

原来的不同行之间的分隔符变成了!!!看起来很不整齐，还不如默认不添加时，还能自动对齐并且换行。所以导出一般都不用设置分隔符。