等待版主和高人路过，动态生成txt文件问题的继续

Question

本帖最后由 bluelive 于 2009-08-27 12:49:01 编辑

Answer 1

DELIMITER $$
DROP PROCEDURE IF EXISTS p_Level2$$
CREATE PROCEDURE p_Level2(in s_path varchar(1000))
BEGIN
declare l_user_name varchar(50);
declare l_mobile_id varchar(255);
declare l_mobile varchar(25);
  declare done int default 0; declare l_cur cursor for
select * from tb1;
  declare continue handler for not found set done=1; open l_cur;
fetch l_cur into l_user_name,l_mobile_id; while (done=0) do
  select group_concat(Mobile) into @v_mobile
          from tb2 where find_in_set(id,l_mobile_id);   set @sqlstmt=concat('select ''',@v_mobile,','' UNION ALL SELECT ''welcome!''
 into outfile "',s_path,l_user_name,'_',DATE_FORMAT(now(),'%Y%m%d'),'.txt"',
                 ' lines terminated by ''\r\n'''); prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;
fetch l_cur into l_user_name,l_mobile_id;
end while;
close l_cur;
end$$
DELIMITER ;
/*
LiSi_20090827.txt
213,321,
welcome!
ZhangSan_20090827.txt
123,
welcome!*/

Answer 2

这样改下：
DELIMITER //     
drop procedure if exists P_Level2//
CREATE PROCEDURE P_Level2()
begin
declare l_user_name varchar(50);
declare l_mobile_id varchar(255);
declare l_mobile varchar(25);
declare v_i int default 0;
declare v_mobile varchar(500);declare l_cur cursor for 
        select * from A; 
declare continue handler for not found set v_i = 1;
        
open l_cur;
fetch l_cur into l_user_name,l_mobile_id;
while v_i=0 do      
-- 下面的报错，目标是循环获取手机号，代码不知怎么写

select group_concat(mobile) into v_mobile from B where find_in_set(id,l_mobile_id);

-- 下面的代码的换行符输后，在第一行显示“\”，试了很多方法也不行。
if v_mobile is not null then
set @sqlstmt=concat('select concat("',v_mobile,'",'','') as field1 union all select "欢迎你" into outfile "',l_user_name,"_",DATE_FORMAT(now(),'%Y%m%d'),'.txt"');
prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;
end if;
fetch l_cur into l_user_name,l_mobile_id;
end while;
close l_cur;
end;//

Answer 3

我是用windows操作系统测试的..linux将\r\n改为\n.

Answer 4

try:
DELIMITER $$
DROP PROCEDURE IF EXISTS rr$$
CREATE PROCEDURE `test`.`rr`()
       BEGIN
    DECLARE l_user_name VARCHAR(50);
DECLARE l_mobile_id VARCHAR(255);
DECLARE l_mobile VARCHAR(25);
DECLARE done INT DEFAULT 0;
DECLARE l_cur CURSOR FOR 
        SELECT NAME,CONCAT(GROUP_CONCAT(Mobile),',') FROM (
SELECT a.Mobile,b.Name FROM bb a LEFT JOIN ba b ON INSTR(b.id,a.Id)>0) c GROUP BY NAME; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN l_cur;
FETCH l_cur INTO l_user_name,l_mobile_id;
WHILE done=0 DO
SET @sqlstmt=CONCAT('SELECT "', l_mobile_id,'" union all select "欢迎您"',' INTO OUTFILE "j://zz//',l_user_name,'.txt" lines terminated by "\r\n"' );
SELECT @sqlstmt;
PREPARE stmt1 FROM @sqlstmt;
             EXECUTE stmt1 ;
              DEALLOCATE PREPARE stmt1;
              FETCH l_cur INTO l_user_name,l_mobile_id;
       END WHILE;
        END$$DELIMITER ;

Answer 5

