在 MySQL 存储函数中声明整数变量时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11757441/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error declaring integer variable inside MySQL stored function
提问by Paul Freeman-Powell
I'm getting an error when trying to declare a new stored function in MySQL (Server version: 5.5.13)
尝试在 MySQL 中声明新的存储函数时出现错误（服务器版本：5.5.13）
Basically, I have a large table which classifies strings depending on how they start. My function takes a string (from user input) and then tells you the classification of that string by searching the database for the classification. It's a bit like a LIKE query, except in reverse as it's the user input that contains the full string and the database contains the string being searched for. Hope that makes sense!
基本上，我有一个大表，它根据字符串的开始方式对字符串进行分类。我的函数接受一个字符串（来自用户输入），然后通过在数据库中搜索分类来告诉您该字符串的分类。它有点像 LIKE 查询，只是相反，因为它是包含完整字符串的用户输入，而数据库包含正在搜索的字符串。希望这是有道理的！
The concept and logic behind it work fine as I've written/developed this in PHP and it works beautifully, however when trying to translate this into a stored function I'm getting an error from MySQL. The code of the function is:
它背后的概念和逻辑运行良好，因为我已经在 PHP 中编写/开发了它，并且运行良好，但是当我尝试将其转换为存储函数时，我收到了来自 MySQL 的错误消息。该函数的代码是：
delimiter $
DROP FUNCTION IF EXISTS get_string_class$
CREATE FUNCTION get_string_class(mystring VARCHAR(15))
RETURNS VARCHAR(15)
READS SQL DATA
BEGIN
DECLARE i INT;
SET i = 2;
DECLARE mystringlength INT;
SET mystringlength = LENGTH(mystring);
DECLARE segment VARCHAR(15);
DECLARE String_Class VARCHAR(15);
SET String_Class = NULL;
WHILE i <= mystringlength DO
SET segment = LEFT(mystring, i);
SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment);
IF SELECT FOUND_ROWS() = 1 THEN
RETURN String_Class
END IF;
i = i + 1;
END WHILE;
RETURN String_Class;
END$
delimiter ;
I'm getting this error:
我收到此错误：
#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 mystringlength INT; SET mystringlength = LENGTH(mystring);
DECLARE segm' at line 10
I've done lots of playing around to trying and work out where I'm going wrong. I've even stripped out the loop altogether to test it, but still get the same error. Does anyone know what I've done wrong where declaring that INT variable? It's probably something incredibly basic...!
我已经做了很多尝试，试图找出我哪里出错了。我什至完全去掉了循环来测试它，但仍然得到同样的错误。有谁知道我在声明 INT 变量时做错了什么？这可能是一些非常基本的东西......！
Thanks very much in advance.
首先十分感谢。
回答by Devart
There were some syntax errors -
有一些语法错误 -
One of them is - all declarations must be at the begining of the BEGIN...END clause.
其中之一是 -所有声明都必须位于 BEGIN...END 子句的开头。
DELIMITER $
DROP FUNCTION IF EXISTS get_string_class$
CREATE FUNCTION get_string_class(mystring VARCHAR(15))
RETURNS VARCHAR(15)
READS SQL DATA
BEGIN
DECLARE i INT;
DECLARE segment VARCHAR(15);
DECLARE String_Class VARCHAR(15);
DECLARE mystringlength INT;
SET i = 2;
SET mystringlength = LENGTH(mystring);
SET String_Class = NULL;
WHILE i <= mystringlength DO
SET segment = LEFT(mystring, i);
SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment);
IF (SELECT FOUND_ROWS()) = 1 THEN
RETURN String_Class;
END IF;
SET i = i + 1;
END WHILE;
RETURN String_Class;
END$
DELIMITER ;
回答by Omesh
DECLAREpart inside stored procedure must be at top and SETand other statemnts starts after that.
DECLARE存储过程中的部分必须在顶部SET，其他语句在此之后开始。
