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

Overview of the basic components of HTML web pages

<br />The information on web pages is mainly...

How to implement digital paging effect code and steps in CSS

A considerable number of websites use digital pagi...

Example of creating a virtual host based on Apache port

apache: create virtual host based on port Take cr...

Dynamic SQL statement analysis in Mybatis

This article mainly introduces the dynamic SQL st...

Tutorial on installing Tomcat server under Windows

1 Download and prepare First, we need to download...

HTML+CSS to achieve simple navigation bar function

Without further ado, I'll go straight to the ...

Solution to MySQL restarting automatically

Preface Recently, a problem occurred in the test ...

Example code of vue custom component to implement v-model two-way binding data

In the project, you will encounter custom public ...

CentOS 8.0.1905 installs ZABBIX 4.4 version (verified)

Zabbix Server Environment Platform Version: ZABBI...

Solve the problem of IDEA configuring tomcat startup error

The following two errors were encountered when co...

Solution to the problem of var in for loop

Preface var is a way to declare variables in ES5....

Detailed explanation of Vue px to rem configuration

Table of contents Method 1 1. Configuration and i...