mysql - SQL 语法错误。创建存储过程

Question

我已经开始使用 MySQL 创建一个存储过程。然后我想进行 migration:up (MyBatis)。

mvn migration:up -Dmigration.path=/path/to/repository

这是我的存储过程

DROP PROCEDURE IF EXISTS add_tips;


CREATE DEFINER=`root`@`localhost` PROCEDURE `add_tips`(gspId INTEGER, gameID INTEGER)
BEGIN

DECLARE @start_datetime = getdate();
DECLARE @execution_time_in_seconds int;
DECLARE @LID int;
INSERT INTO sp_logs(spName, startTime) VALUES(`add_tips`, @start_datetime);
SET @LID = LAST_INSERT_ID();
...
/*some code goes here*/
...
@execution_time_in_seconds = datediff(SECOND,@start_datetime,getdate())
UPDATE sp_logs
SET executionTime = @execution_time_in_seconds
WHERE logId = @LID;

END

执行 migration:up 命令后

我收到一个错误

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@start_datetime = getdate();
[INFO] DECLARE @execution_time_in_seconds int;
[INFO] DECLARE @LI' at line 4

Answer 1

你应该改变'分隔符'

DROP PROCEDURE IF EXISTS add_tips;

delimiter //


CREATE DEFINER=`root`@`localhost` PROCEDURE `add_tips`(gspId INTEGER, gameID INTEGER)
BEGIN

DECLARE @start_datetime = getdate();
DECLARE @execution_time_in_seconds int;
DECLARE @LID int;
INSERT INTO sp_logs(spName, startTime) VALUES(`add_tips`, @start_datetime);
SET @LID = LAST_INSERT_ID();
...
/*some code goes here*/
...
@execution_time_in_seconds = datediff(SECOND,@start_datetime,getdate())
UPDATE sp_logs
SET executionTime = @execution_time_in_seconds
WHERE logId = @LID;

END //

delimiter ;