How to force MySQL to return limited rows?

I would like to limit the results from mysql query submitted by users. Currently I am just parsing query string by PHP then appending LIMIT in the end. For more complex queries with many nested selects/joins the things are becoming complex and less predictable. Are there common way to force mysql to return only lim开发者_JS百科ited results even if user has asking for full set?

Thanks

Arman

---

You are asking for some server configuration settings to limit the maximum number of rows returned by all queries. That is, select * from mytable will return maximum x number (x is set in global server configuration) or rows even there are many more rows in the table.

Unfortunately, this is not possible in standard distributions of MySQL. You can set limits on maximum queries per hour, even what operations a user can do in server. But, limit of number of rows in query result cannot be set globally.

However, you can get the MySQL community edition source code and add a configuration for global query result limit. Also, if you are using some kind of connector to connect to MySQL (for example, if you are connecting to MySQL from .NET), you can leave the server as is but modify the connector code and add your desired limiting configuration option there.

---

As explained here: Set a default LIMIT in PDO/MySQL when no LIMIT is set, it is possible to limit the maximal number of returned records using the MySQL setting 'sql_select_limit':

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_select_limit

This setting is available from at-least MySQL 5.6.