MySQL游标循环取出空值的BUG

图片 10

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

 

总结  

变量的使用不仅仅只有这些,在光标中条件也是一个很好的功能,刚才测试的是continue如果使用EXIT的话语句执行完“SET
@ID=2;”就不往下执行了,后面的IF也不被执行整个语句不会被回滚,但是使用CONTINE当出现错误后还是会往下执行如果后面的语句还有很多的话整个回滚的过程将会很长,在这里可以利用循环,当出现错误立刻退出循环执行后面的if回滚操作,在下一篇讲循环语句会写到,欢迎关注。

 

 

备注:

    作者:pursuer.chen

    博客:http://www.cnblogs.com/chenmh

本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接。

《欢迎交流讨论》

变量和条件,MySQL变量条件 概述
变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重…

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

 

条件  

条件的作用一般用在对指定条件的处理,比如我们遇到主键重复报错后该怎样处理。 

定义条件

 定义条件就是事先定义某种错误状态或者sql状态的名称,然后就可以引用该条件名称开做条件处理,定义条件一般用的比较少,一般会直接放在条件处理里面。

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

1.没有定义条件:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();

#查询变量值
SELECT @ID,@X;

图片 1

 报主键重复的错误,其中1062是主键重复的错误代码,23000是sql错误状态

图片 2

2.定义处理条件

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR 1062;
#引用前面定义的条件名称并做赋值处理
DECLARE EXIT HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

在执行存储过程的步骤中并没有报错,但是由于我定义的是exit,所以在遇到报错sql就终止往下执行了。

图片 3

接下来看看continue的不同

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN
#定义条件名称,
DECLARE reprimary CONDITION FOR SQLSTATE '23000';
#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR reprimary SET @x=1;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

END$$
DELIMITER ;

CALL Pro_Employee_insert();

SELECT @ID,@X;

其中红色标示的是和上面不同的地方,这里定义条件使用的是SQL状态,也是主键重复的状态;并且这里使用的是CONTINUE就是遇到错误继续往下执行。

图片 4

图片 5

条件处理

条件处理就是之间定义语句的错误的处理,省去了前面定义条件名称的步骤。

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE| EXIT| UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

handler_type:遇到错误是继续往下执行还是终止,目前UNDO还没用到。

CONTINUE:继续往下执行

EXIT:终止执行

condition_values:错误状态

SQLSTATE [VALUE]
sqlstate_value:就是前面讲到的SQL错误状态,例如主键重复状态SQLSTATE
‘23000’

condition_name:上面讲到的定义条件名称;

SQLWARNING:是对所有以01开头的SQLSTATE代码的速记,例如:DECLARE CONTINUE
HANDLER FOR SQLWARNING。

NOT FOUND:是对所有以02开头的SQLSTATE代码的速记。

SQLEXCEPTION:是对所有没有被SQLWARNING或NOT
FOUND捕获的SQLSTATE代码的速记。

mysql_error_code:是错误代码,例如主键重复的错误代码是1062,DECLARE
CONTINUE HANDLER FOR 1062

 

语句:

DROP PROCEDURE IF EXISTS Pro_Employee_insert;
DELIMITER $$
CREATE PROCEDURE Pro_Employee_insert()
MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN

#引用前面定义的条件名称并做赋值处理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x=2;
#开始事务必须在DECLARE之后
START TRANSACTION ;
SET @ID=1;
INSERT INTO Employee(id,name,depid) VALUES(7,'陈',100);
SET @ID=2;
INSERT INTO Employee(id,name,depid) VALUES(6,'陈',100);
SET @ID=3;

IF @x=2 THEN
  ROLLBACK;
ELSE
  COMMIT;
END IF;  

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee_insert();
#查询
SELECT @ID,@X;

图片 6

通过SELECT
@ID,@X可以知道存储过程已经执行到了最后,但是因为存储过程后面有做回滚操作整个语句进行了回滚,所以ID=7的符合条件的记录也被回滚了。

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 ;

 

