可以用存储过程直接导出文件,参考以下示例:drop procedure if exists sp_export_file_result; create procedure sp_export_file_result() begin declare v_i varchar(20); declare v_filename varchar(100); set v_i = date_format(now(),'%Y%m%d%H%i%s'); set v_filename=concat('"d://result_',v_i,'.txt"'); select @result:= re from tb_test_insert where id=100; set @str = concat('select @result into outfile ',v_filename); select @str; prepare stmt1 from @str; execute stmt1; deallocate prepare stmt1; end;
测试: mysql> call sp_export_file_result(); +------------------+ | @result:= re | +------------------+ | aaaaa | +------------------+ 1 row in set (0.00 sec)+-------------------------------------------------------------+ | @str | +-------------------------------------------------------------+ | select @result into outfile "d://result_20090814161226.txt" | +-------------------------------------------------------------+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call sp_export_file_result(); +------------------+ | @result:= re | +------------------+ | aaaaa | +------------------+ 1 row in set (0.00 sec)+-------------------------------------------------------------+ | @str | +-------------------------------------------------------------+ | select @result into outfile "d://result_20090814161231.txt" | +-------------------------------------------------------------+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)mysql> call sp_export_file_result(); +------------------+ | @result:= re | +------------------+ | aaaaa | +------------------+ 1 row in set (0.00 sec)+-------------------------------------------------------------+ | @str | +-------------------------------------------------------------+ | select @result into outfile "d://result_20090814161232.txt" | +-------------------------------------------------------------+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call sp_export_file_result(); +------------------+ | @result:= re | +------------------+ | aaaaa | +------------------+ 1 row in set (0.00 sec)+-------------------------------------------------------------+ | @str | +-------------------------------------------------------------+ | select @result into outfile "d://result_20090814161233.txt" | +-------------------------------------------------------------+ 1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>D:\>dir d:\result_* 驱动器 D 中的卷没有标签。 卷的序列号是 B87C-4852 d:\ 的目录2009-08-14 16:08 6 result_20090814160855.txt 2009-08-14 16:09 6 result_20090814160900.txt 2009-08-14 16:09 6 result_20090814160901.txt 2009-08-14 16:12 6 result_20090814161226.txt 2009-08-14 16:12 6 result_20090814161231.txt 2009-08-14 16:12 6 result_20090814161232.txt 2009-08-14 16:12 6 result_20090814161233.txt 2009-08-14 16:04 6 result_733998.txt 8 个文件 48 字节 0 个目录 11,863,044,096 可用字节D:\>
+------+
| c1 |
+------+
| 1 |
+------+
1 row in set (0.05 sec)mysql> select c1 into @c from b where w_id=1;
Query OK, 1 row affected (0.00 sec)mysql> select @c;
+------+
| @c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)mysql> select @c INTO OUTFILE 'c:\\aaa.txt' ;
Query OK, 1 row affected (0.00 sec)mysql>
[code=BatchFile]
C:\>type aaa.txt
1C:\>[/code]
SQL codemysql>select c1from bwhere w_id=1;+------+| c1|+------+|1|+------+1 rowinset (0.05 sec)mysql>select c1into@cfrom bwhere w_id=1;
Query OK,1 row affected (0.00 sec)mysql>select@c;+------+|@c|+------+|1|+------+1 rowinset (0.00 sec)mysql>select@cINTO OUTFILE'c:\\aaa.txt' ;
Query OK,1 row affected (0.00 sec)mysql>
版主的方法很好,可是下一次若相同一个文件输出,会覆盖上一个值,如何才能做到不覆盖呢?请教。。非常感谢!
比如你是在操作系统脚本中,则可以把 c:\aaa.txt 文件生成生, copy c:\data.txt + c:\aaa.txt c:\data.txt 把内容合并到data.txt 然后删除 aaa.txt当然在其实编程语言中也很方便来实现。
create procedure sp_export_file_result()
begin
declare v_i varchar(20);
declare v_filename varchar(100);
set v_i = date_format(now(),'%Y%m%d%H%i%s');
set v_filename=concat('"d://result_',v_i,'.txt"');
select @result:= re from tb_test_insert where id=100;
set @str = concat('select @result into outfile ',v_filename);
select @str;
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
end;
mysql> call sp_export_file_result();
+------------------+
| @result:= re |
+------------------+
| aaaaa |
+------------------+
1 row in set (0.00 sec)+-------------------------------------------------------------+
| @str |
+-------------------------------------------------------------+
| select @result into outfile "d://result_20090814161226.txt" |
+-------------------------------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call sp_export_file_result();
+------------------+
| @result:= re |
+------------------+
| aaaaa |
+------------------+
1 row in set (0.00 sec)+-------------------------------------------------------------+
| @str |
+-------------------------------------------------------------+
| select @result into outfile "d://result_20090814161231.txt" |
+-------------------------------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.02 sec)mysql> call sp_export_file_result();
+------------------+
| @result:= re |
+------------------+
| aaaaa |
+------------------+
1 row in set (0.00 sec)+-------------------------------------------------------------+
| @str |
+-------------------------------------------------------------+
| select @result into outfile "d://result_20090814161232.txt" |
+-------------------------------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> call sp_export_file_result();
+------------------+
| @result:= re |
+------------------+
| aaaaa |
+------------------+
1 row in set (0.00 sec)+-------------------------------------------------------------+
| @str |
+-------------------------------------------------------------+
| select @result into outfile "d://result_20090814161233.txt" |
+-------------------------------------------------------------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>D:\>dir d:\result_*
驱动器 D 中的卷没有标签。
卷的序列号是 B87C-4852 d:\ 的目录2009-08-14 16:08 6 result_20090814160855.txt
2009-08-14 16:09 6 result_20090814160900.txt
2009-08-14 16:09 6 result_20090814160901.txt
2009-08-14 16:12 6 result_20090814161226.txt
2009-08-14 16:12 6 result_20090814161231.txt
2009-08-14 16:12 6 result_20090814161232.txt
2009-08-14 16:12 6 result_20090814161233.txt
2009-08-14 16:04 6 result_733998.txt
8 个文件 48 字节
0 个目录 11,863,044,096 可用字节D:\>