Mysql部分
分页核心就是从结果集截取第M~N条记录
mysql语句 limit M offset N
M指pageSize,即每页显示条数;N指pageSize*(pageIndex-1),即从哪里开始
当offset的N超过数据集的总量,不会报错,只会返回空结果集;当只有limit M 没有N时,不会报错,相当于offset 0
limit N,M 等同于 limit M offset N
当数据量特别大时,需要使用联合索引,来提升查询速度;有索引还是慢,那么用两次select进行分页,第一层只查比如id信息,再用in查询该id对应所需要查询的信息;如果id是自增,还可以先找那条id。
PHP
pdo_mysql的预处理可以防止一些sql注入(id=-1 1=1)
<?php
class DB{
private $dsn;
private $user;
private $password;
private $charset;
private $pdoInstance;
private $pdoStmt;
public funtion _construct($config =[]){
$this->dsn = $config['dsn'];
$this->user = $config['user'];
$this->password= $config['password'];
$this->charset = $config['charset'];
$this->connect();
}
private funtion connect(){
if(!$this->pdoInstance){
$options = [
PDO::AYSQL_ATTR_INIT_COMMAND => 'SET NAMES' . $this->charset
];
$this->pdoInstance = new PDO($this->dsn ,$this->user, $this->password, $options);
$this->pdoInstance->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPRION);
}
}
public funtion query($sql,$parameters = []){
if(!is_array($parameters)){
$parameters=[$parameters];
}
$this->pdoStmt = $this->pdoInstance->prepare($sql);
foreach($parameters as $parameter){
$this->pdoStmt ->bindValue($index++,$parameter[0],$parameter[1]);
}
$ececRe = $this->pdoStmt -> execute();
if(!$ececRe ){
throw new MySQLException ($this->pdoStmt->errorInfo()[2],$this->pdoStmt->errorCode());
}
$data = $this -> pdoStmt->fetchAll(PDO::FETCH_ASSOC);
return $data;
}
}
class MySQLException extends Exception{
}
?>
<?php
require_once 'Db.php';
class App
{
private $db;
public function _construct(){
$this->db=new DB([
'dsn'=>'地址',
'user'=>'root',
'password'=>'';
'charset'=>'utf8',
]);
}
public function run(){
try{
$pageSize=$_GET['page_size']??10;
$pageIndex=$_GET['page_index'];
$data=$this->pagination(intval($pageSize),intval($pageIndex));
$count=intval($this->getCount());
$totalPage=ceil($count/$pageSize);
$info=[
'count'=>$count,
'total_page'=>$totalPage,
'date'=>$date,
]
return $this->returnSuccessData($data);
}catch(Exception $e){
return $this->returnData($e->getCode(),$e->getMessage());
}
}
public function pagination($pageSize , $pageIndex){
$sql='selcet id,title,data from t_blogs where type=1 order by date limit ? offset ?';
$limit =$pageSize;
$offset=$pageSize*($pageIndex-1);
$data=$this->db->query($sql,[$limit,PDO::PARAM_INT],[$offset,PDO::PARAM_INT]);
return $data;
}
public function getCount(){
$sql='selcet count(*) as count from where t_blogs';
$data=$this->db->query($sql);
return $date[0]['count'];
}
public function returnSuccessData($date){
$content=[
'code'=>0,
'message'=>'Success',
'info'=>$date,
];
return json_encode($content);
}
public function returnData($code,$message,$data=[]){
$content=[
'code'=>$code,
'message'=>$message,
'info'=>$date,
];
return json_encode($content);
}
}
$app=new App();
$re=$app->run();
?>
JS
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>分页</title>
</head>
<body>
<table>
<thead>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</thead>
<ul id="list">
<li class="roll">上一页</li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li id="list-page"></li>
<li class="roll">下一页</li>
</ul>
</table>
<script>
getData();
function renderPage(pageArr){
for(let i=0;i<pageArr.length;i++){
$('#list-page').eq(i).text(pageArr[i]);
}
if (pageArr[i]==='...') {
$('#list-page').eq(i).css('border','none');
}
$('#list').on('click','li',function(){
switch(#(this).text()) {
case '...' :
break;
case '上一页':
setCurrentPage(currentPage-1);
getData();
break;
}
})
}
function getData(){
$.ajax('地址',{
type:'GET',
data:{
page_size:pageSize,
page_index=currentPage,
},
dataType:'json'
}).done(function (data){
if(data.code===0){
$('#data').html('');
data.info.data.forEach(element =>{
let el='<tr><td>${element.id}</td><td>${element.title}</td><td></td><td></td><td></td></tr>';
$('#data').append(el);
})
}
else
{
}
}).fail(function (data){
});
}
</script>
</body>
</html>