MySQL Multiple Select Options

How can I c开发者_如何转开发onstruct a MySQL query using both the SQL_CALC_FOUND_ROWS and DISTINCT options?

SQL_CALC_FOUND_ROWS only works with the LIMIT clause, and allows you to use the information function FOUND_ROWS() to find the number of rows in the full record set without running another query to count the rows.

SELECT SQL_CALC_FOUND_ROWS * FROM ... WHERE ... LIMIT X;

SELECT FOUND_ROWS();

Since keywords like DISTINCT, and where conditions actually affects the full record set, the SQL_CALC_FOUND_ROWS function has no concept of what's beeing left out. As far as I know, there are no similar information functions available for the DISTINCT keyword, leaving you no other option than to run the query twice.

SELECT DISTINCT * FROM ... WHERE ...;

SELECT COUNT(*) FROM ... WHERE ...;

NOTE: You can still use both SQL_CALC_FOUND_ROWS and DISTINCT in the same query, but FOUND_ROWS(); will only see what's left out by the LIMIT clause.