Mysql 从表中动态删除所有空列

发布于08月07日

我有一个非常类似于SELECT statement to remove empty columns from resultset with variable data的情景.

但在我的例子中，我有一个包含300多列的表！

我可以静态地编写准备好的语句中的所有列名，但我想知道是否有一种方法可以通过循环所有列并判断它们是否只有空值，然后跳过它们来实现这一点.

推荐答案

为此，你需要PROCEDURE分、CURSOR分和PREPARED STATEMENT分.

CREATE TABLE mytable2023 (id int, a varchar(10), b varchar(10), c varchar(10))

INSERT INTO mytable2023 VALUES (1,NULL,'B', NULL),(2,NULL,'B1', NULL)

Records: 2  Duplicates: 0  Warnings: 0

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE  TABLE_NAME = 'mytable2023'; #TABLE_SCHEMA = 'my_database' AND

COLUMN_NAME
a
b
c
id

CREATE PROCEDURE delcolumn(
    tbname varchar(100)
)
BEGIN
    DECLARE finished INTEGER DEFAULT 0;

    DECLARE colname varchar(100) DEFAULT "";

    -- declare cursor for employee email
    DEClARE curcol 
        CURSOR FOR 
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE  TABLE_NAME = tbname; #TABLE_SCHEMA = 'my_database' AND

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

    OPEN curcol;

    getcol: LOOP
        FETCH curcol INTO colname;
        IF finished = 1 THEN 
            LEAVE getcol;
        END IF;
        -- build email list
         SET @sql = CONCAT('SELECT COUNT(`',colname,'`) INTO @count FROM `',tbname,'`;');
         PREPARE stmt2 FROM @sql;
         EXECUTE stmt2;
       IF @count = 0 THEN
         SET @sql = CONCAT('ALTER TABLE `',tbname,'` DROP COLUMN `',colname,'`');
         PREPARE stmt2 FROM @sql;
         EXECUTE stmt2;
       END IF;
    END LOOP getcol;
    CLOSE curcol;

END