MySQL游标循环的施用

发布时间:2019-11-03  栏目:MySQL  评论:0 Comments

FETCH
INTO的变量名绝对无法是你定义CUEvoqueSOWrangler时SQL语句查出来的列名可能列小名,也就说您定义的变量名既不可能是表中已经存在的列名,也不可能是你定义游标时用过的别名(如本例中的count卡塔尔,只要多个准则不切合,FETCH
INTO就把全路的变量赋NULL值,那一点你能够尝尝在FETCH
INTO后加一句Select打字与印刷变量名验证。

DECLARE cur CURSOR FOR SELECT suffix from tb_name;

不过在编写制定进度中却遇上贰个很恶心的BUG,小编最早的原委是那般写的:

— 遍历数据停止标记

不等的生龙活虎部分在于变量定义的称谓,即:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

图片 1

— 提取游标里的多寡,这里独有三个,八个的话也长期以来;

那么存款和储蓄进程如下:

— 游标

2.在相符重新条件的笔录中,把主键大于最小主键的记录整个删掉就能够。

BEGIN

1.基于重复决断标准寻找重复记录的小小主键(日常是ID列卡塔尔。

— 关闭游标

逻辑很清楚,就是依靠重复判定规范依次删掉重复组中主键大于最小主键的笔录们。

IF done THEN

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

CREATE PROCEDURE test_procedure ()

Jeremi(估算大概是个程序员卡塔 尔(阿拉伯语:قطر‎回答,那是叁个显然的误解,当您表明了变量a(起首值为NULL卡塔 尔(阿拉伯语:قطر‎,然后FETCH
INTO a就也就是set a=a,在别的程序语言中那都以无解的。

— 将终止标记绑定到游标

早上同事要自小编写个MySQL去除重复数据的SQL,想起来上次写过生机勃勃篇MySQL去除重复数据的博客,使用导入导出加独一索引完结的,可是这种方式对作业影响一点都不小,所以再一次写七个仓库储存进度来删重复数据,那意气风发写就写了叁个早晨,这种BUG确实是很令人悲伤和浪费时间的。

FETCH cur INTO a;

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 ;

END LOOP;

于是在编写存款和储蓄进度中为定义的变量加个前缀标记是很好的习贯,想起在此之前Oracle写存款和储蓄进度真的都加v_前缀,SQL
Server 都用@前缀,以往轮到mysql却忽视了,确实须求记住下。

DECLARE done INT DEFAULT FALSE;

贴一下页面下最终两条钻探(截至2018.08.01卡塔 尔(阿拉伯语:قطر‎:

END IF;

在查询到那些BUG此前去官方网站页面特意看了一下是不是是作者的语法有不当:https://dev.mysql.com/doc/refman/5.5/en/cursors.html ,确信语法没难题,但尾数第二条商议呈现也许是列名的隐讳BUG,最后一条争论理论了BUG说法,但尚无艺术本人要么基于BUG
REPORT做了以上修正,然后成效就平常了。

— 评释停止的时候

假诺我犹如下表,供给删除start_time和end_time都同样的重复记录。

DECLARE a CHAR(100);

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 ;

— 展开游标

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.

OPEN cur;

这里把流程省略的汇报一下,删重复数据的逻辑超轻巧:

END

这正是说再回头看一下官方网址文书档案下的末尾一条争论,以前自个儿认为最后一条反对BUG的议论纷纭完全部是谈心,是哪位傻X说那不是个BUG的?后来细心想了想,他俩都对,那实在也算个BUG,傻X的也是自己。

LEAVE read_loop;

Brent蒙受的光景与作者相似,并列出了BUG
Report的链接。

— 这里做你想做的轮回的事件

— 要求定义选用游标数据的变量

DELETE from mail_email where email like a;

CLOSE cur;

— 起先循环

read_loop: LOOP

留下评论

网站地图xml地图