概述  

 变量在存储过程中会经常被使用,变量的使用方法是一个重要的知识点,特别是在定义条件这块比较重要。

 mysql版本:5.6

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

 

MySQL 变量和条件,MySQL变量条件

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

说明:在调用存储过程后,未遇到错误消息时处理程序未被激活,当执行INSERT语句出现出错消息时,MySQL检查是否为这个错误代码定义了处理程序。如果有,则激活该处理程序,本例中,INSERT语句导致的错误消息刚好是SQLSTATE代码中的一条。接下来执行处理程序的附加语句(SET
@x2=1)。此后,MySQL检查处理程序的类型,这里的类型为CONTINUE,因此存储过程继续处理,将用户变量x赋值为3。如果这里的INSERT语句能够执行,处理程序将不被激活,用户变量x2将不被赋值。

变量定义和赋值  

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

declare定义变量

在存储过程和函数中通过declare定义变量在BEGIN…END中,且在语句之前。并且可以通过重复定义多个变量

注意:declare定义的变量名不能带‘@’符号,mysql在这点做的确实不够直观,往往变量名会被错成参数或者字段名。

DECLARE var_name[,...] type [DEFAULT value]

例如:

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

SET变量赋值 

SET除了可以给已经定义好的变量赋值外,还可以指定赋值并定义新变量,且SET定义的变量名可以带‘@’符号,SET语句的位置也是在BEGIN
….END之间的语句之前。

1.变量赋值

SET var_name = expr [, var_name = expr] ...

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

  SELECT @pcount;

图片 7

 2.通过赋值定义变量

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
SET pname='王';
SET @ID=1;
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid AND name=pname;
SELECT @ID;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

图片 8

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

DELIMITER ;

SELECT … INTO语句赋值

 通过select
into语句可以将值赋予变量,也可以之间将该值赋值存储过程的out参数,上面的存储过程select
into就是之间将值赋予out参数。

DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE pname VARCHAR(20) DEFAULT '陈';
DECLARE Pid INT;
SELECT COUNT(id) INTO Pid FROM Employee WHERE depid=pdepid AND name=pname;
SELECT Pid;

END$$
DELIMITER ;

CALL Pro_Employee(101,@pcount);

这个存储过程就是select into将值赋予变量;

 图片 9

表中并没有depid=101 and name=’陈’的记录。 

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

END REPEAT [end_label]

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.

 

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

 

图片 10

    SET K3= ‘小于’;

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

         statement_list

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 ;

 

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

BEGIN

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

IFK1>K2 THEN

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

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

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

 

那么存储过程如下:

  www.2cto.com  

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

 

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

 

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

END REPEAT;

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

 

[ELSE一个或多个SQL语句]

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

 

DELIMITER ;

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

ELSE

 

   END IF;

例: 修改上例中的存储过程,将SQLSTATE ‘23000’
定义成NON_UNIQUE,并在处理程序中使用这个名称。程序片段为:

 

 

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

 

 

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

 

  www.2cto.com  

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

   ENDWHILE;

 

循环语句中还有一个iterate语句,它只可以出现在LOOP、REPEAT和WHILE语句内,意为“再次循环”。它的格式为:ITERATE
label

 

 

SELECT 姓名,专业名 INTO name, project

| EXIT

   DECLARECONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2=1;

CREATE PROCEDURE dowhile()

 

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

 

END CASE

DELIMITER $$

DELETE FROM XS WHERE 学号=XH;

 

 
 [email protected]=3;
 www.2cto.com  

CLOSE cursor_name语句参数的含义与OPEN语句中相同。

DECLARE num INT(4);

 

          IF @a<0 THEN

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

 

 

 

 

Error 1048, “Column cannot benull”

 

 

   condition_name是处理条件的名称,接下来会讲到。

 

 

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

 

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

