Node + MySQL 实现分页+模糊查询

本教程主要讲关于分页模糊查询的文章，我认为这个功能是需求很大的。如果模糊查询一个商品的时候，可以进行地修改或者删除一个商品。

上次讲的是关于Node+MySQL分页的文章，不过我现在创建一个简单的项目案例是ES6写法，修改部分的代码。

Promise是异步代码实现控制流的一种方式，因为代码干净、可读并且健壮。
包括如何创建 Promise 对象、使用 Promise、处理集合、Promise 实用方法和错误处理等。
安装bluebird

npm install bluebird

创建Promise

const Promise = require("bluebird");

定义queryAsync常量值

const queryAsync = Promise.promisify(connection.query.bind(connection));

then()的rejected回调作用几乎一致,.catch()的作用是捕获Promise的错误。

promise.then(
    () => { console.log('this is success callback') }
).catch(
    (err) => { console.log(err) }
)

定义常量

    var total;  //总页数
    var queryPagination;
    var pageNum = parseInt(req.query.pageNum, 10) || 1; // 每页码
    var page = parseInt(req.query.page, 10) || 0; // 默认页数
    var numPages; 
    var skip = page * pageNum;
    var search = req.query.search;  //查询
  
  
    console.log(req.query);
    console.log(page);
    console.log(pageNum);
    console.log(skip);
    console.log(search);

    var end = pageNum;
    var limit = skip + ',' + end;
    console.log(limit);
    console.log("SELECT * FROM goods LIMIT " + limit);

判断是否模糊查询

if (search != null) {
       queryAsync("select count(*) as total from goods where title like '%" + search + "%'")
            .then((results) => {
                if (results[0]) {
                    total = results[0].total;
                }
            })
            .then(() => queryAsync("select * from goods where title like '%" + search + "%' LIMIT " + limit))
            .then(results => {
                if (results.length != 0) {
                    res.json({
                        "goods": results,
                        "total": total
                    });
                } else {
                    res.json({
                        "message": "无数据..."
                    });
                }
                
            });
    } else {
        queryAsync("select count(*) as total from goods")

商品总页数

queryAsync("select count(*) as total from goods")
            .then(results => {
                total = results[0].total;
                numPages = Math.ceil(total / page);
                console.log('number of pages:', numPages);
            })

获取商品分页并判断分页

.then(() => queryAsync("SELECT * FROM goods LIMIT " + limit))
            .then(results => {
                var data = {
                    results: results,
                    total: total
                };

                if (page > numPages) {
                    data.pagination = {
                        current: page,        //当前页
                        perPage: pageNum,  //每页码
                        prev: page > 0 ? page - 1 : undefined,
                        next: page < numPages - 1 ? page + 1 : undefined
                    }
                } else {
                    err: "查询页面" + page + "是 >= 最大页数" + numPages
                }

                res.json(data);

            })

统一使用catch()来处理错误

.catch(err => {
                console.error(err);
                res.json({ err: err });
            });

在这个例子中，查询结果显示在设置results响应有效载荷的字段，而分页元数据中所设定的pagination字段。

//获取商品列表分页 + 模糊查询
app.get('/good', (req, res) => {
    var total;  //总页数
    var queryPagination;
    var pageNum = parseInt(req.query.pageNum, 10) || 1; // 每页码
    var page = parseInt(req.query.page, 10) || 0; // 默认页数
    var numPages;
    var skip = page * pageNum;
    var search = req.query.search;  //查询

    //console.log(req.query);
    //console.log(page);
    //console.log(pageNum);
    //console.log(skip);
    //console.log(search);

    var end = pageNum;
    var limit = skip + ',' + end;
    //console.log(limit);
    //console.log("SELECT * FROM goods LIMIT " + limit);

    if (search != null) {
        queryAsync("select count(*) as total from goods where title like '%" + search + "%'")
            .then((results) => {
                if (results[0]) {
                    total = results[0].total;
                }
            })
            .then(() => queryAsync("select * from goods where title like '%" + search + "%' LIMIT " + limit))
            .then(results => {
                if (results.length != 0) {
                    res.json({
                        "goods": results,
                        "total": total
                    });
                } else {
                    res.json({
                        "message": "无数据..."
                    });
                }
                
            });
    } else {
        queryAsync("select count(*) as total from goods")
            .then(results => {
                total = results[0].total;
                numPages = Math.ceil(total / page);
                console.log('number of pages:', numPages);
            })
            .then(() => queryAsync("SELECT * FROM goods LIMIT " + limit))
            .then(results => {
                var data = {
                    results: results,
                    total: total
                };

                if (page > numPages) {
                    data.pagination = {
                        current: page,        //当前页
                        perPage: pageNum,  //每页码
                        prev: page > 0 ? page - 1 : undefined,
                        next: page < numPages - 1 ? page + 1 : undefined
                    }
                } else {
                    err: "查询页面" + page + "是 >= 最大页数" + numPages
                }

                res.json(data);

            })
            .catch(err => {
                console.error(err);
                res.json({ err: err });
            });
    }

});

这是原生MySQL分页+模糊查询

使用GET参数（这里page是你想要的结果数，和pageNum是每页结果数），search是你想要模糊查询关键词

我的github地址：完整CRUD+分页模糊查询