图片 1

MySQL游标循环取出空值的BUG

Posted by

早上同事要我写个MySQL去除重复数据的SQL,想起来上次写过一篇MySQL去除重复数据的博客,使用导入导出加唯一索引实现的,但是那种方式对业务影响较大,所以重新写一个存储过程来删重复数据,这一写就写了一个上午,这种BUG确实是很令人沮丧和浪费时间的。

MySQL心得7-1-存储过程

这里把流程简单的描述一下,删重复数据的逻辑很简单:

 

1.根据重复判断条件找出重复记录的最小主键(一般是ID列)。

  1. 使用存储过程的优点有:

2.在符合重复条件的记录中,把主键大于最小主键的记录全部删掉即可。

 

假设我有如下表,需要删除start_time和end_time都一样的重复记录。

(1)存储过程在服务器端运行,执行速度快。

图片 1

 

那么存储过程如下:

(2)存储过程执行一次后,其执行规划就驻留在高速缓冲存储器,在以后的操作中,只需从高速缓冲存储器中调用已编译好的二进制代码执行,提高了系统性能。

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE v_start_time,v_end_time DATETIME;
DECLARE v_count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO v_start_time,v_end_time,min_id,v_count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=v_start_time AND end_time=v_end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

 

逻辑很清晰,就是根据重复判断条件依次删掉重复组中主键大于最小主键的记录们。

(3)确保数据库的安全。使用存储过程可以完成所有数据库操作,并可通过编程方式控制上述操作对数据库信息访问的权限。
 www.2cto.com  

但是在编写过程中却遇到一个很恶心的BUG,我最初的内容是这么写的:

 

DELIMITER //
DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
CREATE PROCEDURE Del_Dup_FOR_TEST()
BEGIN
DECLARE min_id INT;
DECLARE start_time,end_time DATETIME;
DECLARE count INT;
DECLARE done INT DEFAULT 0;
DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN my_cur;
  myloop: LOOP
  FETCH my_cur INTO start_time,end_time,min_id,count;
  IF done=1 THEN
  LEAVE myloop;
  END IF;
  DELETE FROM leo.test WHERE start_time=start_time AND end_time=end_time AND id>min_id;
  COMMIT;
  END LOOP myloop;
CLOSE my_cur;
END;
//
DELIMITER ;

2.创建存储过程可以使用create procedure语句。

不同的部分在于变量定义的名称,即:

 

FETCH
INTO的变量名绝对不能是你定义CURSOR时SQL语句查出来的列名或者列别名,也就说你定义的变量名既不能是表中已经存在的列名,也不能是你定义游标时用过的别名(如本例中的count),只要一个条件不符合,FETCH
INTO就把全部的变量赋NULL值,这点你可以尝试在FETCH
INTO后加一句Select打印变量名验证。

要在MySQL 5.1中创建存储过程,必须具有CREATE
routine权限。要想查看数据库中有哪些存储过程,可以使用SHOW PROCEDURE
STATUS命令。要查看某个存储过程的具体信息,可使用SHOWCREATE PROCEDURE
sp_name命令,其中sp_name是存储过程的名称。

在查询到这个BUG之前去官网页面特地看了一下是否是我的语法有错误: ,确信语法没问题,但倒数第二条评论显示可能是列名的隐藏BUG,最后一条评论反驳了BUG说法,但没有办法我还是根据BUG
REPORT做了以上修改,然后功能就正常了。

 

关于此BUG的BUG报告页面详见MySQL BUG:#28227 和
BUG:#5967

CREATE PROCEDURE的语法格式:

那么再回头看一下官网文档下的最后一条评论,开始我认为最后一条反驳BUG的评论完全是扯淡,是哪个傻X说这不是个BUG的?后来仔细想了想,他俩都对,这确实也算个BUG,傻X的也是我。

 

贴一下页面下最后两条评论(截止2018.08.01):

CREATE PROCEDURE sp_name ([proc_parameter[,…]])

Posted by Brent Roady on May 9, 2012
It should be noted that the local variable names used in FETCH [cursor] INTO must be different than the variable names used in the SELECT statement 
defining the CURSOR. Otherwise the values will be NULL. 
In this example, 
DECLARE a VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT a FROM table1;
FETCH cur1 INTO a;
the value of a after the FETCH will be NULL.
This is also described here: http://bugs.mysql.com/bug.php?id=28227