DELIMITER ;

 

   ELSE  SET sex=’无’;

DELIMITER $$

 

CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6))

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

CREATE PROCEDURE XSCJ.COMPAR

 

END WHILE [end_label]

 

 

 

说明:sp_name为存储过程的名称,如果要调用某个特定数据库的存储过程,则需要在前面加上该数据库的名称。parameter为调用该存储过程使用的参数,这条语句中的参数个数必须总是等于存储过程的参数个数。
 www.2cto.com  

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

  1.  局部变量

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

说明:characteristic是存储过程创建时的特征,在CREATE
PROCEDURE语句中已经介绍过。只要设定了其中的值,存储过程的特征就随之变化。

例: 创建一个存储过程,计算XS表中行的数目。

 

 

说明:该语句格式与LEAVE差不多,区别在于:LEAVE语句是离开一个循环,而ITERATE语句是重新开始一个循环。

 

●  condition_value:给出SQLSTATE的代码表示。

mysql_error_code是具体的SQLSTATE代码。除了SQLSTATE值,MySQL错误代码也被支持,表示的形式为:ERROR=
‘xxxx’。

 

     SELECT学号 FROM XS;

 

 

(2)CASE语句

 

SET num=1, str1= ‘hello’;

 

 

END IF

     statement_list

   DECLARE  KCH CHAR(3);

 

其中,characteristic为:

 

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

 

 

  www.2cto.com  

   ELSE

 

END$$

 

 

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

说明:cursor_name是游标的名称,游标名称使用与表名同样的规则。select_statement是一个SELECT语句,返回的是一行或多行的数据。这个语句声明一个游标,也可以在存储过程中定义多个游标,但是一个块中的每一个游标必须有唯一的名字。

    SET K3= ‘等于’;

 

 | SQL SECURITY { DEFINER | INVOKER }

   SQLWARNING是对所有以01开头的SQLSTATE代码的速记。NOT
FOUND是对所有以02开头的SQLSTATE代码的速记。SQLEXCEPTION是对所有没有被SQLWARNING或NOT
FOUND捕获的SQLSTATE代码的速记。当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上三种形式。

  1.  流程控制语句

 

 

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

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

 

游标使用完以后,要及时关闭。关闭游标使用CLOSE语句,格式为:

一条SELECT…INTO语句返回的是带有值的一行,这样可以把数据读取到存储过程中。但是常规的SELECT语句返回的是多行数据,如果要处理它需要引入游标这一概念。MySQL支持简单的游标。在MySQL中,游标一定要在存储过程或函数中使用,不能单独在查询中使用。

或者:

DELIMITER $$

 

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

 

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

 

 

 

BEGIN

(3)循环语句

END$$

 

 

 

 

   SET @x=3;  www.2cto.com  

 

 

 

DECLARE XS_CUR1 CURSOR FOR

 

注意:这里的SELECT子句不能有INTO子句。

如果要修改存储过程的内容,可以使用先删除再重新定义存储过程的方法。

 

 

   OPENNUMBER_XS;

 

 

characteristic特征如下:

  www.2cto.com  

 

(1)声明游标

   DECLARE  XF TINYINT;

DELIMITER ;

 

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

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

 

   WHEN when_value THEN statement_list

其中,condition_value:

 

   SET @x=2;

 

 

BEGIN

 

 

 

 

 

DELIMITER $$

 

 

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

 

语法格式:DECLAREcursor_name cursor for select_statement

 

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

 

 

 

| mysql_error_code

 

 

 

CASE

语法格式:FETCH cursor_nameINTO var_name [, var_name] …

 

 

   INSERTINTO XS VALUES(‘081101’, ‘王民’, ‘计算机’, 1, ‘1990-02-10’, 50
, NULL, NULL);

 

 

语法格式为:ALTER PROCEDURE sp_name [characteristic …]

   [characteristic …] routine_body

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图