使用 mysql 在 nodejs 中进行分页

2022-01-04 00:00:00 sql limit node.js mysql pagination

在我的项目中，我需要使用分页查询数据库并为用户提供基于当前搜索结果进行查询的功能.像限制之类的东西，我找不到任何可以与 nodejs 一起使用的东西.我的后端是mysql，我正在写一个rest api.

In my project I need to query the db with pagination and provide user the functionality to query based on current search result. Something like limit, I am not able to find anything to use with nodejs. My backend is mysql and I am writing a rest api.

推荐答案

您可以尝试类似的方法(假设您使用 Express 4.x).

You could try something like that (assuming you use Express 4.x).

使用GET参数(这里page是你想要的页面结果数，npp是每页结果数).

Use GET parameters (here page is the number of page results you want, and npp is the number of results per page).

在此示例中，查询结果设置在响应负载的 results 字段中，而分页元数据设置在 pagination 字段中.

In this example, query results are set in the results field of the response payload, while pagination metadata is set in the pagination field.

至于根据当前搜索结果查询的可能性，你需要扩展一点，因为你的问题有点不清楚.

As for the possibility to query based on current search result, you would have to expand a little, because your question is a bit unclear.

var express = require('express');
var mysql   = require('mysql');
var Promise = require('bluebird');
var bodyParser = require('body-parser');
var app = express();

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'myuser',
  password : 'mypassword',
  database : 'wordpress_test'
});
var queryAsync = Promise.promisify(connection.query.bind(connection));
connection.connect();

// do something when app is closing
// see http://stackoverflow.com/questions/14031763/doing-a-cleanup-action-just-before-node-js-exits
process.stdin.resume()
process.on('exit', exitHandler.bind(null, { shutdownDb: true } ));

app.use(bodyParser.urlencoded({ extended: true }));

app.get('/', function (req, res) {
  var numRows;
  var queryPagination;
  var numPerPage = parseInt(req.query.npp, 10) || 1;
  var page = parseInt(req.query.page, 10) || 0;
  var numPages;
  var skip = page * numPerPage;
  // Here we compute the LIMIT parameter for MySQL query
  var limit = skip + ',' + numPerPage;
  queryAsync('SELECT count(*) as numRows FROM wp_posts')
  .then(function(results) {
    numRows = results[0].numRows;
    numPages = Math.ceil(numRows / numPerPage);
    console.log('number of pages:', numPages);
  })
  .then(() => queryAsync('SELECT * FROM wp_posts ORDER BY ID DESC LIMIT ' + limit))
  .then(function(results) {
    var responsePayload = {
      results: results
    };
    if (page < numPages) {
      responsePayload.pagination = {
        current: page,
        perPage: numPerPage,
        previous: page > 0 ? page - 1 : undefined,
        next: page < numPages - 1 ? page + 1 : undefined
      }
    }
    else responsePayload.pagination = {
      err: 'queried page ' + page + ' is >= to maximum page number ' + numPages
    }
    res.json(responsePayload);
  })
  .catch(function(err) {
    console.error(err);
    res.json({ err: err });
  });
});

app.listen(3000, function () {
  console.log('Example app listening on port 3000!');
});

function exitHandler(options, err) {
  if (options.shutdownDb) {
    console.log('shutdown mysql connection');
    connection.end();
  }
  if (err) console.log(err.stack);
  if (options.exit) process.exit();
}

这是此示例的 package.json 文件:

Here is the package.json file for this example:

{
  "name": "stackoverflow-pagination",
  "dependencies": {
    "bluebird": "^3.3.3",
    "body-parser": "^1.15.0",
    "express": "^4.13.4",
    "mysql": "^2.10.2"
  }
}

相关文章