Mysql calculates the record whose two or more fields are NULL, and mysqlnull

Mysql calculates the record whose two or more fields are NULL, and mysqlnull

Core code

/* ------------------------------ Calculate the records t1: id, id1, id2, id3, id4, id5, and id6 with two or more NULL fields. The field id is the primary key; how to print the record id with one or more NULL fields? In addition, how does one read records one by one in sequence in the stored procedure be the most convenient? Note: The primary key IDs are unordered or may be strings; ------------------------------------------- */drop table if exists t1; create table t1 (id int, id1 int, id2 int, id3 int, id4 int, id5 int, id6 int); insert t1 select1, null, null union all select 2, null, 3 union all select 3, 1, 2, 3, 4, 5, 6 union all select 4, 1, 2, 3, 4, 5, null union all select 5, null, 3, 4, null, null; delimiter $ create procedure usp_c_null () begin declare n_c int; declare idd int; declare cur cursor for select id, case char_length (concat (ifnull (id1, '@'), ifnull (id2 ,'@'), ifnull (id3, '@'), ifnull (id4, '@'), ifnull (id5, '@'), ifnull (id6 ,'@'))) -char_length (replace (concat (ifnull (id1, '@'), ifnull (id2, '@'), ifnull (id3, '@'), ifnull (id4, '@'), ifnull (id5, '@'), ifnull (id6 ,'@')),'@','')) when 6 then 6 when 5 then 5 when 4 then 4 when 3 then 3 when 2 then 2 when 1 then 1 else 0 end as c from t1; declare exit HANDLER for not found close cur; open cur; repeat fetch cur into idd, n_c; if (n_c> = 2) thenselect * from t1 where id = idd; end if; until 0 end repeat; close cur; end; $ delimiter; /* + ------ + | id | id1 | id2 | id3 | id4 | id5 | id6 | + ------ + ------ + | 1 | 1 | 1 | 1 | NULL | + ------ + 1 row in set (0.10 sec) + ------ + | id | id1 | id2 | id3 | id4 | id5 | id6 | + ------ + ------ + | 2 | NULL | 1 | 2 | 3 | + ------ + 1 row in set (0.14 sec) + ------ + | id | id1 | id2 | id3 | id4 | id5 | id6 | + ------ + ------ + | 5 | NULL | 3 | 4 | NULL | + ------ + 1 row in set (0.17 sec) */