How to force MySQL to return limited rows?

Question

I would like to limit the results of the mysql query submitted by users. I am currently just parsing a PHP query string and then adding a LIMIT at the end. For more complex queries with many nested choices / joins, things become complex and less predictable. Is there a general way to force mysql to return only limited results, even if the user requests a complete set?

thanks
Arman

Answer 1

You request server configuration settings to limit the maximum number of rows returned by all requests. That is, select * from mytable will return the maximum number x (x is specified in the global server configuration) or rows, even in the table there are more rows.

Unfortunately, this is not possible with standard MySQL distributions. You can set limits on the maximum requests per hour, even those operations that the user can do on the server. But the row limit as a result of the query cannot be set globally.

However, you can get the source code for the MySQL community and add configuration for the global limit of the query result. In addition, if you use some kind of connector to connect to MySQL (for example, if you connect to MySQL from .NET), you can leave the server as it is, but change the connector code and add the necessary configuration restriction parameter there.