mysql 求解求2个或以上字段为NULL的记录
核心代码
/*-------------------------------- 求2个或以上字段为NULL的记录 t1: id,id1,id2,id3,id4,id5,id6 在t1表中有个字段； 其中id是主键； 怎样打印其中个字段或以上为NULL的记录id？ 另外，存储过程中怎么实现按顺序一条一条读取记录最方便？ 注：主键id是没有顺序的，也可能是字符串的； -----------------------------------------*/ droptableifexistst1; createtablet1(idint,id1int,id2int,id3int,id4int,id5int,id6int); insertt1select 1,1,1,1,1,null,nullunionallselect 2,null,null,null,1,2,3unionallselect 3,1,2,3,4,5,6unionallselect 4,1,2,3,4,5,nullunionallselect 5,null,3,4,null,null,null; delimiter$$ createprocedureusp_c_null() begin declaren_cint; declareiddint; declarecurcursorfor selectid,casechar_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,'@')),'@','')) when6then6when5then5when4then4when3then3when2then2when1then1else0endascfromt1; declareexitHANDLERfornotfoundclosecur; opencur; repeat fetchcurintoidd,n_c; if(n_c>=2)then select*fromt1whereid=idd; endif; until0endrepeat; closecur; end; $$ delimiter; /* +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |1|1|1|1|1|NULL|NULL| +------+------+------+------+------+------+------+ 1rowinset(0.10sec) +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |2|NULL|NULL|NULL|1|2|3| +------+------+------+------+------+------+------+ 1rowinset(0.14sec) +------+------+------+------+------+------+------+ |id|id1|id2|id3|id4|id5|id6| +------+------+------+------+------+------+------+ |5|NULL|3|4|NULL|NULL|NULL| +------+------+------+------+------+------+------+ 1rowinset(0.17sec) */