数据库服务器为空,如何获得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)。