MySQL-3-数据导入导出

一、数据导入导出

1.1、注意事项

• 字段分隔符要与文件中的一致
• 指定导入文件的绝对路径
• 导入数据的表字段类型要与文件字段匹配
• 禁用selinux

1.2、查看默认路径

mysql> show variables like "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

1.3、修改文件导入导出路径

• 在主配置文件中添加secure_file_priv参数
• 创建目录文件并授权给mysqld，关闭selinux

[root@master-51 ~]# echo "secure_file_priv=/dirdata" >> /etc/my.cnf

[root@master-51 ~]# mkdir -p /dirdata && chown -R mysql:mysql /dirdata
[root@master-51 ~]# systemctl restart mysqld

mysql> show variables like "secure_file_priv";
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| secure_file_priv | /dirdata/ |
+------------------+-----------+
1 row in set (0.00 sec)

1.4、数据导入

• 语法格式

mysql> load data infile "目录名/文件名" into table 表名
fields terminated by "分隔符" 	#列分隔符
lines terminated by "\n";		 #行分隔符

• 建表

mysql> desc user;
+----------+-----------+------+-----+---------+-------+
| Field    | Type      | Null | Key | Default | Extra |
+----------+-----------+------+-----+---------+-------+
| name     | char(10)  | YES  |     | NULL    |       |
| password | char(1)   | YES  |     | NULL    |       |
| uid      | int(2)    | YES  |     | NULL    |       |
| gid      | int(2)    | YES  |     | NULL    |       |
| command  | char(100) | YES  |     | NULL    |       |
| homedir  | char(50)  | YES  |     | NULL    |       |
| shell    | char(50)  | YES  |     | NULL    |       |
+----------+-----------+------+-----+---------+-------+

• 添加index索引

mysql> alter table user add index(name);

1.5、数据导出

• 表记录存储到系统文件里
• 本地文件名不能与导出表名相同
• 禁用selinux
• 列分割符不指默认为一个tab键的距离，默认换行
• 语法格式

sql 查询 into outfile "目录名/文件名" fields terminated by "分割符" lines terminated by "\n";
## 换行符可以不写

mysql> select * from user into outfile "/dirdata/user1.txt";

1.6、导入导出格式对比

load data infile "目录名/文件名" into table 表名 fields terminated by "分隔符" lines terminated by "\n";         

sql 查询 into outfile "目录名/文件名" fields terminated by "分隔符";

1.7、复制旧表为新表

相关博客：https://www.csdn.net/tags/NtDaYg2sNjI1OTMtYmxvZwO0O0OO0O0O.html

> 复制旧表结构到新表
create table new_table select * from old_table where 1=2;
或者：create table new_table like old_table;