I have a process which requires that I insert new records to multiple tables and I thought I'd try using a stored procedure as I am developing in PHP 5.3.5 and using the MySql Server 5.5.8.
我有一个进程,要求我将新记录插入到多个表中,我想我会尝试使用存储过程,因为我在PHP 5.3.5中开发并使用MySql Server 5.5.8。
Created a simple Insert procedure but I wanted to obtain the value of the recently added id so that it could be used for a subsequent insert.
创建了一个简单的Insert过程但我想获取最近添加的id的值,以便它可以用于后续插入。
The id values of the table are set to auto_increment, and are bigint(20). The insert statement works, but when I try to use the DECLARE statement I obtain the following error:
表的id值设置为auto_increment,并且是bigint(20)。 insert语句有效,但是当我尝试使用DECLARE语句时,我得到以下错误:
#1064 - 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 'DECLARE @i BIGINT(20); END' at line >8
#1064 - 您的SQL语法有错误;检查与您的> MySQL服务器版本对应的手册,以便在'DECLARE @i BIGINT(20)附近使用正确的语法;结束'在线> 8
THe stored procedure is as follows:
存储过程如下:
CREATE PROCEDURE test(
IN a VARCHAR(255),
IN b VARCHAR(255),
IN c BIGINT(20)
)
BEGIN
INSERT INTO tableA (datetime_created) VALUE (NOW());
DECLARE @i BIGINT(20); // or BIGINT(20);
SET @i = SELECT id FROM tableA ORDER BY DESC LIMIT 1;
INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, @i);
END
I have removed all statements, and when I build what I would think is a simple stored procedure, the error appears as soon as the second line is added. Any help would be greatly appreciated for I have been searching and attempting to resolve this but I have not found a solution as of yet.
我删除了所有语句,当我构建我认为是一个简单的存储过程时,只要添加第二行就会出现错误。任何帮助将非常感谢因为我一直在寻找并试图解决这个问题,但我还没有找到解决方案。
2 个解决方案
#1
1
There are several problems:
有几个问题:
- variable declarations should come first
- using
DECLARE
you meant to use a local (stored procedure level) variable instead of user variable. For that don't use@
in front of a variable name. - there is a typo in first INSERT. It should be
VALUES
instead ofVALUE
- use
LAST_INSERT_ID()
function to retrieve a value of an auto_incremented column for a row that has been just inserted instead of
变量声明应该首先出现
使用DECLARE你意味着使用本地(存储过程级别)变量而不是用户变量。为此,不要在变量名前面使用@。
在第一次INSERT中有一个拼写错误。它应该是VALUES而不是VALUE
使用LAST_INSERT_ID()函数检索刚刚插入的行的auto_incremented列的值,而不是
Your code might look something like this
您的代码可能看起来像这样
DELIMITER $$
CREATE PROCEDURE test(
IN a VARCHAR(255),
IN b VARCHAR(255),
IN c BIGINT(20)
)
BEGIN
DECLARE i BIGINT(20); // or BIGINT(20);
INSERT INTO tableA (datetime_created) VALUES (NOW());
SET i = LAST_INSERT_ID();
INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, i);
END$$
DELIMITER ;
In this particular case you don't need a variable at all. A revised version of your SP might look like this
在这种特殊情况下,您根本不需要变量。 SP的修订版可能如下所示
DELIMITER $$
CREATE PROCEDURE test(
IN a VARCHAR(255),
IN b VARCHAR(255),
IN c BIGINT(20)
)
BEGIN
INSERT INTO tableA (datetime_created) VALUES (NOW());
INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, LAST_INSERT_ID());
END$$
DELIMITER ;
Here is SQLFiddle demo for both versions
这是两个版本的SQLFiddle演示
#2
0
You have to set delimiter to something else then ;
你必须将分隔符设置为其他内容;
As you are probably using phpMyAdmin, there's an input field for that under the main textarea. The standard is ;
, use eg ##
instead.
由于您可能正在使用phpMyAdmin,因此在主textarea下有一个输入字段。标准是;,使用例如##代替。
#1
1
There are several problems:
有几个问题:
- variable declarations should come first
- using
DECLARE
you meant to use a local (stored procedure level) variable instead of user variable. For that don't use@
in front of a variable name. - there is a typo in first INSERT. It should be
VALUES
instead ofVALUE
- use
LAST_INSERT_ID()
function to retrieve a value of an auto_incremented column for a row that has been just inserted instead of
变量声明应该首先出现
使用DECLARE你意味着使用本地(存储过程级别)变量而不是用户变量。为此,不要在变量名前面使用@。
在第一次INSERT中有一个拼写错误。它应该是VALUES而不是VALUE
使用LAST_INSERT_ID()函数检索刚刚插入的行的auto_incremented列的值,而不是
Your code might look something like this
您的代码可能看起来像这样
DELIMITER $$
CREATE PROCEDURE test(
IN a VARCHAR(255),
IN b VARCHAR(255),
IN c BIGINT(20)
)
BEGIN
DECLARE i BIGINT(20); // or BIGINT(20);
INSERT INTO tableA (datetime_created) VALUES (NOW());
SET i = LAST_INSERT_ID();
INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, i);
END$$
DELIMITER ;
In this particular case you don't need a variable at all. A revised version of your SP might look like this
在这种特殊情况下,您根本不需要变量。 SP的修订版可能如下所示
DELIMITER $$
CREATE PROCEDURE test(
IN a VARCHAR(255),
IN b VARCHAR(255),
IN c BIGINT(20)
)
BEGIN
INSERT INTO tableA (datetime_created) VALUES (NOW());
INSERT INTO tableB(attr1, attr2, attr3, attr4) VALUES (a, b, c, LAST_INSERT_ID());
END$$
DELIMITER ;
Here is SQLFiddle demo for both versions
这是两个版本的SQLFiddle演示
#2
0
You have to set delimiter to something else then ;
你必须将分隔符设置为其他内容;
As you are probably using phpMyAdmin, there's an input field for that under the main textarea. The standard is ;
, use eg ##
instead.
由于您可能正在使用phpMyAdmin,因此在主textarea下有一个输入字段。标准是;,使用例如##代替。