MySQL 在函数中选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8240501/
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 select in function
提问by joedborg
I'm trying to write a MySQL function with a select inside, but always get a NULL return
我正在尝试编写一个带有 select 的 MySQL 函数，但总是得到一个 NULL 返回值
CREATE FUNCTION test (i CHAR)
RETURNS CHAR
NOT DETERMINISTIC
BEGIN
DECLARE select_var CHAR;
SET select_var = (SELECT name FROM table WHERE id = i);
RETURN select_var;
END$$
mysql> SELECT test('1')$$
+-----------------+
| test('1') |
+-----------------+
| NULL |
+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql>
mysql> SHOW WARNINGS
-> $$
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'i' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
采纳答案by Zohaib
try to specify the size of charreturn type. for example if name can be of 20 characters then try
尝试指定char返回类型的大小。例如，如果名称可以是 20 个字符，则尝试
RETURNS CHAR(20)
回答by Edouard B
Does it works with this :
它是否适用于此：
CREATE FUNCTION test (i CHAR)
RETURNS VARCHAR(SIZE)
NOT DETERMINISTIC
BEGIN
DECLARE select_var VARCHAR(SIZE);
SET select_var = (SELECT name FROM table WHERE id = i);
RETURN select_var;
END$$
回答by user6079391
You have a error in your code, if you need only a result, you obtain it from a simple select an assign with the word INTOlike this, remember, it return the last one.
你的代码有错误，如果你只需要一个结果，你可以从一个简单的选择一个带有INTO字的赋值中获得它，记住，它返回最后一个。
CREATE FUNCTION test (i CHAR)
RETURNS VARCHAR(SIZE)
NOT DETERMINISTIC
BEGIN
DECLARE select_var VARCHAR(SIZE);
SELECT name INTO select_var FROM table WHERE id = i;
RETURN select_var;
END$$
回答by Garr Godfrey
You have a table named table? Escape that name if that is really what it is:
你有一个名为 table 的表吗？如果确实如此，请转义该名称：
(SELECT name FROM `table` WHERE id = i);
or put the table name in....it seems to be missing
或者把表名放进去....好像不见了
