使用Key进行分页

paging_with_key.sql

SET @start_key=1005069;
SET @paging_size = 20;

SELECT * FROM (
	SELECT
		(@row_num:=@row_num + 1) AS `@row_num`,
		(@row_num_in_page:=CASE
			WHEN f.id = @start_key THEN 0
			WHEN @row_num_in_page>=0 THEN @row_num_in_page+1
			ELSE -1
			END)
		AS `@row_num_in_page`,
		f.*
	FROM
		fabula as f,
		(SELECT @row_num:=0) AS t,
		(SELECT @row_num_in_page:=-1) AS t2
	ORDER BY f.time_happened DESC
) AS fo
WHERE fo.`@row_num_in_page` > 0 AND fo.`@row_num_in_page` <= @paging_size;