mysql - 如何获得MySQL服务器上每个数据库的空值计数？

我想获取MySQL服务器上每个数据库的所有列和表的空计数。结果表应如下所示：

+---------------+------------+
| database_name | null_count |
+---------------+------------+
| database1     |          0 |
| database2     |       5643 |
| database3     |         72 |
+---------------+------------+

但是，我无法超出单个表的空计数：

SELECT concat('select', group_concat(' count(*) - count(', column_name, ') '  SEPARATOR ' + '), 'from ', MAX(table_schema), '.', MAX(TABLE_NAME))
INTO @SQL
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'Accidents'
AND TABLE_NAME = 'nesreca';

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

你有什么主意吗

PS：我能够使用MATLAB代码获得结果。但是，MySQL内的解决方案将是更可取的。

最佳答案

我认为您使用动态SQL的方向正确。因为您需要整个数据库而不是单个表，所以查询的结构将类似于：

select table_schema, sum(cnt)
from (select "table_schema", count(*) as cnt from "table_name" where "column_name" is null union all
      . . .
     ) t
group by table_schema;

您可以将其构造为：

set group_concat_max_len=1500000;
set @subSQL = '(select "@table_schema" as table_schema, count(*) as cnt from `@table_schema`.`@table_name` where `@column_name` is null)';

select @subSQL := group_concat(replace(replace(replace(@subSQL, '@table_schema', table_schema
                                                      ), '@table_name', table_name
                                              ), '@column_name', column_name
                                      ) separator ' union all '
                              )
from information_schema.columns;

set @SQL = concat('select table_schema, sum(cnt) as NumNulls from (',
                  @subSQL,
                  ') t group by table_schema');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

我应该注意，对于此查询，group_concat()中间值的默认长度可能太小。您将不得不调整它。系统变量为group_concat_max_len（请参见here）。