Posted by Jérémi Lassausaie on February 3, 2015
Answer for Brent Roady :
I don't see any bug in the bahaviour described.
DECLARE a VARCHAR(255);
/* you declare a variable "a" without a specified default value, a=NULL */
DECLARE cur1 CURSOR FOR 
SELECT a FROM table1;
/* You declare a cursor that selects "a" FROM a table */
OPEN cur1;
/* You execute your cursor query, a warning is raised because a is ambiguously defined but you don't see it */
FETCH cur1 INTO a;
/* you put your unique field in your unique row into a (basically you do "SET a=a;") so a is still NULL */
There is no bug report, just a misunderstanding.

 

Brent遇到的现象与我相同,并列出了BUG
Report的链接。

   [characteristic …] routine_body

Jeremi(猜测可能是个程序员)回答,这是一个显而易见的误解,当你声明了变量a(初始值为NULL),然后FETCH
INTO a就相当于set a=a,在任何程序语言中这都是无解的。

 

因此在编写存储过程中为定义的变量加个前缀标识是很好的习惯,想起以前Oracle写存储过程确实都加v_前缀,SQL
Server 都用@前缀,现在轮到mysql却忽略了,确实需要牢记下。

其中,proc_parameter的参数如下:

 

[ IN | OUT | INOUT ] param_name type

 

characteristic特征如下:

 

  language SQL

 

 | [NOT] DETERMINISTIC

 

 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

 

 | SQL SECURITY { DEFINER | INVOKER }

 

 | COMMENT ‘string’

 

说明:

 

●  
sp_name:存储过程的名称,默认在当前数据库中创建。需要在特定数据库中创建存储过程时,则要在名称前面加上数据库的名称,格式为:db_name.sp_name。值得注意的是,这个名称应当尽量避免取与MySQL的内置函数相同的名称,否则会发生错误。

 

 ●  
proc_parameter:存储过程的参数,param_name为参数名,type为参数的类型,当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。MySQL存储过程支持三种类型的参数:输入参数、输出参数和输入/输出参数,关键字分别是IN、OUT和INOUT。输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候,存储过程使用输出参数。输入/输出参数既可以充当输入参数也可以充当输出参数。存储过程也可以不加参数,但是名称后面的括号是不可省略的。

 

注意:参数的名字不要等于列的名字,否则虽然不会返回出错消息,但是存储过程中的SQL语句会将参数名看做列名,从而引发不可预知的结果。

 

characteristic:存储过程的某些特征设定,下面一一介绍:

 

language
sql:表明编写这个存储过程的语言为SQL语言,目前来讲,MySQL存储过程还不能用外部编程语言来编写,也就是说,这个选项可以不指定。将来将会对其扩展,最有可能第一个被支持的语言是PHP。
 www.2cto.com  

deterministic:设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果,设置为NOT
DETERMINISTIC则表示会产生不确定的结果。默认为NOTDETERMINISTIC。

 

contains SQL:表示存储过程不包含读或写数据的语句。NO
SQL表示存储过程不包含SQL语句。reads SQL
DATA表示存储过程包含读数据的语句,但不包含写数据的语句。modifies SQL
DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定,默认的是CONTAINS
SQL。

 

SQL SECURITY:SQL
SECURITY特征可以用来指定存储过程使用创建该存储过程的用户(DEFINER)的许可来执行,还是使用调用者(INVOKER)的许可来执行。默认值是DEFINER。

 

COMMENT
‘string’:对存储过程的描述,string为描述内容。这个信息可以用SHOWCREATE
PROCEDURE语句来显示。

 

●  
routine_body:这是存储过程的主体部分,也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句,这个部分总是以begin开始,以end结束。当然,当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

 

  1.  
    在开始创建存储过程之前,先介绍一个很实用的命令,即delimiter命令。在MySQL中,服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候,存储过程体中可能包含多个SQL语句,每个SQL语句都是以分号为结尾的,这时服务器处理程序的时候遇到第一个分号就会认为程序结束,这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。

 

DELIMITER语法格式为:DELIMITER $$

 

