只是想知道，是否有任何快速的方法来计算 MySQL 表中的所有 NULL 值(来自所有列)？

谢谢你的想法!

最佳答案

如果您希望仅由 MYSQL 完成此操作而不枚举所有列，请查看此解决方案。

在这种方法中，您不必通过硬编码来维护数据库列的数量。如果您的表架构将被修改，此方法将起作用，并且不需要更改代码。

SET @db = 'testing'; -- database
SET @tb = 'fuzzysearch'; -- table
SET @x = ''; -- will hold the column names with ASCII method applied to retrieve the number of the first char
SET @numcolumns = 0; -- will hold the number of columns in the table

-- figure out how many columns we have
SELECT count(*) into @numcolumns FROM information_schema.columns where table_name=@tb and table_schema=@db;

-- we have to prepare some query from all columns of the table
SELECT group_concat(CONCAT('ASCII(',column_name,')') SEPARATOR ",") into @x from information_schema.columns where table_name=@tb and table_schema=@db;
-- after this query we have a variable separated with comma like
-- ASCII(col1),ASCII(col2),ASCII(col3)

-- we now generate a query to concat the columns using comma as separator (null values are omitted from concat)
-- then figgure out how many times the comma is in that substring (this is done by using length(value)-length(replace(value,',',''))
-- the number returned is how many non null columns we have in that column
-- then we deduct the number from the known number of columns, calculated previously
-- the +1 is added because there is no comma for single value
SET @s = CONCAT('SELECT @numcolumns - (length(CONCAT_WS(\',\',', @x, '))-length(replace(CONCAT_WS(\',\',', @x, '),\',\',\'\')) + 1) FROM ',@db,'.',@tb,';');
PREPARE stmt FROM @s;
EXECUTE stmt;
-- after this execution we have returned for each row the number of null columns
-- I will leave to you to add a sum() group call if you want to find the null values for the whole table
DEALLOCATE PREPARE stmt;

ASCII 用于避免阅读，无意义地连接很长的列，而且 ASCII 使我们对于第一个字符是逗号 (,) 的值是安全的。

由于您正在处理报告，您可能会发现这很有帮助，因为如果您放入一个方法，它可以重复用于每个表。

我试图让尽可能多的评论。

让我们按照上面的紧凑方式(反向方式)拆分:

我想结束这样的查询

SELECT totalcolumns - notnullcolumns from table; -- to return null columns for each row

虽然第一个很容易通过运行计算:

SELECT count(*) FROM information_schema.columns where table_name=@tb and table_schema=@db;

第二个 notnullcolumns 有点麻烦。 在检查了 MySQL 中可用的函数之后，我们发现 CONCAT_WS 不包含 CONCAT 空值

所以运行这样的查询:

SELECT CONCAT_WS(",","First name",NULL,"Last Name");
returns: 'First name,Last Name'

这很好，我们从枚举中去掉了空值。 但是我们如何得到实际连接了多少列呢？

嗯，这很棘手。我们必须计算逗号+1 的数量才能得到实际连接的列。

对于这个技巧，我们使用了以下 SQL 符号

select length(value)-length(replace(value,',','')) +1 from table

好的，现在我们有了连接列的数量。

但接下来是更难的部分。

我们必须为 CONCAT_WS() 枚举所有值。
我们需要这样的东西:

SELECT CONCAT_WS(",",col1,col2,col3,col4,col5);

这是我们必须使用准备好的语句的地方，因为我们必须从未知列动态准备 SQL 查询。我们不知道我们的表中会有多少列。

为此，我们使用来自 information_schema 列表的数据。我们需要传递表名，还有数据库名，因为我们可能在不同的数据库中有相同的表名。

我们需要一个在 CONCAT_WS“字符串”上向我们返回 col1、col2、col3、col4、col5 的查询

为此我们运行一个查询

SELECT group_concat(column_name SEPARATOR ",") into @x from information_schema.columns where table_name=@tb and table_schema=@db;

还有一点要提。当我们使用 length() 和 replace() 方法来找出连接了多少列时，我们必须确保值之间没有逗号。但也要注意，我们的数据库单元格中可以有很长的值。对于这两个技巧，我们都使用方法 ASCII('value')，它将返回第一个字符的 ASCII 字符，它不能是逗号，并且对于 null 列将返回 null。

也就是说，我们可以在上述综合解决方案中压缩所有这些。

关于mysql - 如何统计表中的所有 NULL 值？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2295318/