在 MySQL 的 LIMIT 子句中使用变量 [英] Using variable in a LIMIT clause in MySQL

问题描述

我正在编写一个存储过程，其中我有一个名为 my_size 的输入参数，它是一个 INTEGER.我希望能够在 SELECT 语句的 LIMIT 子句中使用它.显然这不受支持，有没有办法解决这个问题?

I am writing a stored procedure where I have an input parameter called my_size that is an INTEGER. I want to be able to use it in a LIMIT clause in a SELECT statement. Apparently this is not supported, is there a way to work around this?

# I want something like:
SELECT * FROM some_table LIMIT my_size;

# Instead of hardcoding a permanent limit:
SELECT * FROM some_table LIMIT 100;

推荐答案

搜索结果 这篇文章.我已经粘贴了下面的相关文字.

A search turned up this article. I've pasted the relevant text below.

这是一个论坛帖子，展示了一个准备好的语句示例您为 limit 子句分配了一个变量值:

Here's a forum post showing an example of prepared statements letting you assign a variable value to the limit clause:

http://forums.mysql.com/read.php?98,126379,133966#msg-133966

但是，我认为这个错误应该引起一些注意，因为我不能想象一下，在一个过程中准备好的语句将允许任何过程编译时优化.我有一种准备好的感觉语句在过程运行时编译和执行，哪个概率对效率有负面影响.如果极限子句可以接受正常的过程变量(例如，一个过程参数)，那么数据库仍然可以执行编译时在过程中对查询的其余部分进行优化.这个可能会更快地执行该过程.我不是专家不过.

However, I think this bug should get some attention because I can't imagine that prepared statements within a procedure will allow for any procedure-compile-time optimizations. I have a feeling that prepared statements are compiled and executed at the runtime of the procedure, which probaby has a negative impact on efficiency. If the limit clause could accept normal procedure variables (say, a procedure argument), then the database could still perform compile-time optimizations on the rest of the query, within the procedure. This would likely yield faster execution of the procedure. I'm no expert though.

这篇关于在 MySQL 的 LIMIT 子句中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！