MySQL存储函数时出错ERROR 1064& 1327 [英] Errors with MySQL stored function creation ERROR 1064 & 1327

问题描述

  DELIMITER // 
 
我使用MySQL v5.1.36，并尝试使用此代码创建存储函数。 
 CREATE FUNCTION`modx`.getSTID（x VARCHAR（255））RETURNS INT DETERMINISTIC 
 BEGIN 
 DECLARE y INT; 
 SELECT id INTO y 
 FROM`modx`.coverage_state 
 WHERE`coverage_state`.name = x; 
 RETURN y; 
 END // 
  

当进入MySQL控制台时，我收到了这个响应。

  mysql> DELIMITER // 
 mysql> CREATE FUNCTION`modx`.getSTID（x VARCHAR（255））RETURNS INT DETERMINISTIC 
  - > BEGIN 
  - > DECLARE y INT; 
错误1064（42000）：您的SQL语法错误;检查
对应于你的MySQL服务器版本的手册，在
行3 
 mysql>附近使用正确的语法。 SELECT id INTO y 
  - > FROM`modx`.coverage_state 
  - > WHERE`coverage_state`.name = x; 
错误1327（42000）：未声明的变量：y 
 mysql>返回y; 
错误1064（42000）：您的SQL语法错误;检查手册，
对应于您的MySQL服务器版本，以便在第1行'RETUR 
 N y'附近使用正确的语法
 mysql> END // 
  

我可以在网上找到我的语法是正确的。我在做什么错了？

解决方案

我通过在我的变量周围加上``来解决我的问题。

pre $ DELIMITER //
CREATE FUNCTION`modx`.getSTID（x VARCHAR （255））RETURNS INT DETERMINISTIC
BEGIN
DECLARE`y` INT;
SELECT id INTO`y`
FROM`modx`.coverage_state
WHERE`coverage_state`.name =`x`;
RETURN'y`;
END //
DELIMITER;


I am using MySQL v5.1.36 and I am trying to create a stored function using this code.

DELIMITER //
CREATE FUNCTION `modx`.getSTID (x VARCHAR(255)) RETURNS INT DETERMINISTIC
    BEGIN
    DECLARE y INT;
    SELECT id INTO y
    FROM `modx`.coverage_state
    WHERE `coverage_state`.name = x;
    RETURN y;
    END//

When entered into the MySQL Console I get this response.

mysql>  DELIMITER //
mysql>  CREATE FUNCTION `modx`.getSTID (x VARCHAR(255)) RETURNS INT DETERMINISTIC
    ->          BEGIN
    ->          DECLARE y INT;
ERROR 1064 (42000): 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 '' at
line 3
mysql>          SELECT id INTO y
    ->          FROM `modx`.coverage_state
    ->          WHERE `coverage_state`.name = x;
ERROR 1327 (42000): Undeclared variable: y
mysql>          RETURN y;
ERROR 1064 (42000): 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 'RETUR
N y' at line 1
mysql>          END//

From what I can find online my syntax is correct. What am I doing wrong?

解决方案

I solved the my issues by adding `` around my variables. Here is the code I ended up with.

DELIMITER //
CREATE FUNCTION `modx`.getSTID (x VARCHAR(255)) RETURNS INT DETERMINISTIC
    BEGIN
    DECLARE `y` INT;
    SELECT id INTO `y`
    FROM `modx`.coverage_state
    WHERE `coverage_state`.name = `x`;
    RETURN `y`;
    END//
DELIMITER ;

这篇关于MySQL存储函数时出错ERROR 1064& 1327的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！