未设置 LIMIT 时，在 PDO/MySQL 中设置默认 LIMIT [英] Set a default LIMIT in PDO/MySQL when no LIMIT is set

问题描述

我想知道是否可以在 PHP 中全局设置和取消设置 MySQL 的默认结果限制.

I am trying to find out if it is possible to globally set and unset a default limit of results from MySQL in PHP.

一些可能看起来像的伪代码:

Some pseudo code of what that might look like:

$pdo->prepare('SELECT * FROM example');
$pdo->setAttribute(PDO::DEFAULT_LIMIT, 1000); // max of 1000 rows returned
$pdo->exec();
$pdo->setAttribute(PDO::DEFAULT_LIMIT, 0); // no limit

// OR

$pdo->query('SET LIMIT_FOR_THIS_MYSQL_SESSION = 1000'); // max of 1000 rows returned
$pdo->prepare('SELECT * FROM example');
$pdo->exec();
$pdo->query('SET LIMIT_FOR_THIS_MYSQL_SESSION = 0');  // no limit

我不确定 PDO/MySQL 是否支持这样的东西.如果不是，是否可以模仿这种行为?(理想情况下不用在 PHP 中解析 SQL，但我会尽量得到)

I am not sure if PDO/MySQL supports anything like this. If not is it possible to emulate this behavior? (Ideally without parsing the SQL in PHP, but I will take what I can get)

注意:在实际代码中，PDO 有一个用于连接和查询的包装类，因此我可以动态控制查询的每个部分发生的情况，并且可以轻松访问查询字符串和查询变量.

NOTE: In the actual code, PDO has a wrapper class for both connections and queries, so I can dynamically control what happens for every part of a query, and have easy access to the query string and query vars.

上下文:

我正在开发一个 API，我们正在尝试设置一次可以返回多少结果的限制.由于有数十个端点，因此能够以某种方式全局设置和取消设置查询限制会容易得多.

I am working on an API and we are trying to setup limits for how many results can be returned at once. Since there are dozens of endpoints, it would be a lot easier to be able to somehow globally set and unset a limit on queries.

由于真实代码如何进行查询的定制性质，类似于上面描述的内容将能够仅在 PDO 包装器(1 个文件)中实现，而不必在每个端点(每个端点)中设置逻辑可以由多个文件组成)来动态添加一个 LIMIT 子句.

Due to the customized nature of how the real code does queries, something like what is described above would be able to be implemented in just the PDO wrapper (1 file), rather than having to setup logic in each endpoint (each of which can be made up of several files) to dynamically add a LIMIT clause.

推荐答案

您可以使用 MySQL 的 sql_select_limit 设置以配置选择应返回的记录数.它的值可以从配置文件中设置，从会话中的代码动态设置.它的值被实际 sql 语句中的任何限制子句覆盖:

You can use MySQL's sql_select_limit setting to configure how many records a select should return. It's value can be set from the config file, dynamically from code within a session. Its value is overridden by any limit clause within the actual sql statement:

从 SELECT 语句返回的最大行数.这新连接的默认值是最大行数服务器允许每桌.典型的默认值为 (232)−1 或(264)-1.如果您更改了限制，则默认值可以是通过分配一个 DEFAULT 值来恢复.

The maximum number of rows to return from SELECT statements. The default value for a new connection is the maximum number of rows that the server permits per table. Typical default values are (232)−1 or (264)−1. If you have changed the limit, the default value can be restored by assigning a value of DEFAULT.

如果 SELECT 有一个 LIMIT 子句，则 LIMIT 优先于sql_select_limit 的值.

If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of sql_select_limit.

这篇关于未设置 LIMIT 时，在 PDO/MySQL 中设置默认 LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！