mysql导出表文件_mysql导出表数据到文件的几种方法

方法一：SELECT...INTO OUTFILE

mysql> select * from mytbl into outfile /tmp/mytbl.txt;

Query OK, 3 rows affected (0.00 sec)

查看mytbl.txt中内容如下：

mysql> system cat /tmp/mytbl.txt

1 name1

2 name2

3 \N

导出的文件中数据以制表符分隔，以"\n"为换行符

mysql> system od -c /tmp/mytbl.txt

0000000 1 \t n a m e 1 \n 2 \t n a m e 2 \n

0000020 3 \t \ N \n

0000025

也可以自己制定分隔符和换行符

导出成csv格式

mysql> select * from mytbl into outfile /tmp/mytbl2.txt fields terminated by , enclosed by \" lines terminated by \ \n;

Query OK, 3 rows affected (0.01 sec)

mysql> system cat /tmp/mytbl2.txt

"1","name1"

"2","name2"

"3",\N

导出的文件一定不能已经存在。(这有效的防止了mysql可能覆盖重要文件。)<