说明:$$是用户定义的结束符,通常这个符号可以是一些特殊的符号,如两个“#”,一个“¥”、数字、字母等都可以。当使用DELIMITER命令时,应该避免使用反斜杠(“”)字符,因为那是MySQL的转义字符。

 

例:创建存储过程,实现的功能是删除一个特定学生的信息。

 

DELIMITER $$

 

CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

 

BEGIN

 

DELETE FROM XS WHERE 学号=XH;

 

END $$

 

DELIMITER ;

 

说明:当调用这个存储过程时,MySQL根据提供的参数XH的值,删除对应在XS表中的数据。

 

在关键字BEGIN和END之间指定了存储过程体,当然,BEGIN-END复合语句还可以嵌套使用。

 

  1.  局部变量

 

在存储过程中可以声明局部变量,它们可以用来存储临时结果。要声明局部变量必须使用declare语句。在声明局部变量的同时也可以对其赋一个初始值。

 

DECLARE语法格式:DECLARE var_name[,…] type [DEFAULT value]

 

说明:var_name为变量名;type为变量类型;default子句给变量指定一个默认值,如果不指定默认为NULL的话。可以同时声明多个类型相同的局部变量,中间用逗号隔开。

 

例: 声明一个整型变量和两个字符变量。

 

DECLARE num INT(4);

 

DECLARE str1, str2 VARCHAR(6);

 

declare n char(10) default ‘abcdefg’;

 

说明:局部变量只能在BEGIN…END语句块中声明。

 

局部变量必须在存储过程的开头就声明,声明完后,可以在声明它的BEGIN…END语句块中使用该变量,其他语句块中不可以使用它。
 www.2cto.com  

 

在存储过程中也可以声明用户变量,不过千万不要将这两个混淆。局部变量和用户变量的区别在于:局部变量前面没有使用@符号,局部变量在其所在的BEGIN…END语句块处理完后就消失了,而用户变量存在于整个会话当中。

 

  1.  使用SET语句赋值

 

要给局部变量赋值可以使用SET语句,SET语句也是SQL本身的一部分。语法格式为:SET
 var_name = expr [,var_name = expr] …

 

例: 在存储过程中给局部变量赋值。

 

SET num=1, str1= ‘hello’;

 

说明:与声明用户变量时不同,这里的变量名前面没有@符号。注意,例中的这条语句无法单独执行,只能在存储过程和存储函数中使用。

 

  1. SELECT…INTO语句(重点)

 

使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此,返回的结果只能有一行。语法格式为:

 

SELECT col_name[,…] INTO var_name[,…]  table_expr

 

说明:col_name是列名,var_name是要赋值的变量名。table_expr是SELECT语句中的FROM子句及后面的部分,这里不再叙述。

 

例:
在存储过程体中将XS表中的学号为081101的学生姓名和专业名的值分别赋给变量name和project。

 

SELECT 姓名,专业名 INTO name, project

 

   FROMXS;  WHERE 学号= ‘081101’;

 

  1.  流程控制语句

 

在MySQL中,常见的过程式SQL语句可以用在一个存储过程体中。例如:IF语句、CASE语句、LOOP语句、WHILE语句、iterate语句和LEAVE语句。

 

(1)IF语句

 

IF-THEN-ELSE语句可根据不同的条件执行不同的操作。

 

语法格式为:

 

IF 判断的条件THEN 一个或多个SQL语句

 

[ELSEIF判断的条件THEN一个或多个SQL语句] …

 

[ELSE一个或多个SQL语句]

 

END IF

 

说明:当判断条件为真时,就执行相应的SQL语句。

 

IF语句不同于系统的内置函数IF()函数,IF()函数只能判断两种情况,所以请不要混淆。

 

例: 创建XSCJ数据库的存储过程,判断两个输入的参数哪一个更大。

 

DELIMITER $$  www.2cto.com  

 

CREATE PROCEDURE XSCJ.COMPAR

 

(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR(6) )

 

BEGIN

 

IFK1>K2 THEN

 

    SET K3= ‘大于’;

 

ELSEIFK1=K2 THEN

 

    SET K3= ‘等于’;

 

ELSE

 

    SET K3= ‘小于’;

 

ENDIF;

 

END$$

 

