您的位置:澳门新葡8455最新网站 > 数据库管理 > MySQL变量条件,MySQL游标循环抽出空值的BUG

MySQL变量条件,MySQL游标循环抽出空值的BUG

发布时间:2019-11-17 13:57编辑:数据库管理浏览(122)

    上午同事要自己写个MySQL去除重复数据的SQL,想起来上次写过豆蔻梢头篇MySQL去除重复数据的博客,使用导入导出加独一索引完毕的,可是这种办法对专业影响不小,所以重复写二个囤积进度来删重复数据,那风流罗曼蒂克写就写了三个早上,这种BUG确实是很令人心寒和浪费时间的。

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

    这里把流程简便的陈诉一下,删重复数据的逻辑相当粗略:

    概述  

    澳门新葡萄京娱乐场, 变量在存款和储蓄进度中会平日被利用,变量的选取方法是一个尤为重要的知识点,非常是在定义准则那块超重大。

     mysql版本:5.6

    1.基于重复判别规范寻找重复记录的小不点儿主键(日常是ID列卡塔 尔(英语:State of Qatar)。

    变量定义和赋值  

    #创建数据库
    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;

    澳门新葡萄京娱乐场 1

     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);
    

    澳门新葡萄京娱乐场 2

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

    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将值付与变量;

     澳门新葡萄京娱乐场 3

    表中并未depid=101 and name='陈'的记录。 

    生机勃勃旦作者犹如下表,要求删除start_time和end_time都同样的重复记录。

    条件  

    规则的效用平日用在对点名条件的管理,比如大家相见主键重复报错后该怎么管理。 

    概念准则

     定义条件正是事先定义某种错误状态或许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;
    

    澳门新葡萄京娱乐场 4

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

    澳门新葡萄京娱乐场 5

    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就止住往下实施了。

    澳门新葡萄京娱乐场 6

    接下去看看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正是碰见错误继续往下施行。

    澳门新葡萄京娱乐场 7

    澳门新葡萄京娱乐场 8

    规范处理

    原则管理便是中间定义语句的失实的拍卖,省去了前面定义法则名称的手续。

    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:上面讲到的定义法则名称;

    SQLWAKugaNING:是对全体以01开端的SQLSTATE代码的笔记,譬如:DECLARE CONTINUE HANDLEV12 Vantage FO福特Explorer SQLWA帕杰罗NING。

    NOT FOUND:是对具备以02方始的SQLSTATE代码的笔记。

    SQLEXCEPTION:是对全体未有被SQLWA昂科雷NING或NOT FOUND捕获的SQLSTATE代码的笔记。

    mysql_error_code:是错误代码,比方主键重复的错误代码是1062,DECLARE CONTINUE HANDLE本田CR-V FO奥德赛 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;
    

    澳门新葡萄京娱乐场 9

    透过SELECT @ID,@X能够领略存储进度已经试行到了最终,然而因为存款和储蓄进度后边有做回滚操作整个讲话实行了回滚,所以ID=7的相符条件的记录也被回滚了。

    澳门新葡萄京娱乐场 10

    总结  

    变量的行使不止独有那几个,在光标中规格也是一个很好的功力,刚才测量试验的是continue如果使用EXIT的说话句实施完“SET @ID=2;”就不往下实施了,前边的IF也不被实践总体语句不会被回滚,可是使用CONTINE当现身谬误后还是会往下实施如若后边的口舌还会有众多的话整个回滚的历程将会非常短,在这里间能够行使循环,当现身错误马上退出循环实施前面包车型大巴if回滚操作,在下风流倜傥篇讲循环语句会写到,接待关心。

     

     

    备注:

        作者:pursuer.chen

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

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

    《欢迎交流讨论》

    变量和规范化,MySQL变量条件 概述 变量在蕴藏进度中会平常被应用,变量的使用方式是三个要害的知识点,特别是在定义准绳那块相当的重...

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

    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,作者开始时期的剧情是如此写的:

    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 ;
    

    不等的有的在于变量定义的名号,即:

    FETCH INTO的变量名绝对无法是你定义CU景逸SUVSO奥迪Q3时SQL语句查出来的列名可能列别名,也就说你定义的变量名既不可能是表中已经存在的列名,也不可能是您定义游标时用过的别称(如本例中的count卡塔尔国,只要二个法规不相符,FETCH INTO就把全部的变量赋NULL值,那一点你可以尝试在FETCH INTO后加一句Select打字与印刷变量名验证。

    在查询到这么些BUG从前去官方网址页面特意看了弹指间是还是不是是作者的语法有不当: ,确信语法没难点,但尾数第二条钻探呈现大概是列名的隐瞒BUG,最终一条商议理论了BUG说法,但还没主意自个儿要么基于BUG REPORT做了上述改善,然后效用就不奇怪了。

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

    那么再回头看一下官方网站文档下的末段一条批评,最初自己以为最后一条反对BUG的评头论脚完全部都以闲扯,是哪些傻X说那不是个BUG的?后来细心想了想,他俩都对,那着实也算个BUG,傻X的也是自个儿。

    贴一下页面下最后两条商酌(停止2018.08.01卡塔尔国:

    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的链接。

    Jeremi(猜想也许是个工程师卡塔尔回答,那是叁个举世知名的误会,当您证明了变量a(开端值为NULL卡塔 尔(英语:State of Qatar),然后FETCH INTO a就约等于set a=a,在别的程序语言中那都是无解的。

    据此在编排存款和储蓄进程中为定义的变量加个前缀标志是很好的习于旧贯,想起此前Oracle写存款和储蓄进度真的都加v_前缀,SQL Server 都用@前缀,未来轮到mysql却忽略了,确实须要深深记住下。

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:MySQL变量条件,MySQL游标循环抽出空值的BUG

    关键词: