php - MySql 中的声明错误

Question

DECLARE @num INT DEFAULT = 5; 

SELECT * FROM page p LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Art-Rock-Band' LIMIT @num

UNION ALL

SELECT * FROM page p LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Echo-Pop-Preisträger' LIMIT @num

UNION ALL

SELECT * FROM page p LEFT JOIN categorylinks c ON p.page_id = c.cl_from 
WHERE c.cl_to = 'Englische_Band' LIMIT @num

它返回错误

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 @num INT DEFAULT = 5' at line 1

我不知道如何修复这个错误，DECLARE 应该可以工作。

Answer 1

假设这是在 begin/end block 中，您不需要 = 符号:

DECLARE @num INT DEFAULT 5; 

或者，你可以这样做:

SET @num = 5;

编辑:

那么看起来你的代码块不是存储程序。作为documentation明确指出:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

因此，您想要执行的操作在存储过程、用户定义函数、触发器或动态 SQL 中是允许的。但是，它不允许只用于任何 SQL 查询。