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
Hyperf official website Hyperf official documenta...
Table of contents Preface 🍹Preparation 🍲vue3 usag...
How to use CSS to control the arc movement of ele...
Table of contents Preface know Practice makes per...
Click here to return to the 123WORDPRESS.COM HTML ...
Prerequisite: Mac, zsh installed, mysql downloade...
People who use virtual machines usually set up sh...
Table of contents 1. Registering custom instructi...
Preface Every time I use the terminal to create a...
Table of contents Preface 1. Overview 2. Read-wri...
Virtualization 1. Environment Centos7.3 Disable s...
premise In complex scenarios, a lot of data needs...
Table of contents 1. Create a watermark Js file 2...
Table of contents 1. Install vue-video-player 2. ...
Adding the extra_hosts keyword in docker-compose....