在mysql bigint变量声明内部自定义nextval函数中出现错误 [英] Error in mysql bigint variable declaration inside custom nextval function

问题描述

我忘了put delimiter指令，一些分号，并使用tsl语法，如[select variable = field]，在mysql中无效。

当您使用tsl语法时，Mysql错误是[不允许返回从函数的结果集]和不帮助很多。

@AndreKR指向我，谢谢。

Im使用mysqlworkbench 5.2.30 CE。
工作函数成为：

 分隔符// 
 CREATE FUNCTION nextval（seq_name varchar（100）） 
 RETURNS bigint（20）
 READS SQL DATA 
 NOT DETERMINISTIC 
 BEGIN 
 DECLARE workval bigint（20）; 
 SELECT count（1）into workval 
 FROM tip_sequence 
 WHERE sequencename = seq_name; 
 IF workval<> 1 THEN 
 DELETE 
 FROM tip_sequence 
 WHERE sequencename = seq_name; 
 INSERT 
 INTO tip_sequence（sequencename，sequenceval，sequencestep）
 VALUES（seq_name，1,1）; 
 END IF; 
 SELECT sequenceval into workval 
 FROM tip_sequence 
 WHERE sequencename = seq_name; 
 UPDATE tip_sequence 
 SET sequenceval = sequenceval + sequencestep 
 WHERE sequencename = seq_name; 
 RETURN workval; 
 END // 
 delimiter; 
   
 
解决方案
由于 DECLARE workval bigint （20）; 行是第一个带有分号的末尾，我怀疑您在输入功能代码之前忘记更改分隔符（虽然这取决于您使用的客户端）。
 
 
 
尝试将您的代码更改为：
  DELIMITER＃
 CREATE FUNCTION nextval（seq_name varchar（100））
 
 ... 
 
 END＃
  
 
I forget put delimiter directive, some semicolon and was using tsl syntax like [select variable = field] that is not valid in mysql.

Mysql error when you use tsl syntax is [not allowed to return a result set from a function] and dont help much.

@AndreKR point all of it to me, thanks.

Im using mysqlworkbench 5.2.30 CE.
The work function become:
delimiter //
CREATE FUNCTION nextval (seq_name varchar(100))  
  RETURNS bigint(20)  
    READS SQL DATA  
  NOT DETERMINISTIC  
    BEGIN  
     DECLARE workval bigint(20);  
     SELECT count(1) into workval  
        FROM tip_sequence  
        WHERE sequencename = seq_name;  
     IF workval <> 1 THEN  
        DELETE  
            FROM tip_sequence  
            WHERE sequencename = seq_name;  
        INSERT  
            INTO tip_sequence (sequencename, sequenceval, sequencestep)  
            VALUES (seq_name, 1, 1);  
     END IF;
     SELECT sequenceval into workval  
        FROM tip_sequence  
        WHERE sequencename = seq_name;  
     UPDATE tip_sequence  
        SET sequenceval = sequenceval + sequencestep  
        WHERE sequencename = seq_name;  
     RETURN workval;
    END//
delimiter ;

解决方案
Since the DECLARE workval bigint(20); line is the first one with a semicolon at the end, I suspect you forgot to change the delimiter before inputting the function code (though this depends on the client you're using).

Try changing your code to:
DELIMITER #
CREATE FUNCTION nextval (seq_name varchar(100))  

...

END#


                        
这篇关于在mysql bigint变量声明内部自定义nextval函数中出现错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！