mysql导出导入记录

利用此功能可以将不常变化的数据保存到文件，然后通过文件访问。减少数据库访问。
还可以进行数据备份等。

1.导出文本数据

确保tmp目录是可以写数据的，chmod 777 tmp

select * into outfile “/Users/high/tmp/sql”from t1;

数据默认保存格式为每条记录一行，每个字段用tab隔开。

可以自己指定分割。

fields terminated by“,”enclosed by “” //设置字段用什么包裹，这里设置为空

lines terminated  by“\n” starting  by“start:”

from tb_name 的位置可以随便放置。

select * from tb_name into outfile "/Users/high/tmp/sql3" fields terminated by "," lines terminated by "\n" starting by "start;";

 select * from tb_name into outfile "/Users/high/tmp/sql4" fields terminated by "," enclosed by "[" lines terminated by "\n" starting by "start;";

2.导出二进制数据 

dumpfile不做换行，空格等分割处理。

select * into dumpfile from tb_name;

3.导入数据

可以将上面导出的数据再导入到数据库。 这里注意如果表中有自动增长的主键则导出的时候，需要讲主键设置为NULL，或者手动将导出的主键修改为NULL。否则导入的时候会跟既有的主键冲突。

如下面，将主键设置为NULL

select NULL,name from tb into out file "/Users/high/tmp/sql4"  fields terminated by "," lines terminated by "\n" starting by "start:";

导入文件

load data  infile "filepath" into table tb;

指定格式导入

load data  infile "filepath" into table tb fields terminated by "," lines terminated by "\n" starting by "start:";

<完>