Mysql为5.02版本,现需动态生成N个TXT文件(N为记录条数)
例如:
表:A
字段:a,b
数据范例:
a b
1 1
1 2
2 1基础Sql:select count(distinct a) N from A
希望生成出来的文件名为:字段a+“.txt”,例如:1.txt,2.txt等基础Sql:select a,count(*) m from A group by a
文件内容格式为:字段a:a共有m2条记录 例如:字段a:1共有2条记录曾参考一高人的sql(http://www.raid120.net/qtos/100217640.html)改编,结果失败,现到csdn高手出没处看看。
例如:
表:A
字段:a,b
数据范例:
a b
1 1
1 2
2 1基础Sql:select count(distinct a) N from A
希望生成出来的文件名为:字段a+“.txt”,例如:1.txt,2.txt等基础Sql:select a,count(*) m from A group by a
文件内容格式为:字段a:a共有m2条记录 例如:字段a:1共有2条记录曾参考一高人的sql(http://www.raid120.net/qtos/100217640.html)改编,结果失败,现到csdn高手出没处看看。
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
+------+------+
3 rows in set (0.00 sec)mysql> delimiter //
mysql> drop PROCEDURE p_bluelive//
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE p_bluelive()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE v_a INT;
-> DECLARE cur1 CURSOR FOR SELECT distinct a FROM t_bluelive;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
-> FETCH cur1 INTO v_a;
-> WHILE done=0 DO
-> set @sqlstmt=concat('SELECT a,count(*) as N from t_bluelive wher
e a=',v_a,' INTO OUTFILE \'',v_a,'.txt\'');
-> PREPARE stmt1 FROM @sqlstmt;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
-> FETCH cur1 INTO v_a;
-> END WHILE;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> delimiter ;
mysql> CALL p_bluelive();
Query OK, 0 rows affected (0.00 sec)mysql>[code=BatchFile]C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>dir *.txt
Volume in drive C is xxxxxx
Volume Serial Number is xxxxxx Directory of C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn08/25/2009 05:30p 4 1.txt
08/25/2009 05:30p 4 2.txt
2 File(s) 8 bytes
0 Dir(s) 29,682,731,008 bytes freeC:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>type 1.txt
1 2C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>type 2.txt
2 1C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>[/code]
-> set @sqlstmt=concat('SELECT a,count(*) as N from t_bluelive wher
e a=',v_a,' INTO OUTFILE \'',v_a,'.txt\'');
-> PREPARE stmt1 FROM @sqlstmt;
-> EXECUTE stmt1 ;
-> DEALLOCATE PREPARE stmt1;
这段是关键
此处能够解释下作用,还有循环时,done似乎没有变化,如何退出循环呢?
这个是抓异常,当游标FETCH完最后记录时,会抛出这个异常,然后就会将变量done的值赋为1,这样,“WHILE done=0 DO” 就为假了,就退出了循环。