求一小段MYSQL的存储过程我以写了一半

Question

需求: 查询指定库中所有的表中字段记录为空的字段，把这些字段删除掉。注：如果某张表中有100条记录，但有一个字段只在某一条记录中有值，此字段就不能删除。要完全为空才能删除。存储过程代码我以写了一小部份，但存储过程实在不熟悉，所以求大家帮我写一下。感谢。100分送上。BEGIN
/*局部变量的定义 */
declare tmpName varchar(200) default '' ;
 declare  sum1 int;
 declare  sum2 int;
declare resultNames cursor FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'department' AND table_schema = 'sz8_news';declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;OPEN resultNames;/*打开游标*/FETCH resultNames into tmpname; -- 取数据
/* 循环体 */
WHILE ( tmpname is not null) DO set @count = "select count(*) into @sum1 from department";/*总记录数*/
 set @sql = concat('select count(*) into @sum2 from department where ',tmpname," is null;");/*为空的总记录数*/  PREPARE stmt1 FROM @count ;
  EXECUTE stmt1 ;
  DEALLOCATE PREPARE stmt1;  PREPARE stmt2 FROM @sql;
  EXECUTE stmt2;
  DEALLOCATE PREPARE stmt2;  set sum1 = @sum1+0;
  set sum2 = @sum2+0;/*判断字段是否为空*/
if sum1==sum2 then
     -- 还需要大家帮我写两个循环及删除语句,这一段我写了一天了.存储过程不熟写得好累.
        -- select sum1,sum2;
end if;
  /*游标向下走一步*/
FETCH resultNames INTO tmpname;
END WHILE;CLOSE resultNames; /*关闭游标*/
END

Answer 1

declare resultNames cursor FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'department' AND table_schema = 'sz8_news';
你到底是 “指定库中所有的表” 还是 仅查这个 table_name = 'department'  ？

Answer 2

ACMAIN_CHM
终于看到你了。。我给你发站内短信了。你回我吧。我找的就是你。你到底是 “指定库中所有的表” 还是 仅查这个 table_name = 'department' ？
是“指定库中所有的表”哈。。我现在还没有加入循环功能。因为我不会。所以就只针对单个表来作测试。

Answer 3

我试着写了一个简单的脚本，
具体思路：
使用嵌套cursor, 逐表取列，如果该列is not null的count(1)值为0，则drop 该列, 生成alter table语句，插入临时表。(注：不能直接生成该语句， 马上执行，这样会影响当前cursor获取下一条记录)完整的存储过程如下：
delimiter //CREATE PROCEDURE drop_nullcolumns(IN userName varchar(256))
BEGIN
    DECLARE v int DEFAULT 0;
    DECLARE tname varchar(200) default '' ;
    DECLARE done int DEFAULT 0;
    DECLARE tmpName varchar(200) default '' ;
    DECLARE alterStmt varchar(512) default '';
    
    DECLARE tableNames cursor FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = userName;
    DECLARE resultNames cursor FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = tname AND table_schema = userName;
    DECLARE tmpCursor cursor FOR select stmt from tmp_dropnullcolumns;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
    /* DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; */
    
    SET @x=0;
    SET @alterstr='';
    /* select tname; */
    
    /* create a temporary table to store the 'alter table ' statement */
    create temporary table if not exists tmp_dropnullcolumns(stmt VARCHAR(256));
    
    
    OPEN tableNames;
    
    WHILE ( done = 0 ) DO
        FETCH tableNames into tname;
        if not done then
            /* select tname; */
            OPEN resultNames;
            WHILE (done = 0) DO
                FETCH resultNames into tmpName;
                if not done then
                    /* select tmpName; */
                    set @sqlstr = concat('select count(1) into @x from ', tname, ' where ', tmpName, ' is not null');
                    prepare stmt from @sqlstr;
                    execute stmt;
                    deallocate prepare stmt;
                    if (@x = 0) then
                      set @alterstr = concat('alter table ', tname, ' drop column ', tmpName);
                      select @alterstr;
                      insert into tmp_dropnullcolumns values(@alterstr);
                    end if;
                end if;
            END WHILE;
            CLOSE resultNames;
            set done = 0;
        end if;
    END WHILE;
    set done = 0;
    CLOSE tableNames;    OPEN tmpCursor;
    WHILE ( done = 0 ) DO
        if not done then
            FETCH tmpCursor into alterStmt;
            select alterStmt;
            set @sqlstr = concat (' ', alterStmt);
            prepare stmt from @sqlstr;
            execute stmt;
            deallocate prepare stmt;
        end if;
    END WHILE;
    CLOSE tmpCursor;
    DROP table tmp_dropnullcolumns;