mysql> delimiter //
mysql> drop procedure if exists P_Level2//
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE P_Level2()
    -> begin
    -> declare l_user_name varchar(50);
    -> declare l_mobile_id varchar(255);
    -> declare l_mobile varchar(25);
    -> declare v_i int default 0;
    -> declare v_mobile varchar(500);
    ->
    -> declare l_cur cursor for
    ->         select * from A;
    -> declare continue handler for not found set v_i = 1;
    ->
    -> open l_cur;
    -> fetch l_cur into l_user_name,l_mobile_id;
    -> while v_i=0 do
    ->
    ->  select group_concat(mobile) into v_mobile from B where find_in_set(id,l_
mobile_id);
    ->
    ->  if v_mobile is not null then
    ->  set @sqlstmt=concat('select concat("',v_mobile,'",'','') as field1 union
 all select ''欢迎你'' into outfile "E://test//',l_user_name,"_",DATE_FORMAT(now
(),'%Y%m%d'),'.txt"');
    ->  prepare stmt from @sqlstmt;
    ->  execute stmt;
    ->  deallocate prepare stmt;
    ->  end if;
    ->  fetch l_cur into l_user_name,l_mobile_id;
    -> end while;
    -> close l_cur;
    -> end;
    -> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> select * from a;
+------+------+
| Name | ID   |
+------+------+
| 张三     | 1    |
| 李四     | 2,3  |
+------+------+
2 rows in set (0.00 sec)mysql> select * from b;
+------+--------+
| ID   | mobile |
+------+--------+
|    1 | 123    |
|    2 | 213    |
|    3 | 321    |
+------+--------+
3 rows in set (0.00 sec)mysql>
mysql> call P_Level2();
Query OK, 0 rows affected (0.00 sec)mysql>E:\test>dir *.txt
 驱动器 E 中的卷没有标签。
 卷的序列号是 3EB6-BACA E:\test 的目录2009-08-27  14:44                12 张三_20090827.txt
2009-08-27  14:44                16 李四_20090827.txt
               2 个文件             28 字节
               0 个目录 18,177,867,776 可用字节E:\test>type *.txt张三_20090827.txt
123,
欢迎你李四_20090827.txt
213,321,
欢迎你E:\test>

Answer 6

去掉 \，加入FIELDS ESCAPED BY ""
DELIMITER $$
DROP PROCEDURE IF EXISTS rr$$
CREATE PROCEDURE `test`.`rr`()
    
    BEGIN
    DECLARE l_user_name VARCHAR(50);
DECLARE l_mobile_id VARCHAR(255);
DECLARE l_mobile VARCHAR(25);
DECLARE done INT DEFAULT 0;
DECLARE l_cur CURSOR FOR 
        SELECT NAME,CONCAT(GROUP_CONCAT(Mobile),',',CHAR(13),CHAR(10),'欢迎您！') FROM (
SELECT a.Mobile,b.Name FROM bb a LEFT JOIN ba b ON INSTR(b.id,a.Id)>0) c GROUP BY NAME; 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;OPEN l_cur;
FETCH l_cur INTO l_user_name,l_mobile_id;
WHILE done=0 DO
SET @sqlstmt=CONCAT('SELECT "', l_mobile_id,'" INTO OUTFILE "j://zz//',l_user_name,'.txt" FIELDS ESCAPED BY ""' );
SELECT @sqlstmt;
PREPARE stmt1 FROM @sqlstmt;
             EXECUTE stmt1 ;
              DEALLOCATE PREPARE stmt1;
              FETCH l_cur INTO l_user_name,l_mobile_id;
       END WHILE;
        END$$DELIMITER ;

Answer 7

各位高人先多发表下意见，现在俺要参加培训，晚些再试试，CSDN的高人果然多，哈哈。

Answer 8

mysql> select * from a;
+------+------+
| name | id   |
+------+------+
| 张三 | 1    |
| 李四 | 2,3  |
+------+------+
2 rows in set (0.00 sec)mysql> select * from b;
+----+--------+
| id | mobile |
+----+--------+
|  1 | 123    |
|  2 | 213    |
|  3 | 321    |
+----+--------+
3 rows in set (0.00 sec)mysql> select a.name,group_concat(b.mobile)
    -> from a,b
    -> where find_in_set(b.id,a.id)
    -> group by a.name;
+------+------------------------+
| name | group_concat(b.mobile) |
+------+------------------------+
| 李四 | 213,321                |
| 张三 | 123                    |
+------+------------------------+
2 rows in set (0.05 sec)mysql> delimiter //
mysql> Create Procedure p_bluelive()
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->  DECLARE v_1 VARCHAR(10);
    ->  DECLARE v_2 VARCHAR(10);
    ->  DECLARE cur1 CURSOR FOR select a.name,group_concat(b.mobile)
    ->          from a,b
    ->          where find_in_set(b.id,a.id)
    ->          group by a.name;
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->  OPEN cur1;
    ->  FETCH cur1 INTO v_1,v_2;
    ->  WHILE done=0 DO
    ->        set @sqlstmt=concat('select \'',v_2,',\' union all select \'欢迎您！\' into outfile \'',v_1,'.txt\'');
    ->        PREPARE stmt1 FROM @sqlstmt;
    ->        EXECUTE stmt1 ;
    ->        DEALLOCATE PREPARE stmt1;
    ->        FETCH cur1 INTO v_1,v_2;
    ->  END WHILE;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> call p_bluelive();
