Pagination in MySql - How to use LIMIT and OFFSET in MySql


In this MySql tutorial we will learn what is LIMIT and OFFSET in MySql and how to use them in MySql. LIMIT and OFFSET helps us in selecting specific rows and perform pagination in MySql.



OFFSET - default value of OFFSET is 0.
OFFSET +1 is the number from which MySql database engine will start fetching the rows.
LIMIT - It is number of rows that will be fetched from MySql database.



--Execute these MySql database scripts  >
CREATE TABLE my_schema.employee(ID int,NAME  varchar(255) );
INSERT INTO my_schema.employee(ID, NAME)VALUES(1, 'ankit');
INSERT INTO my_schema.employee(ID, NAME)VALUES(2, 'rohit');
INSERT INTO my_schema.employee(ID, NAME)VALUES(3, 'amy');
INSERT INTO my_schema.employee(ID, NAME)VALUES(4, 'ric');
INSERT INTO my_schema.employee(ID, NAME)VALUES(5, 'hay');
INSERT INTO my_schema.employee(ID, NAME)VALUES(6, 'wat');
INSERT INTO my_schema.employee(ID, NAME)VALUES(7, 'kev');
INSERT INTO my_schema.employee(ID, NAME)VALUES(8, 'stu');
INSERT INTO my_schema.employee(ID, NAME)VALUES(9, 'sac');
INSERT INTO my_schema.employee(ID, NAME)VALUES(10, 'gay');
INSERT INTO my_schema.employee(ID, NAME)VALUES(11, 'lax');
INSERT INTO my_schema.employee(ID, NAME)VALUES(12, 'adam');

EMPLOYEE table will look like this >




-- Fetch top 5 rows in MySql database using LIMIT and OFFSET
select * from my_schema.employee limit 5;
select * from my_schema.employee limit 5  OFFSET 0;




-- Fetch rows between 5-10 (including) in MySql database using LIMIT and OFFSET
select * from my_schema.employee limit 6  OFFSET 4;





-- Fetch rows between 6-10 (including) in MySql database using LIMIT and OFFSET
select * from my_schema.employee limit 5  OFFSET 5;





-- Fetch rows between 4-6 (including) in MySql database using LIMIT and OFFSET
select * from my_schema.employee limit 3  OFFSET 3;



-- Fetch rows between 8-11 (including) in MySql database using LIMIT and OFFSET
select * from my_schema.employee limit 4  OFFSET 7;



So in this MySql tutorial we learned what is LIMIT and OFFSET in MySql and how to use them in MySql. LIMIT and OFFSET helped us in selecting specific rows between two rows and perform pagination in MySql.


Related >>

select rows between one number to another in MySql tutorial

How to change the MySql user(root) password tutorial


Labels: MySql
eEdit
Must read for you :