EN
Node.js - MySQL LIMIT
0 points
In this article, we would like to show you how to use SQL LIMIT in Node.js.
Note: at the end of this article you can find database preparation SQL queries, you will also find an example of a more parameterized query there.
xxxxxxxxxx1
const mysql = require('mysql');2
3
const connection = mysql.createConnection({ // gets connection with database4
host: 'localhost', // '127.0.0.1'5
user: 'root',6
password: 'password',7
database: 'database_name',8
});9
10
connection.connect((error) => {11
if (error) throw error;12
const query = 'SELECT * FROM `users` LIMIT ?';13
const value = 2;14
15
connection.query(query, [value], (error, result) => { // sends queries16
connection.end(); // closes connection 17
if (error) throw error;18
console.log(connection.sql); // SELECT * FROM `users`19
}); // LIMIT 220
});Output:
xxxxxxxxxx1
[2
{ id: 1, name: 'John', role: 'admin' },3
{ id: 2, name: 'Chris', role: 'moderator' }4
]xxxxxxxxxx1
const mysql = require('mysql');2
3
const connection = mysql.createConnection({ // gets connection with database4
host: 'localhost', // '127.0.0.1'5
user: 'root',6
password: 'password',7
database: 'database_name',8
});9
10
connection.connect((error) => {11
if (error) throw error;12
const query = 'SELECT * FROM `users` ' +13
'LIMIT ? OFFSET ?'; // or shorter 'LIMIT 1, 2'14
const values = [2, 1];15
16
connection.query(query, values, (error, result) => { // sends queries17
connection.end(); // closes connection 18
if (error) throw error;19
console.log(connection.sql); // SELECT * 20
}); // FROM `users`21
}); // LIMIT 2 OFFSET 1Output:
xxxxxxxxxx1
[2
{ id: 2, name: 'Chris', role: 'moderator' },3
{ id: 3, name: 'Kate', role: 'user' }4
]create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `users` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`name` VARCHAR(100) NOT NULL,4
`role` VARCHAR(15) NOT NULL,5
PRIMARY KEY (`id`)6
)7
ENGINE=InnoDB;insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `users`2
(`name`, `role`)3
VALUES4
('John', 'admin'),5
('Chris', 'moderator'),6
('Kate', 'user'),7
('Denis', 'moderator');xxxxxxxxxx1
// ...2
connection.connect((error) => {3
if (error) throw error;4
const query = `SELECT * FROM ??5
LIMIT ?`;6
const values = ['users', 2];7
8
connection.query(query, values, (error, result) => { // sends queries9
connection.end(); // closes connection 10
if (error) throw error;11
console.log(connection.sql); // SELECT * FROM `users`12
}); // LIMIT 213
});The names of columns and tables can be placed in the query by a double question mark ??.
