into outputfile的一个问题 请问下面这个存储过程怎么修改CREATE PROCEDURE dt_test(in vCsvPath varchar(255))select * from tab_info into outfile @vCsvPath 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 可能是存储过程不支持into outfile,直接写不可以吗?SELECT * FROM tab_info into outfile 'D:\\tab_info.txt'; 存储过程是支持into outfile的 1. into outfile @vCsvPath 你定义的变量是 vCsvPath 而不是 @vCsvPath2. intooutfile 只能是文本型的常量(文件名)并且要有'引号!所以改成如下,用MYSQL的prepare 来动态生成SQL语句并执行。mysql> delimiter //mysql> CREATE PROCEDURE dt_test(in vCsvPath varchar(255)) -> begin -> set @sql_dt_test = concat('select * from tab_info into outfile \'',vCsvPath,'\''); -> PREPARE stmt1 FROM @sql_dt_test; -> EXECUTE stmt1; -> DEALLOCATE PREPARE stmt1; -> end//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql>mysql> call dt_test('Cat_Of_9Life.txt');Query OK, 0 rows affected (0.00 sec)mysql>[code=BatchFile]C:\PROGRA~1\MySQL\MYSQLS~1.1\bin>dir ..\data\csdn\*.txt Volume in drive C is xxxxx Volume Serial Number is xxx Directory of C:\PROGRA~1\MySQL\MYSQLS~1.1\data\csdn11/22/2009 05:26p 28 Cat_Of_9Life.txt 1 File(s) 28 bytes 0 Dir(s) 25,731,174,912 bytes freeC:\PROGRA~1\MySQL\MYSQLS~1.1\bin>[/code] 可以,不过要用动态累加字符串,生成动态SQL语句方法 像这种的输入变量,用动态语句处理即可,mysql下动态语句的语法是:PREPARE xxx FROM @str; -- @str为要执行的语句,你可以在前面构造好后,然后传入这里执行就可以了EXECUTE xxx;DEALLOCATE PREPARE xxx; 急,求实现方法???? 求一条SQL语句 mySql查询表达式 mysql innodb_buffer_pool_size table_open_cache 会不会重叠 升星星,无条件散分 mysql5,支持rollback吗,为什么不行 这个插入语句怎么写? mysql建数据库,mysql怎么用脚本建库 select查询问题 关于触发器trigger的问题,触发器的触发条件是否可以限制在满足条件的某几行 这个MySql语句该怎么优化? 还是请教一个交叉排列order by问题
2. intooutfile 只能是文本型的常量(文件名)并且要有'引号!所以改成如下,用MYSQL的prepare 来动态生成SQL语句并执行。mysql> delimiter //
mysql> CREATE PROCEDURE dt_test(in vCsvPath varchar(255))
-> begin
-> set @sql_dt_test = concat('select * from tab_info into outfile \'',vCsvPath,'\'');
-> PREPARE stmt1 FROM @sql_dt_test;
-> EXECUTE stmt1;
-> DEALLOCATE PREPARE stmt1;
-> end//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call dt_test('Cat_Of_9Life.txt');
Query OK, 0 rows affected (0.00 sec)mysql>[code=BatchFile]C:\PROGRA~1\MySQL\MYSQLS~1.1\bin>dir ..\data\csdn\*.txt
Volume in drive C is xxxxx
Volume Serial Number is xxx Directory of C:\PROGRA~1\MySQL\MYSQLS~1.1\data\csdn11/22/2009 05:26p 28 Cat_Of_9Life.txt
1 File(s) 28 bytes
0 Dir(s) 25,731,174,912 bytes freeC:\PROGRA~1\MySQL\MYSQLS~1.1\bin>[/code]
EXECUTE xxx;
DEALLOCATE PREPARE xxx;