MySQL mysql存储的函数参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3970011/
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
mysql stored function parameter
提问by Elliott
I have just started to create a stored function this is my first time so I am having a few problems. Currently I call the function using SELECT test();(test is the function name for now). I want to send a number to the function (username ID) and have the username returned.
我刚刚开始创建一个存储函数，这是我第一次，所以我遇到了一些问题。目前我使用SELECT test();（测试是现在的函数名称）调用该函数。我想向函数发送一个数字（用户名 ID）并返回用户名。
I have this working by using SELECT test(1);1 is the ID of a user in the table. This seems to work as the username is returned, but if I type in any number the same username is returned also.
我通过使用SELECT test(1);1 是表中用户的 ID 来完成这项工作。这似乎在返回用户名时起作用，但如果我输入任何数字，也会返回相同的用户名。
BEGIN
DECLARE new_username VARCHAR(90);
SELECT `username` INTO new_username FROM `users` WHERE `ID` = ID;
return new_username;
END
I have set the paramter as ID int.
我已将参数设置为ID int.
Am I right in thinking that the keyword INTOwill put the value of the username into the variable new_username? If I run it without the INTOI get the error:
我认为关键字INTO会将用户名的值放入变量中new_username是否正确？如果我没有运行它，INTO我会收到错误：
Not allowed to return a result set from a function
不允许从函数返回结果集
Have I made any obvious mistakes in this, I hope I havent done it totallywrong. Thanks for any advice :).
我在这方面有没有犯过明显的错误，我希望我没有完全做错。感谢您的任何建议:)。
Edit :I just added a few more rows into my table , I now get the error:
编辑：我刚刚在表中添加了几行，现在出现错误：
Result consisted of more than one row
结果包含多于一行
Full sql version:
完整的sql版本：
CREATE DEFINER=`elliotts`@`%` FUNCTION `test`(ID int)
RETURNS varchar(32) CHARSET latin1
BEGIN
DECLARE new_username VARCHAR(32);
SELECT `username`
INTO new_username
FROM `users`
WHERE `ID` = ID;
return new_username;
END
采纳答案by OMG Ponies
Use:
用：
DROP FUNCTION IF EXISTS `example`.`test` $$
CREATE FUNCTION `example`.`test` (param INT) RETURNS VARCHAR(32)
BEGIN
DECLARE new_username VARCHAR(32);
SELECT `username`
INTO new_username
FROM `users`
WHERE `ID` = param;
RETURN COALESCE(new_username, 'Username not found');
END $$
Mind that the VARCHAR length of the RETURN value matches the variable, which should match the column length you want to return.
请注意 RETURN 值的 VARCHAR 长度与变量匹配，该变量应与您要返回的列长度匹配。
