Core code /*-------------------------------- Find records t1 where two or more fields are NULL: id, id1, id2, id3, id4, id5, id6 In the t1 table there is a field; Where id is the primary key; How to print the record ids where one or more of the fields are NULL? In addition, what is the most convenient way to read records one by one in sequence during the storage process? Note: The primary key id has no order and may also be a string; -----------------------------------------*/ drop table if exists t1; create table t1(id int,id1 int,id2 int,id3 int,id4 int,id5 int,id6 int); insert t1 select 1,1,1,1,1,null,null union all select 2,null,null,null,1,2,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,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) then select * 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 | 1 | NULL | NULL | +------+------+------+------+------+------+------+ 1 row in set (0.10 sec) +------+------+------+------+------+------+------+ | id | id1 | id2 | id3 | id4 | id5 | id6 | +------+------+------+------+------+------+------+ | 2 | NULL | NULL | NULL | 1 | 2 | 3 | +------+------+------+------+------+------+------+ 1 row in set (0.14 sec) +------+------+------+------+------+------+------+ | id | id1 | id2 | id3 | id4 | id5 | id6 | +------+------+------+------+------+------+------+ | 5 | NULL | 3 | 4 | NULL | NULL | NULL | +------+------+------+------+------+------+------+ 1 row in set (0.17 sec) */ You may also be interested in:
|
<<: Detailed explanation of for loop and double for loop in JavaScript
>>: Detailed explanation of common Docker Compose commands
<br />The information on web pages is mainly...
A considerable number of websites use digital pagi...
Let's briefly sort out the configuration of s...
Preface InnoDB stores data in tablespaces. In the...
apache: create virtual host based on port Take cr...
This article mainly introduces the dynamic SQL st...
1 Download and prepare First, we need to download...
Without further ado, I'll go straight to the ...
Preface Recently, a problem occurred in the test ...
This article uses examples to describe the operat...
In the project, you will encounter custom public ...
Zabbix Server Environment Platform Version: ZABBI...
The following two errors were encountered when co...
Preface var is a way to declare variables in ES5....
Table of contents Method 1 1. Configuration and i...