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 implementation method of vue3 source code analysis

Table of contents Preface 🍹Preparation 🍲vue3 usag...

CSS3 sample code to achieve element arc motion

How to use CSS to control the arc movement of ele...

A brief discussion on the implementation principle of Webpack4 plugins

Table of contents Preface know Practice makes per...

Markup Languages ​​- What to learn after learning HTML?

Click here to return to the 123WORDPRESS.COM HTML ...

Detailed process of installing and configuring MySQL and Navicat prenium

Prerequisite: Mac, zsh installed, mysql downloade...

vue3 custom directive details

Table of contents 1. Registering custom instructi...

Detailed explanation of MySQL master-slave replication and read-write separation

Table of contents Preface 1. Overview 2. Read-wri...

VMware virtualization kvm installation and deployment tutorial summary

Virtualization 1. Environment Centos7.3 Disable s...

Detailed explanation of the use of redux in native WeChat applet development

premise In complex scenarios, a lot of data needs...

Vue's global watermark implementation example

Table of contents 1. Create a watermark Js file 2...

How to use vue-video-player to achieve live broadcast

Table of contents 1. Install vue-video-player 2. ...

Docker container custom hosts network access operation

Adding the extra_hosts keyword in docker-compose....