定义 INNER JOIN 表时 MySQL 存储函数语法错误

Question

我的Sql代码是这样的:

DELIMITER $$

CREATE FUNCTION `get_theme`(`section_type` VARCHAR(50))
RETURNS VARCHAR(50)
CONTAINS SQL
SQL SECURITY DEFINER

BEGIN

DECLARE name,dirname VARCHAR(50) ;
SELECT a.name INTO name , a.dirname INTO dirname FROM themes as a
INNER JOIN sections as b ON 
a.section_id = b.id 
WHERE b.name = 'administrators' AND a.`status` = 'Y';

RETURN name + '#' + dirname;
END $$

DELIMITER ;

当我在 heidiSQL 9.2.0.4961 中执行此代码时，我收到此错误: undefined variable :a

但是当我执行这段代码时:

 SELECT a.name as name , a.dirname as dirname FROM themes as a
 INNER JOIN sections as b ON 
 a.section_id = b.id 
 WHERE b.name = 'administrators' AND a.`status` = 'Y';

没有任何问题，并且工作完全正常。

我的代码有什么问题吗？有什么想法吗？

Answer 1

您使用的语法是错误的。正确的语法是:

SELECT list_of_expressions INTO list_of_variables FROM ...

替换此片段:

SELECT a.name INTO name , a.dirname INTO dirname FROM themes as a

这个:

SELECT a.name, a.dirname INTO name , dirname FROM themes as a

<小时/>

注意一点:不要为变量分配与表中列相同的名称，例如:DECLARE name,dirname，如果您以后想使用它们，它会导致困惑和错误在查询中。使用变量前缀，例如:DECLARE v_name,v_dirname