Example select limit in mysql with nodejs

Limit the Result

You can limit the number of records returned from the query, by using the "LIMIT" statement:

Example: Select the 5 first records in the "customers" table:

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

con.connect(function(err) {

  if (err) throw err;

  var sql = "SELECT * FROM customers LIMIT 5";

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlSelectLimitData.js

[ RowDataPacket { id: 1, name: 'nam1', address: 'Highway 71' },

  RowDataPacket { id: 2, name: 'name2', address: 'Lowstreet 4' },

  RowDataPacket { id: 3, name: 'name3', address: 'Apple st 652' },

  RowDataPacket { id: 4, name: 'name4', address: 'Mountain 21' },

  RowDataPacket { id: 5, name: 'name5', address: 'Valley 345' } ]

 

Start From Another Position

If you want to return five records, starting from the third record, you can use the "OFFSET" keyword:

Example: Start from position 3, and return the next 5 records

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

 

con.connect(function(err) {

  if (err) throw err;

  //Return 5 customers, starting from position 2:

  con.query("SELECT * FROM customers LIMIT 5 OFFSET 2", function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlSelectLimitData.js

[ RowDataPacket { id: 3, name: 'name3', address: 'Apple st 652' },

  RowDataPacket { id: 4, name: 'name4', address: 'Mountain 21' },

  RowDataPacket { id: 5, name: 'name5', address: 'Valley 345' },

  RowDataPacket { id: 6, name: 'name6', address: 'Ocean blvd 2' },

  RowDataPacket { id: 7, name: 'name7', address: 'Green Grass 1' } ]

 

Note: "OFFSET 2", means starting from the third position, not the second!

Shorter Syntax

You can also use write your SQL statement like this "LIMIT 2, 5" which returns the same as the offset example above:

Example: Start from position 3, and return the next 5 records

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

con.connect(function(err) {

  if (err) throw err;

  //Return 5 customers, starting from position 2:

  con.query("SELECT * FROM customers LIMIT 2, 5", function (err, result) {

    if (err) throw err;

    console.log(result);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlSelectLimitData.js

[ RowDataPacket { id: 3, name: 'name3', address: 'Apple st 652' },

  RowDataPacket { id: 4, name: 'name4', address: 'Mountain 21' },

  RowDataPacket { id: 5, name: 'name5', address: 'Valley 345' },

  RowDataPacket { id: 6, name: 'name6', address: 'Ocean blvd 2' },

  RowDataPacket { id: 7, name: 'name7', address: 'Green Grass 1' } ]

 

Note: The numbers are reversed: "LIMIT 2, 5" is the same as "LIMIT 5 OFFSET 2"