count number of Null variables per row mysql

问题:

We have the following table in Mysql using innoDB:

id   Var1   Var2  Var 3     
1    NULL    1     2   
2    2    NULL    NULL  
3    4       2    NULL 

We pretend to produce Var4 with the number of NULL variables per row:

id Var4  
1    1  
2    2  
3    1 

I tried unsuccessfully :

update db.table 
set var4 = ISNULL(var1) + ISNULL(var2) + ISNULL(var3);

any suggestions?

回答1:

Here is one way:

select id, ((var1 is null) + (var2 is null) + (var3 is null)) as var4
from table t;

MySQL treats booleans as integers, with true being 1 and false being 0. You can just add them up to get the total.

As an update:

update table t
    set var4 = ((var1 is null) + (var2 is null) + (var3 is null));

As a note, MySQL doesn't support ISNULL(). That is more of a SQL Server function. But it is not ANSI standard anyway, so you are usually better off using coalesce().