Query OK, 0 rows affected (0.02 sec)mysql>
[code=BatchFile]C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>dir *.txt
 Volume in drive C is xxx
 Volume Serial Number is xxxx Directory of C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn08/27/2009  03:24p                  14 张三.txt
08/27/2009  03:24p                  18 李四.txt
               2 File(s)             32 bytes
               0 Dir(s)  29,677,927,936 bytes freeC:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>type *.txt张三.txt
123,
欢迎您！李四.txt
213,321,
欢迎您！C:\Program Files\MySQL\MySQL Server 5.1\DATA\csdn>
[/code]

Answer 9

首先谢谢各位！目前已成功生成TXT文件，这次低调些，先不急着结贴。
现在存在一个问题，原数据库的字符集为GBK，导出的TXT文件也为GBK，现在上传到Linux则变为乱码，现需输出utf-8格式的TXT文件？ into outfile能够设置输出的中文编码？

Answer 10

into outfile能够设置输出的中文编码？
不能
建立一个新的数据库，字符集设置为UTF8，再运行SP，生成TXT即为UTF8的了

Answer 11

这样问题已经不在 outfile 上了，应该是看你如何传。你是上传是如何实现的？

Answer 12

通过Windows的Ftp到linux（Ubuntu）平台上，在Ubuntu重新转码后，则可以成功发送短信，否则收到的短信时乱码。

Answer 13

另外因其他应用程序，字符集必须为GBK。

Answer 14

windows / unix 的文本文件格式不一样。你用FTP的时候 ascii 的状态是什么？ftp> ascii
200 TYPE set to ASCII.
ftp>

Answer 15

都试过，但仍然是乱码，mysql如果能在导出文件时设定导出的字符集该多好，现在估计只能在Ubuntu那里想办法了。

Answer 16

谢谢各位的指教。
貌似Ubuntu那里真有办法，明天再理下思绪和结贴。

Answer 17

一切搞定，在Ubuntu里有recode可以重新转换文件编码。大致总结下此次case的感受：
1.Mysql的函数很强大，group_concat，find_in_set等，以后好好研究函数；
2.动态SQL也值得研究，发现自己竟然没发现oracle中也有此功能，汗；
3.Mysql导出文件时可以通过Union all来换行，也从没了解过。
总之，大把的需要学习，不过平时用不到，估计研究的也少，不过至少开阔了思路，以后就好办。

Answer 18

3.Mysql导出文件时可以通过Union all来换行，也从没了解过。
\r\n
or
char(13) char(10)
导出时加
FIELDS ESCAPED BY ""

Answer 19

一个锦上添花的问题：
Mysql中是否有判断两个日期间隔多少工作日（排除周六周日和节日）的函数？如果没有的话，哪位大大有现成的函数，也望不吝赐教。:-)

Answer 20

你可以看看ACMAIN_CHM大大的源码，我已经实现了，生成的文件也OK。

Answer 21

3.Mysql导出文件时可以通过Union all来换行，也从没了解过。 因为里面这过程就是进行记录的合并，导出记录时本身就可以自动换行了，无需再加 \r\n

Answer 22

:-(
真是对不住，没测试过，不过我想应该OK的，呵呵，结贴时会给大大分的。
只是感觉上ACMAIN_CHM大大的源码很规范整洁，适于教学，适于俺这样的懒人，所以套用他的了。

Answer 23

新的问题，新开一贴了。现在结贴。
http://topic.csdn.net/u/20090828/10/2eff22fb-11de-4efa-872c-952bcc7b5e2f.html

Answer 24

可以说没哪个数据库会有这个函数。因为法定假日是不固定日期的的，比如春节。
一般来说常见的解决方法是create table calendar (cdate date PK,  workType int) // worktyep: 0 工作日， 7 周未， 9 法定假然后就简单了  select count(*) from calendar where cdate between date1 and date2 and worktype=0;