nodejs encapsulates mysql to implement paging

With the rapid development of the Internet, more and more websites and applications are developed, and the amount of data becomes larger and larger, so data processing and management become more and more important. Among them, paging is a very common data query method, which can easily display large amounts of data and provide users with a better user experience. In development, we generally use MySQL database to store data. This article introduces how to use Node.js to encapsulate MySQL to implement paging query function.

1. Create database table

First, we need to create a table in MySQL to store the data we need. This article takes the "users" table as an example, which contains three fields: id, name, and age. The specific table structure and data are as follows:

CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  age INT(11) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO users (name, age) VALUES ('小明', 18);
INSERT INTO users (name, age) VALUES ('小红', 20);
INSERT INTO users (name, age) VALUES ('小华', 22);
INSERT INTO users (name, age) VALUES ('小张', 25);
INSERT INTO users (name, age) VALUES ('小李', 28);
INSERT INTO users (name, age) VALUES ('小刘', 30);

2. Install MySQL and mysql modules

Next, we need to install the MySQL database and the corresponding mysql module. It can be installed through the following command:

npm install mysql

3. Paging query implementation

Before using the mysql module, we need to connect to the MySQL database through the following code:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test'
});

connection.connect();

Here, we set the connected database name to "test", you need to change it according to your actual situation.

Next, we encapsulate a paging query function to query the specified number of pages of data. The function name is "queryByPage", and the parameters passed in are:

• tableName: the name of the data table to be queried
• pageNum: the number of pages to be queried
• pageSize: The number of data displayed on each page
• callback: callback function, used to receive query results

The function implementation code is as follows:

function queryByPage(tableName, pageNum, pageSize, callback) {
  const start = (pageNum - 1) * pageSize;
  const sql = `SELECT * FROM ${tableName} ORDER BY id DESC LIMIT ${start}, ${pageSize}`;
  connection.query(sql, (err, results) => {
    if (err) {
      return callback(err, null);
    }
    const countSql = `SELECT count(*) as count FROM ${tableName}`;
    connection.query(countSql, (err, count) => {
      if (err) {
        return callback(err, null);
      }
      const totalCount = count[0].count;
      const totalPage = Math.ceil(totalCount / pageSize);
      return callback(null, {
        pageNum,
        pageSize,
        totalCount,
        totalPage,
        data: results
      });
    });
  });
}

The above code implements the following Several functions:

• Calculate the starting position of query data based on the number of pages passed in and the number of data displayed on each page
• Execute MySQL query statements to implement paging queries and obtain Query results
• Execute the MySQL query statement and obtain the total amount of data
• Calculate the total number of pages based on the total amount of data and the number of data displayed on each page
• Page the query results and paging The information is encapsulated into an object for callback

4. Call the paging query function

Finally, we can call the encapsulated paging query function in the Node.js application , and display the query results as needed.

queryByPage('users', 1, 2, (err, result) => {
  if (err) {
    console.error(err.message);
    return;
  }
  console.log(result);
});

The above code will query the first page of data in the "users" table, display 2 records on each page, and output the query results to the console.

5. Summary

This article introduces how to use Node.js to encapsulate MySQL to implement paging query function. By creating database tables, installing the mysql module and encapsulating query functions, we can easily query the data of a specified number of pages and display it in the application. Of course, in actual applications, we also need to improve the code according to the actual situation, and perform error handling and exception handling.

The above is the detailed content of nodejs encapsulates mysql to implement paging. For more information, please follow other related articles on the PHP Chinese website!