❤Node07-分页查询

❤Node07-分页查询​

1、优化分页查询接口​

之前我们的接口并没有进行分页的参数设置，在大批量的数据之中一般我们为了性能以及用户的体验，一般还是需要对于表格数据进行分页和优化。因此我们查询接口的分页是十分有必要的，接下来我们就优化一下我们分页查询的方式：

分页参数设置（前端的参数）

分页参数有两个，一个是当前页的页数，另一个是当前页有多少条。

js

pageNum: 1 //页码数（也就是当前是多少页）
pageSize:10 //页条数（也就是当前页是多少条）

我们可以先打印出来看看我们查询蚕食，这边我们可以看到前台传过来的查询参数里面加了 pageNum: '1', pageSize: '10' }的参数，我们把这个参数打印如下图：

console.log(req.query,'req.query');

{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]

先看看我们的数据库查询语句是否正确

js

SELECT * FROM user WHERE age = 18 LIMIT 0, 10

2、拼接上我们的参数​

当我们pageNum:1 pageSize10 的时候返回数据如图

js

{"total": 10,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 3,"name": "教师","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 4,"name": "普通人员","age": "20","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 7,"name": "张三","age": "18","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 12,"name": "可爱","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 20,"name": "san娟","age": "20","sex": 1,"createtime": null,"address": null,"state": 2,"phone": null,"username": null,},{"id": 21,"name": "乐乐","age": "8","sex": 1,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 38,"name": "lin","age": "19","sex": null,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},{"id": 39,"name": "童年的自己","age": "80","sex": null,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,}]
}

修改查询的分页为2，这个时候发现页码已经更改为2 了，查询无误

js

{"total": 10,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,}]
}

3、offset偏移量参数​

这里需要注意的就是这个offset参数 offset是偏移量，通过计算偏移量 (pageNum - 1) * pageSize 来动态生成偏移量，然后将 age、name、偏移量和每页的行数作为参数传递给 SQL 查询的 LIMIT 子句和 WHERE 子句。

我们将查询语句放进去尽心查询，这个时候报错了，查询语句如下：

js

if (pageNum !== undefined&&pageSize !== ''&&pageSize !== null) {query += params.length ? ' AND' : ' WHERE';query += ' LIMIT ?, ?';let offset = (pageNum - 1) * pageSize;params.push(offset);params.push(parseInt(pageSize));}

查询语句之中我们多加了' AND' : ' WHERE',排查修改以后我们更改我们的分页查询方式：

js

const { name, age,sex,pageNum,pageSize } = req.query;
if (pageNum !== undefined&&pageSize !== ''&&pageSize !== null) {query += ' LIMIT ?, ?';let offset = (pageNum - 1) * pageSize;params.push(offset);params.push(10);}

查询无误！

这个时候我将pageSize也给添加的时候发现错误

这个时候一直报错提示我You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''10'' at line 1", sqlState: '42000', index: 0, sql: "SELECT * FROM user LIMIT 0, '10'" }

大致意思就是因为我的数据类型为字符串，转化方式有以下三种，我选择了parseInt 方式

js

parseInt  
parseFloat
Number

• 需要注意的是查询方式，这里需要使用O,10的限制页码条数：

js

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '10' at line 1",
sqlState: '42000',
index: 0,
sql: "SELECT * FROM user LIMIT O,'10'"

ok更改以后查询无误！

js

{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]
{ name: '', age: '', pageNum: '1', pageSize: '10' } req.query
查询条件 SELECT * FROM user LIMIT ?, ? [ 0, 10 ]

4、返回总条数total​

先试试我们的Sql查询语句

js

SELECT COUNT(*) AS total FROM user;

查询效果如下

接下来我们把这个查询加到我们的查询条件里面去

js

connectionpool.query(query,params,(err, results) => {// console.log(err,'err');// console.log(results,'results');if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{let sqltotal = `SELECT COUNT(*) AS total FROM user;`// 查询数据库并返回数据connectionpool.query(sqltotal,(errtotal, total) => { if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{res.json({total,code:'200',data:results,});}});}});

查询以后可以看出我们的查询结果如下,这里的返回结果条数是嵌套在数组里面。

js

{"total": [{"total": 12}],"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},...]
}

优化一下我们的查询方式

js

connectionpool.query(sqltotal,(errtotal, rows) => { let total = rows[0]['total'];if (err) {console.error('Error querying database:', err);res.status(500).json({ error: 'Internal server error' });return;}else{res.json({total:total,code:'200',data:results,});}});

查询成功！

js

{"total": 12,"code": 200,"data": [{"id": 1,"name": "管理员","age": "1888","sex": 1,"address": "地址","state": 1,},{"id": 2,"name": "后台开发人员","age": "18","sex": 2,"createtime": null,"address": null,"state": 1,"phone": null,"username": null,},...]
}