mysql solves the problem of finding records where two or more fields are NULL

mysql solves the problem of finding records where two or more fields are NULL

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:
  • Why should MySQL fields use NOT NULL?
  • Should nullable fields in MySQL be set to NULL or NOT NULL?
  • MySQL query empty fields or non-empty fields (is null and not null)
  • Analysis of the reasons why MySQL field definitions should not use null

<<:  Detailed explanation of for loop and double for loop in JavaScript

>>:  Detailed explanation of common Docker Compose commands

Recommend

Simple use of Vue vee-validate plug-in

Table of contents 1. Installation 2. Import 3. De...

JavaScript Reflection Learning Tips

Table of contents 1. Introduction 2. Interface 3....

MySQL master-slave replication principle and points to note

Written in front I have been writing a special to...

Code for aligning form checkbox and radio text

Alignment issues like type="radio" and t...

Steps for encapsulating element-ui pop-up components

Encapsulate el-dialog as a component When we use ...

CSS web page responsive layout to automatically adapt to PC/Pad/Phone devices

Preface There are many devices nowadays, includin...

A detailed introduction to JavaScript execution mechanism

Table of contents 1. The concept of process and t...

Details on using JS array methods some, every and find

Table of contents 1. some 2. every 3. find 1. som...

How to stop CSS animation midway and maintain the posture

Preface I once encountered a difficult problem. I...

Linux system disk formatting and manually adding swap partition

Windows: Support NTFS, FAT Linux supports file fo...

Example of writing mobile H5 to invoke APP (IOS, Android)

iOS 1. URL scheme This solution is basically for ...

JavaScript to achieve fancy carousel effect

This article shares two methods of implementing t...

Tutorial on downloading, installing, configuring and using MySQL under Windows

Overview of MySQL MySQL is a relational database ...