END
//以下是执行结果：
mysql> call drop_nullcolumns('test');//
+---------------------------------+
| @alterstr                       |
+---------------------------------+
| alter table test drop column id |
+---------------------------------+
1 row in set (0.19 sec)+-----------------------------------+
| @alterstr                         |
+-----------------------------------+
| alter table test drop column name |
+-----------------------------------+
1 row in set (0.22 sec)+------------------------------------------+
| @alterstr                                |
+------------------------------------------+
| alter table zsystempage drop column site |
+------------------------------------------+
1 row in set (0.53 sec)+-----------------------------------------+
| @alterstr                               |
+-----------------------------------------+
| alter table zsystempage drop column url |
+-----------------------------------------+
1 row in set (0.55 sec)+------------------------------------------------+
| @alterstr                                      |
+------------------------------------------------+
| alter table zsystempage drop column updatetime |
+------------------------------------------------+
1 row in set (0.58 sec)+--------------------------------------------+
| @alterstr                                  |
+--------------------------------------------+
| alter table zsystempage drop column length |
+--------------------------------------------+
1 row in set (0.59 sec)+----------------------------------------------+
| @alterstr                                    |
+----------------------------------------------+
| alter table zsystempage drop column filename |
+----------------------------------------------+
1 row in set (0.61 sec)+---------------------------------+
| alterStmt                       |
+---------------------------------+
| alter table test drop column id |
+---------------------------------+
1 row in set (0.63 sec)+-----------------------------------+
| alterStmt                         |
+-----------------------------------+
| alter table test drop column name |
+-----------------------------------+
1 row in set (0.75 sec)ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
mysql> show tables; //
+----------------+
| Tables_in_test |
+----------------+
| a              |
| a2             |
| aa             |
| t123           |
| t5             |
| tbigint        |
| tdate          |
| test           |
| tt             |
| zcleanurl      |
| zsystempage    |
+----------------+
11 rows in set (0.00 sec)mysql> desc test;//
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(32) | YES  |     | a       |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.02 sec)仅供参考。实际上，如果某表所有字段都是空，没有值，最后只能剩下一个字段，不能将所有字段全部清除。其实，写一个jdbc的java小程序，也许来得更快。思路都是一样的。

Answer 4

