MySQL选择功能 [英] MySQL select in function

问题描述

我正在尝试编写一个内部带有select的MySQL函数，但总是得到NULL返回

I'm trying to write a MySQL function with a select inside, but always get a NULL return

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)

推荐答案

尝试指定char返回类型的大小.例如，如果名称可以包含20个字符，则尝试

try to specify the size of char return type. for example if name can be of 20 characters then try

RETURNS CHAR(20)

这篇关于MySQL选择功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！