outfile ---- 用于导出查询得到的所有数据
dumpfile ---- 用于导出一条数据,通常写入第二条的时候出错,但第二条内容已被写入文件
load_file---- 读取文件所有内容
注意: 实际测试过程中,用户必须具备文件对应的操作权限。
mysql> select User,Password from mysql.user;
+------------------+-------------------------------------------+
| User | Password |
+------------------+-------------------------------------------+
| root | *9CFBBC772F3F6C106020035386DA5BBBF1249A11 |
| root | *9CFBBC772F3F6C106020035386DA5BBBF1249A11 |
| root | *9CFBBC772F3F6C106020035386DA5BBBF1249A11 |
| debian-sys-maint | *8C4C424D182238AFBA8B217F692D07C952EF4087 |
+------------------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> select User,Password from mysql.user into outfile "/tmp/outfile_mysql.user.txt";
Query OK, 4 rows affected (0.00 sec)
mysql> select User,Password from mysql.user into dumpfile "/tmp/dumpfile_mysql.user.txt";
ERROR 1172 (42000): Result consisted of more than one row
mysql> select load_file("/tmp/outfile_mysql.user.txt");
+---------------------------------------------------------------------------------------------------------+
| load_file("/tmp/outfile_mysql.user.txt") |
+---------------------------------------------------------------------------------------------------------+
| root *9CFBBC772F3F6C106020035386DA5BBBF1249A11
| root *9CFBBC772F3F6C106020035386DA5BBBF1249A11
| root *9CFBBC772F3F6C106020035386DA5BBBF1249A11
| debian-sys-maint *8C4C424D182238AFBA8B217F692D07C952EF4087
|
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select load_file("/tmp/dumpfile_mysql.user.txt");
+--------------------------------------------------------------------------------------------+
| load_file("/tmp/dumpfile_mysql.user.txt") |
+--------------------------------------------------------------------------------------------+
| root*9CFBBC772F3F6C106020035386DA5BBBF1249A11root*9CFBBC772F3F6C106020035386DA5BBBF1249A11 |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>