让大家帮我写一段这种需求的mysql存储过程

Question

需求: 查询指定库中所有的表中字段记录为空的字段，把这些字段删除掉。注：如果某张表中有100条记录，但有一个字段只在某一条记录中有值，此字段就不能删除。要完全为空才能删除。感谢。100分送上。

Answer 1

我试着写了一个简单的脚本，
具体思路：
使用嵌套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 2

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 3

用游标去循环所有记录，分别查看所有字段是否有记录，如果没有，就DROP。如1楼说的，表必须有一个字段存在，如果全是空的，也得保留一个字段。

Answer 4

非常感谢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