mysql分页

第二条SQL子查询，效率高

WITH t_o AS (
SELECT u.*,ROW_NUMBER() OVER(ORDER BY u.created_date ) rn FROM user_info u
)
SELECT t1.* FROM t_o t1 WHERE t1.rn BETWEEN 1 AND 5;

-- 取第6到10条
WITH t_o AS (
SELECT u.*,ROW_NUMBER() OVER(ORDER BY u.created_date ) rn FROM user_info u
)
SELECT t1.* FROM t_o t1 LIMIT 5 , 5 ; -- 第一个参数偏移量，第二个参数指定返回记录行的最大数目


-- 表数据量大时，效率高
WITH tab_order_min AS ( -- 第6条【开始的那一条】的时间
  SELECT created_date FROM user_info u ORDER BY u.`created_date` LIMIT 5 , 1
)
SELECT u.* FROM user_info u , tab_order_min m WHERE u.created_date>=m.created_date ORDER BY u.created_date  LIMIT 0 , 5