CREATE PROCEDURE 'seckill'.'execute_seckill'(IN v_seckill_id bigint,IN v_phone bigint,IN v_kill_time TIMESTAMP ,out r_result INT )
BEGIN
DECLARE insert_count INT DEFAULT 0;
start TRANSACTION ;
INSERT ignore INTO success_killed(seckill_id,user_phone,create_time) VALUES (v_seckill_id,v_phone,v_kill_time);
SELECT ROW_COUNT() INTO insert_count;
IF(insert_count==0) THEN
ROLLBACK ;
SET r_result=-1;
ELSEIF(insert_count<0) THEN
ROLLBACK ;
SET r_result=-2;
ELSE
update seckill set number = number - 1
where seckill_id = #{seckillId}
and start_time <= #{killTime}
and end_time >= #{killTime}
and number > 0;
SELECT ROW_COUNT() INTO insert_count;
IF(insert_count==0) THEN
ROLLBACK ;
SET r_result=0;
ELSEIF(insert_count<0) THEN
ROLLBACK ;
SET r_result=-2;
ELSE
COMMIT ;
SET r_result=1;
END IF;
END IF;
END ;
$$
楼上正解,反单引号用错了,你一般看到“ERROR 1064 (42000): You have an error in your SQL syntax”,都是语法错误
将存储过程名上的单引号改为反单引号(ESC下面),就可以了
去掉这个前缀"'seckill'."就OK了,你可能已经进到这个库里了,不需要再加数据库前缀了