我无法弄清楚我在制作这个 MYSQL 查询时犯了什么错误 [英] I am not able to figure out what wrong did I commit in making this MYSQL Query

问题描述

CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1));
DELIMITER ;
CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE x INTEGER;
SET @x = 1; 
INSERT INTO t1 VALUES (1); 
SET @x = 2; 
INSERT INTO t1 VALUES (1); 
SET @x = 3; 
END;

当我运行这个查询时，我在第 4 行收到 1064 错误.任何有关如何处理它的提示都非常感谢.

WHEN I run this query I get 1064 error at line 4. Any hint on how to deal with it is highly appreciated.

推荐答案

• 您需要将 Delimiter 设置为任何 except ;
• 或者，检查同名存储过程是否已存在.
• 最后，将 Delimiter 重新定义回 ;
• 除非你打算在这个存储过程之外使用变量x；你真的不需要使用 @;它使该变量在该特定会话中随处可用).
• You need to set Delimiter to anything except ;
• Optionally, put a check if the same name stored procedure already exists.
• At the end, redefine the Delimiter back to ;
• Unless, you are going to use variable x outside this stored procedure; you really dont need to use @; it makes the variable available everywhere in that particular session).

尝试(评论中有更多解释):

Try (more explanation in the comments):

CREATE TABLE t1 (s1 INT, PRIMARY KEY (s1)); -- create the table

DELIMITER $$ -- redefine the delimiter to $$ (for eg)

DROP PROCEDURE IF EXISTS `handlerdemo` $$ -- drop previous if exists

CREATE PROCEDURE handlerdemo ()
  BEGIN

    DECLARE x INT DEFAULT 0; -- datatype is INT 
    -- also a good practice to set default value

    SET x = 1; -- no need to use in Session context
    INSERT INTO t1 VALUES (x); -- use variable name here instead of literal value

    SET x = 2; 
    INSERT INTO t1 VALUES (x); 

  END $$ -- remember that delimiter is $$ right now

-- redefine the Delimiter back to ;
DELIMITER ;

这篇关于我无法弄清楚我在制作这个 MYSQL 查询时犯了什么错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！