mysql> select * from t1;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | NULL | NULL | NULL |
|    2 |    2 | NULL | NULL |
+------+------+------+------+
2 rows in set (0.00 sec)mysql> select * from t2;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | NULL |    1 | NULL |
|    2 | NULL |    2 |    2 |
+------+------+------+------+
2 rows in set (0.00 sec)mysql> select * from t3;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | NULL |    1 | NULL |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
2 rows in set (0.00 sec)mysql> delimiter //
mysql> drop PROCEDURE p_maoweiwer//
Query OK, 0 rows affected (0.00 sec)mysql> CREATE PROCEDURE p_maoweiwer()
    -> BEGIN
    ->  DECLARE done INT DEFAULT 0;
    ->
    ->  DECLARE cTbl varchar(64);
    ->  DECLARE cCol varchar(64);
    ->
    ->  DECLARE cur1 CURSOR FOR
    ->          select TABLE_NAME,COLUMN_NAME
    ->          from INFORMATION_SCHEMA.COLUMNS
    ->          where TABLE_SCHEMA='maoweiwer'
    ->          and IS_NULLABLE='YES'
    ->          order by TABLE_NAME;
    ->  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    ->
    ->  set @sqlDrop='';
    ->  OPEN cur1;
    ->  FETCH cur1 INTO cTbl, cCol;
    ->  WHILE done = 0 DO
    ->          set @x=0;
    ->          set @sql=concat('select 1 into @x from `',cTbl,'` where `',cCol,'` is not null limit 1');
    ->          PREPARE stmt1 FROM @sql;
    ->          EXECUTE stmt1;
    ->          DEALLOCATE PREPARE stmt1;
    ->          if @x=0 then
    ->                  set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');
    ->                  PREPARE stmt1 FROM @sqlDrop;
    ->                  EXECUTE stmt1;
    ->                  DEALLOCATE PREPARE stmt1;
    ->          end if ;
    ->          set done = 0;
    ->          FETCH cur1 INTO cTbl, cCol;
    ->  END WHILE;
    ->  CLOSE cur1;
    ->
    -> END//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql>
mysql> call p_maoweiwer();
Query OK, 0 rows affected (0.13 sec)mysql> select * from t1;
+------+------+
| id   | col1 |
+------+------+
|    1 | NULL |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)mysql> select * from t2;
+------+------+------+
| id   | col2 | col3 |
+------+------+------+
|    1 |    1 | NULL |
|    2 |    2 |    2 |
+------+------+------+
2 rows in set (0.00 sec)mysql> select * from t3;
+------+------+------+------+
| id   | col1 | col2 | col3 |
+------+------+------+------+
|    1 | NULL |    1 | NULL |
|    2 |    2 |    2 |    2 |
+------+------+------+------+
2 rows in set (0.00 sec)mysql>

Answer 5

现在才上网，一回家就急着打开CSDN BBS 看有人回复了没。感谢楼上的二位哈。有兄弟说到用JDBC 对的，其实用JDBC更来得快，JDBC相对来说我也更熟悉，但主要是我想用JAVA调用存储过程或独立于程序，直接在数据库层上来操作，一是学点东西，二是也有这个需要。
先不说了我先看下你们写的。膜拜一下。

Answer 6

非常感谢ACMAIN_CHM、iihero两位同学
马上给分，同时我自己测试了一下，针对我数据库表的特殊需求，把程序进行了一小点的改进。BEGIN
     DECLARE done INT DEFAULT 0;     DECLARE cTbl varchar(64);
     DECLARE cCol varchar(64);     DECLARE cur1 CURSOR FOR
             select TABLE_NAME,COLUMN_NAME
             from INFORMATION_SCHEMA.COLUMNS
             where TABLE_SCHEMA='sz8_news'
             and IS_NULLABLE='YES'
             order by TABLE_NAME;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;     set @sqlDrop='';
     OPEN cur1;
     FETCH cur1 INTO cTbl, cCol;/*得到表名及列名*/
     WHILE done = 0 DO
             set @x=0;
/*主要改进了这里把空值也纳入判断条件中去，即如果字段为null或空*/
     set @sql=concat('select 1 into @x from ',cTbl,' where ',cCol,' is not null and ',cCol,' !=""  limit 1');             PREPARE stmt1 FROM @sql;
             EXECUTE stmt1;
             DEALLOCATE PREPARE stmt1;             if @x=0 then
                     set @sqlDrop=concat('alter table `',cTbl,'` drop COLUMN`',cCol,'`;');                     PREPARE stmt1 FROM @sqlDrop;
                     EXECUTE stmt1;
                     DEALLOCATE PREPARE stmt1;
             end if ;
             set done = 0;
             FETCH cur1 INTO cTbl, cCol;
     END WHILE;
     CLOSE cur1;    END