mysql bigint 声明_在mysql bigint变量声明内部自定义nextval函数中出现错误

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#