使用MySQL在Node.js中分页 [英] Pagination in nodejs with mysql

问题描述

在我的项目中，我需要使用分页查询数据库，并为用户提供基于当前搜索结果进行查询的功能.像极限之类的东西，我找不到与Node.js一起使用的东西.我的后端是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文件:

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

这篇关于使用MySQL在Node.js中分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！