在MySQL表中找到所有只有空值的列 [英] Find all those columns which have only null values, in a MySQL table

问题描述

情况如下:

我有很多表，每个表都有很多列.我需要为新系统处理这个将要弃用的旧数据库，并且我正在寻找一种消除所有-显然-从未使用过的列的方法.

I have a substantial number of tables, with each a substantial number of columns. I need to deal with this old and to-be-deprecated database for a new system, and I'm looking for a way to eliminate all columns that have - apparently - never been in use.

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

I wanna do this by filtering out all columns that have a value on any given row, leaving me with a set of columns where the value is NULL in all rows. Of course I could manually sort every column descending, but that'd take too long as I'm dealing with loads of tables and columns. I estimate it to be 400 tables with up to 50 (!) columns per table.

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

Is there any way I can get this information from the 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'，因为这是唯一没有任何填充值的列.

The output should be 'column_a' and 'column_c', for being the only columns without any filled in values.

推荐答案

您可以通过动态创建(从从该字符串准备语句并执行它.

You can avoid using a procedure by dynamically creating (from the INFORMATION_SCHEMA.COLUMNS table) a string that contains the SQL you wish to execute, then preparing a statement from that string and executing it.

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

The SQL we wish to build will look like:

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

可以使用以下方法完成此操作:

This can be done using the following:

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 上查看.

这篇关于在MySQL表中找到所有只有空值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！