如何计算表中的所有NULL值？ [英] How to count all NULL values in a table?

问题描述

只是想知道，有没有任何快速的方法来计算所有 NULL 值（从所有列）在MySQL表？

感谢任何想法！

解决方案

如果你想这是由MYSQL独家，没有枚举所有的列请查看此解决方案。

在此方法中，您不必通过硬编码来维护数据库列的数量。

  SET @如果您的表格架构将被修改，此方法将会正常运作， db ='testing'; -  database 
 SET @tb ='fuzzysearch'; -  table 
 SET @x =''; - 将使用ASCII方法保存列名称以检索第一个char 
 SET @numcolumns = 0的数字; - 将保存表中的列数
 
  - 计算出我们有多少列
 SELECT count（*）into @numcolumns FROM information_schema.columns where table_name = @ tb and table_schema = @ db; 
 
  - 我们必须从表的所有列准备一些查询
 SELECT group_concat（CONCAT（'ASCII（'，column_name，'）'）SEPARATOR，）into @x from information_schema.columns where table_name = @ tb and table_schema = @ db; 
  - 在这个查询之后，我们有一个用逗号分隔的变量
  -  ASCII（col1），ASCII（col2），ASCII（col3）
 
  - 使用逗号作为分隔符（空值从concat中省略）的查询使用逗号作为分隔符
  - 然后figgure out逗号在该子字符串中的次数（这是通过使用length（value）-length （value，'，'，''））
  - 返回的数字是我们在该列中有多少非空列
  - 然后我们从已知列数中扣除数，计算之前
  - 添加了+1，因为没有单个值的逗号
 SET @s = CONCAT（'SELECT @numcolumns  - （length（CONCAT_WS（\'，\'，' @x，'）） -  length（replace（CONCAT_WS（\'，\'，'，@x，'），\'，\'，\' ，@ db，'。'，@ tb，';'）; 
 PREPARE stmt FROM @s; 
 EXECUTE stmt; 
  - 在此执行后，的空列
  - 如果要查找整个表的空值，我将留给你添加一个sum（）组调用
 DEALLOCATE PREPARE stmt; 
  

ASCII用于避免读取，连接非常长的列，

由于您正在使用报表，因此您可能会发现这很有帮助，因为这可以重复用于每个表，如果你放置在方法中。

我试图尽可能多地发表评论。

紧凑方式（反向）：

我想结束这样的查询

  SELECT totalcolumns  -  notnullcolumns from table; - 为每一行返回空列
  

第一个很容易通过运行： / p>

  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）; 
返回：'名字，姓氏'
  

来自枚举的空值。
但是我们如何得到有多少列实际连接？

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

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

 从表
选择长度（值）-length（replace（value，'，'，''））+1  

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

但更难的部分是下一个。

我们必须枚举CONCAT_WS br>
我们需要这样的：

  SELECT CONCAT_WS（，，col1，col2，col3 ，col4，col5）; 
  

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

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

我们需要一个返回col1，col2 ，col3，col4，col5给我们的CONCAT_WSstring

因此，我们运行一个查询

  SELECT group_concat（column_name SEPARATOR，）into information_schema.columns其中table_name = @ tb和table_schema = @ db; 
  

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

这就是说，我们可以在上述综合解决方案中压缩这一切。

Just wondering, is there any quick way to count all the NULL values (from all columns) in a MySQL table?

Thanks for any idea!

解决方案

If you want this done exclusively by MYSQL and without enumerating all of the columns take a look at this solution.

In this method you don't have to maintain the number of database columns by hard coding them. If your table schema will get modified this method will work, and won't require code change.

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;

The ASCII is used to avoid reading, concatenating very long columns for nothing, also ASCII makes us safe for values where the first char is a comma(,).

Since you are working with reports, you may find this helpful as this can be reused for each table if you put in a method.

I tried to let as many comments as possible.

Let's split on pieces the above compact way (reverse way):

I wanted to end up having a query like this

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

While the first one is easy to calcule by running:

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

The second one the notnullcolumns is a bit of pain. After a piece of examination of the functions available in MySQL, we detect that CONCAT_WS does not CONCAT null values

So running a query like this:

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

This is good, we take rid of the null values from the enumeration. But how do we get how many columns were actually concatenated?

Well that is tricky. We have to calculate the number of commas+1 to get the actually concatenated columns.

For this trick we used the following SQL notation

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

Ok, so we have now the number of concatenated columns.

But the harder part is coming next.

We have to enumerate for CONCAT_WS() all values.
We need to have something like this:

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

This is where we have to take use of the prepared statements, as we have to prepare an SQL query dynamically from yet unknown columns. We don't know how many columns will be in our table.

So for this we use data from information_schema columns table. We need to pass the table name, but also the database name, as we might have the same table name in separate databases.

We need a query that returns col1,col2,col3,col4,col5 to us on the CONCAT_WS "string"

So for this we run a query

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

One more thing to mention. When we used the length() and replace() method to find out how many columns were concatenated, we have to make sure we do not have commas among the values. But also take note that we can have really long values in our database cells. For both of this trick we use method ASCII('value'), which will return the ASCII char of the first char, which cannot be comma and will return null for null columns.

That being said we can compact all this in the above comprehensive solution.

这篇关于如何计算表中的所有NULL值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！