DELIMITER ;

 

说明:存储过程中K1和K2是输入参数,K3是输出参数。

 

(2)CASE语句

 

前面已经介绍过了,这里介绍CASE语句在存储过程中的用法,与之前略有不同。语法格式为:

 

CASE case_value

 

   WHEN when_value THEN statement_list

 

   [WHEN when_value THEN statement_list] …

 

   [ELSE statement_list]

 

END CASE

 

或者:

 

CASE

 

   WHEN search_condition THEN statement_list

 

   [WHEN search_condition THEN statement_list] …

 

   [ELSE statement_list]  www.2cto.com  

 

END CASE

 

说明:一个CASE语句经常可以充当一个IF-THEN-ELSE语句。

 

第一种格式中case_value是要被判断的值或表达式,接下来是一系列的WHEN-THEN块,每一块的when_value参数指定要与case_value比较的值,如果为真,就执行statement_list中的SQL语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。CASE语句最后以END
CASE结束。

 

第二种格式中CASE关键字后面没有参数,在WHEN-THEN块中,search_condition指定了一个比较表达式,表达式为真时执行THEN后面的语句。与第一种格式相比,这种格式能够实现更为复杂的条件判断,使用起来更方便。

 

例: 创建一个存储过程,针对参数的不同,返回不同的结果。

 

DELIMITER $$

 

CREATE PROCEDURE XSCJ.RESULT

 

(IN str VARCHAR(4), OUT sex VARCHAR(4) )

 

BEGIN

 

 CASE str

 

   WHEN’M’ THEN SET sex=’男’;

 

   WHEN’F’ THEN SET sex=’女’;

 

   ELSE  SET sex=’无’;

 

   ENDCASE;

 

END$$

 

DELIMITER ;

 

例: 用第二种格式的CASE语句创建以上存储过程。程序片段如下:

 

CASE

 

   WHENstr=’M’ THEN SET sex=’男’;

 

   WHENstr=’F’ THEN SET sex=’女’;

 

   ELSE  SET sex=’无’;

 

END CASE;

 

(3)循环语句

 

MySQL支持3条用来创建循环的语句:while、repeat和loop语句。在存储过程中可以定义0个、1个或多个循环语句。

 

●   WHILE语句语法格式为:

 

[begin_label:] WHILE search_condition  DO

 

statement_list  www.2cto.com  

 

END WHILE [end_label]

 

说明:语句首先判断search_condition是否为真,不为真则执行statement_list中的语句,然后再次进行判断,为真则继续循环,不为真则结束循环。begin_label和end_label是WHILE语句的标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们的名字必须是相同的。

 

例: 创建一个带WHILE循环的存储过程。

 

DELIMITER $$

 

CREATE PROCEDURE dowhile()

 

BEGIN

 

   DECLARE v1 INT DEFAULT5;

 

   WHILE  v1 > 0 DO

 

         SET v1 = v1-1;

 

   END WHILE;

 

END $$

 

DELIMITER ;

 

●   repeat语句格式如下:

 

[begin_label:] REPEAT

 

     statement_list

 

UNTIL search_condition

 

END REPEAT [end_label]

 

说明:REPEAT语句首先执行statement_list中的语句,然后判断search_condition是否为真,为真则停止循环,不为真则继续循环。REPEAT也可以被标注。

 

例: 用REPEAT语句创建一个如例7.9的存储过程。程序片段如下:

 

REPEAT

 

    v1=v1-1;

 

    UNTIL v1<1;

 

END REPEAT;

 

说明:REPEAT语句和WHILE语句的区别在于:REPEAT语句先执行语句,后进行判断;而WHILE语句是先判断,条件为真时才执行语句。

 

●   LOOP语句语法格式如下:

 

[begin_label:] LOOP

  www.2cto.com  

         statement_list

 

END LOOP [end_label]

 

说明:LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造,statement_list是需要重复执行的语句。在循环内的语句一直重复至循环被退出,退出时通常伴随着一个LEAVE
语句。

 

LEAVE语句经常和BEGIN…END或循环一起使用。结构如下:

 

LEAVE label ;
label是语句中标注的名字,这个名字是自定义的。加上LEAVE关键字就可以用来退出被标注的循环语句。

 

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注