mysql limit分页用法讲解

1.情景展示

在Oracle当中，使用rownum来进行分页；

而在mysql中，是没有rownum的，需要通过limit来实现。

2.用法介绍

limit [offset,]rows

limit后面可以跟一个参数，也可以跟两个参数；

格式一：

limit rows

rows代表行数，表示：获取前rows行数据。

等价于：

limit 0, rows

格式二：

limit offset,rows

offset代表偏移量，rows代表行数，表示：获取偏移量后面的rows行数据，相当于查询的是：

offset<rownum≤rows

或offset+1≤rownum≤rows。

偏移量的初始值为0（不是1），这就是可以解释，为什么格式一当中的两种方式一样的原因啦。

举例子：

-- 获取表前5行数据
SELECT 1 FROM meta_theme ORDER BY THEMEID LIMIT 5;
SELECT 1 FROM meta_theme ORDER BY THEMEID LIMIT 0,5;

 获取第6-10行数据

分析：

6-10行，也就是10-6+1=5，共5行数据；

由于limit第一个参数的偏移量不在查询范围内，所以，要想第6行包含在内，需要：6-1=5。

使用limit的问题，就在进行分页查询的时候，有点绕。

知道了limit偏移量的特性之后，我们可以发现：

要想，将第一行数据包含在内，就需要限制limit的第一个参数值必须为0；

否则的话，我们进行分页时，将会将以第一条数据遗漏，比方说：

分页虽然从数量量没有错，但是，此时已经将第一条数据排除在外了。

所以，需要保证，limit的第一页数据包含第一行记录，也就是limit需要从0开始。

LIMIT (pageIndex-1)*pageSize, pageSize

pageSize起始值为1。

注意事项：

limit 后面只能跟数字，不能跟其它

-- 错误用法1：limit后面跟 字符串转数字函数
LIMIT CAST('10' AS SIGNED)
-- 错误用法2：limit后面跟 数字计算
LIMIT 20-10

3.性能分析

基本用法

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...

在中小数据量的情况下，这样的SQL足够用了；

我们可以使用了复合索引来提升查询速度（where的限制条件与order by的排序条件组合成联合索引）；

SELECT THEMEID,2 test FROM meta_theme where STATUS=1 ORDER BY THEMEID LIMIT 0,5;

在这里，为了提升查询速度，我们就可以给字段STATUS+THEMEID，创建索引。

PS：表主键是唯一索引。

子查询分页

随着数据量的增加，页数会越来越多，越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢；

可以通过子查询的方式来提高分页效率。

-- 查询第10001-10010条数据
SELECT
	THEMENAME,
	THEMECODE 
FROM
	meta_theme 
WHERE
	THEMEID >= ( SELECT THEMEID FROM meta_theme WHERE THEMELEVEL = 5 ORDER BY THEMEID LIMIT 10000, 1 ) 
	LIMIT 10

自连接

也可以通过自连接的方式来提高分页效率。

SELECT t1.THEMEID, THEMENAME, THEMECODE FROM meta_theme t1,( SELECT THEMEID FROM meta_theme ORDER BY THEMEID DESC LIMIT 10, 1 ) t2 
WHERE
	t1.THEMEID < t2.THEMEID 
ORDER BY
	t1.THEMEID DESC 
	LIMIT 10;

小结：

可以利用类似策略模式的方式去处理分页，比如判断如果是一百页以内，就使用最基本的分页方式，大于一百页，则使用子查询的分页方式。 

2024年1月31日15:26:03

4.java动态赋值

// 获取指定表名的分页数据（mysql：按主键升序排列）
private final static String MYSQL_GET_TABLE_DATAS = "select * from TABLE_NAME t order by t.PRIMARY_KEY limit START, PAGE_SIZE";
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
/**
 * 生成JdbcTemplate
 * @param driverClassName
 * @param url
 * @param username
 * @param password
 * @return
 */
public static JdbcTemplate getJdbcTemplate(String driverClassName,
                                           String url,
                                           String username,
                                           String password) {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(driverClassName);
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);

    return new JdbcTemplate(dataSource);
}
/**
 * 获取mysql表分页数据
 * @param database 数据库对象
 * @param tableName 表名
 * @return
 */
public static List<Map<String, Object>> getMysqlTableDatas(FlinkDatabase database, String tableName, String primaryKey, Long start, Long pageSize) {
    JdbcTemplate template = JDBCUtils.getJdbcTemplate(database.getDatabasedriver(), database.getDatabaseurl(), database.getUsername(), database.getPassword());

    // 要执行的SQL
    String executeSql = MYSQL_GET_TABLE_DATAS;
    // 替换表名
    executeSql = executeSql.replace("TABLE_NAME", tableName);
    // 替换主键
    executeSql = executeSql.replace("PRIMARY_KEY", primaryKey);
    // 替换开始页
    executeSql = executeSql.replace("START", start + "");
    // 替换结束页
    executeSql = executeSql.replace("PAGE_SIZE", pageSize + "");

    return template.queryForList(executeSql);
}

查询总行数

// 获取指定表名的所有行数
private final static String GET_TABLE_TOTAL_ROWS = "SELECT COUNT(0) TOTAL FROM TABLE_NAME";
/**
 * 查询指定表总行数
 * @param database 数据库对象
 * @param tableName 表名
 * @return
 */
public static Long getTableRows(FlinkDatabase database, String tableName) {
    JdbcTemplate template = JDBCUtils.getJdbcTemplate(database.getDatabasedriver(), database.getDatabaseurl(), database.getUsername(), database.getPassword());

    String executeSql = GET_TABLE_TOTAL_ROWS;
    // 替换表名
    executeSql = executeSql.replace("TABLE_NAME", tableName);

    return template.queryForObject(executeSql, Long.class);
}

  

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

posted @ 2021-11-29 16:16  Marydon  阅读(3383)  评论(0)  编辑  收藏  举报