mysql - 在 MySQL 表中查找所有那些只有空值的列

Question

情况如下:

我有大量的表，每个表都有大量的列。我需要为新系统处理这个旧的和即将弃用的数据库，我正在寻找一种方法来消除所有显然从未使用过的列。

我想通过过滤掉在任何给定行上具有值的所有列来执行此操作，留下一组所有行中的值为 NULL 的列。当然，我可以手动对每一列进行降序排序，但是这会花费很长时间，因为我要处理大量的表和列。我估计它有 400 个表，每个表最多 50(!)列。

有什么方法可以从 information_schema 中获取此信息？

编辑:

这是一个例子:

column_a    column_b    column_c    column_d
NULL        NULL        NULL        1
NULL        1           NULL        1
NULL        1           NULL        NULL
NULL        NULL        NULL        NULL

输出应该是“column_a”和“column_c”，因为它们是唯一没有任何填充值的列。

Answer 1

您可以通过动态创建(从INFORMATION_SCHEMA.COLUMNS 表)一个包含您希望执行的SQL 的字符串，然后preparing a statement 来避免使用过程。从该字符串并执行它。

我们希望构建的 SQL 如下所示:

SELECT * FROM (
  SELECT 'tableA' AS `table`,
         IF(COUNT(`column_a`), NULL, 'column_a') AS `column`
  FROM   tableA
UNION ALL
  SELECT 'tableB' AS `table`,
         IF(COUNT(`column_b`), NULL, 'column_b') AS `column`
  FROM   tableB
UNION ALL
  -- etc.
) t WHERE `column` IS NOT NULL

这可以使用以下方法完成:

SET group_concat_max_len = 4294967295; -- to overcome default 1KB limitation

SELECT CONCAT(
         'SELECT * FROM ('
       ,  GROUP_CONCAT(
            'SELECT ', QUOTE(TABLE_NAME), ' AS `table`,'
          , 'IF('
          ,   'COUNT(`', REPLACE(COLUMN_NAME, '`', '``'), '`),'
          ,   'NULL,'
          ,    QUOTE(COLUMN_NAME)
          , ') AS `column` '
          , 'FROM `', REPLACE(TABLE_NAME, '`', '``'), '`'
          SEPARATOR ' UNION ALL '
         )
       , ') t WHERE `column` IS NOT NULL'
       )
INTO   @sql
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = DATABASE();

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

查看 sqlfiddle .