PHP MySQL PDO实现按保留行数删除用户密码历史冗余数据

问题说明 #

我是PHP和MySQL新手，练手开发登录系统时遇到问题：用户创建或修改密码时，能正常更新users表并向pwdhistory插入记录，但想限制每个用户在pwdhistory表中的记录最多3条——当记录超过3条时，需要保留最新的3条，删除最旧的冗余记录。但现有代码会把该用户的所有记录全部删除，求解决方法。

表结构 #

pwdhistory表 #

CREATE TABLE `pwdhistory` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(150) NOT NULL,
  `changedate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 

users表 #

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(25) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(50) NOT NULL,
  `date_created` datetime NOT NULL,
  `last_pwd_reset` int(11) NOT NULL,
  `last_login_date` datetime NOT NULL,
  `status` int(11) NOT NULL,
  `role` varchar(25) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

现有代码问题 #

当前删除逻辑会删除指定用户的所有记录，原因有两个：

1. 删除函数里按changedate DESC排序，每次删除的是最新的一条记录，而不是最旧的；
2. 循环逻辑里没有更新记录数，初始$checkUserPwdRecords为4，删除一条后记录数变成3，但循环依然用初始值判断，导致无限循环删完所有记录。

现有代码片段：

// 检查用户密码记录数是否超过3
$checkUserPwdRecords = checkUserPwdRecords($id); // 返回4

if($checkUserPwdRecords > 3) {
    do{
        $deleteUserPwdRecords = deleteUserPwdRecords($id);
    } while($checkUserPwdRecords > 3);  
}

function checkUserPwdRecords($id) {
    global $pdo;
    $query = "SELECT password FROM pwdhistory WHERE user_id = ? ";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(1, $id);           
    $stmt->execute();
    $result = $stmt->rowCount();
    return $result;     
}

function deleteUserPwdRecords($id) {
    global $pdo;
    $query = "DELETE FROM pwdhistory WHERE user_id = ? AND changedate IS NOT NULL order by changedate desc LIMIT 1 ";       
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(1, $id);
    $stmt->execute();       
}

解决方案 #

方案一：修复现有循环逻辑 #

1. 修改删除函数，改为删除最旧的记录：

function deleteUserPwdRecords($id) {
    global $pdo;
    // 按changedate升序排序，删除最旧的一条
    $query = "DELETE FROM pwdhistory WHERE user_id = ? ORDER BY changedate ASC LIMIT 1 ";       
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(1, $id);
    $stmt->execute();       
}

2. 更新循环逻辑，每次删除后重新获取记录数：

$checkUserPwdRecords = checkUserPwdRecords($id);
if($checkUserPwdRecords > 3) {
    do{
        deleteUserPwdRecords($id);
        // 每次删除后重新查询记录数，更新判断条件
        $checkUserPwdRecords = checkUserPwdRecords($id);
    } while($checkUserPwdRecords > 3);  
}

方案二：单条SQL批量删除（更高效） #

不用先查询记录数，直接用一条SQL删除该用户除最新3条外的所有记录，避免循环操作：

function deleteOldPwdRecords($id) {
    global $pdo;
    // 子查询先获取该用户最新3条记录的id，再删除不在这个列表里的记录
    $query = "DELETE FROM pwdhistory 
              WHERE user_id = ? 
              AND id NOT IN (
                  SELECT id FROM (
                      SELECT id FROM pwdhistory 
                      WHERE user_id = ? 
                      ORDER BY changedate DESC 
                      LIMIT 3
                  ) AS temp_table
              )";
    $stmt = $pdo->prepare($query);
    $stmt->bindParam(1, $id);
    $stmt->bindParam(2, $id);
    $stmt->execute();
}

调用时直接执行：

deleteOldPwdRecords($id);

注：子查询外层套temp_table是因为MySQL不允许直接在DELETE的WHERE子句中引用正在修改的表，临时表可以绕过这个限制。

内容的提问来源于stack exchange，提问作者Ivan Raposo