核心代码

/*--------------------------------求2个或以上字段为NULL的记录t1:id,id1,id2,id3,id4,id5,id6在t1表中有个字段；其中id是主键；怎样打印其中个字段或以上为NULL的记录id？另外，存储过程中怎么实现按顺序一条一条读取记录最方便？注：主键id是没有顺序的，也可能是字符串的；-----------------------------------------*/droptableifexistst1;createtablet1(idint,id1int,id2int,id3int,id4int,id5int,id6int);insertt1select1,1,1,1,1,null,nullunionallselect2,null,null,null,1,2,3unionallselect3,1,2,3,4,5,6unionallselect4,1,2,3,4,5,nullunionallselect5,null,3,4,null,null,null;delimiter$$createprocedureusp_c_null()begindeclaren_cint;declareiddint;declarecurcursorforselectid,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;repeatfetchcurintoidd,n_c;if(n_c>=2)thenselect*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)*/