SQL_CALC_FOUND_ROWS是mysql特有的保留字,与此对应使用的 函数是 FOUND_ROWS(),可以在查出数据的同时查出所有满足条件的数据量。例如:
SELECT SQL_CALC_FOUND_ROWS * FROM url WHERE id > 7 LIMIT 2;
SELECT FOUND_ROWS();
java代码实现:
Class.forName("com.mysql.cj.jdbc.Driver");
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kjj_test?characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true",
"root", "pwd")){
Statement statement = connection.createStatement();
//found data list
ResultSet resultSet = statement.executeQuery("SELECT SQL_CALC_FOUND_ROWS * FROM url WHERE id > 7 LIMIT 2;");
while (resultSet.next()){
System.out.println( resultSet.getObject(1) );
}
resultSet.close();
statement.close();
//found rows
statement = connection.createStatement();
resultSet = statement.executeQuery("SELECT FOUND_ROWS();");
while (resultSet.next()){
System.out.println(String.format("rows is %d", resultSet.getObject(1)) );
}
resultSet.close();
statement.close();
}
需要注意的是:这个方法有一个副作用,当使用了SQL_CALC_FOUND_ROWS以后,将不能使用查询缓存、在特殊情况下反而或损失一些性能。