如何用MySQL/PHP获取每条记录对应最近有效HOT/COLD状态及切换状态

我有一个事件数据库，用PHP实现了两个插入功能：

• 每小时插入一条带HOT或COLD状态的记录
• 每分钟插入一条无状态的其他数据

显示数据时，需要为每条记录匹配最近的非空HOT/COLD状态；同时希望能获取每条记录的下一个不同状态的记录。

示例数据表：

我尝试用PHP循环结合get_last_record()函数获取最近有效状态，但所有无状态记录都显示同一个状态，现在寻求MySQL或PHP的解决方案。

当前的get_last_record()函数总是查询数据库中最后一条带状态的记录，而不是当前记录之前的最近状态，导致所有无状态记录都用同一个状态：

PHP循环代码 #

while($row = mysqli_fetch_assoc($result)){
   if($row['status'] == "HOT"){
     echo "HOT";
   }else if($row['status'] == "COLD"){
      echo "COLD";
   }else{
     // 无状态记录时获取最近状态
     $hourly_record = get_last_record();
     echo $hourly_record['status'];
   }
}

get_last_record()函数 #

function get_last_record() {
    $sql = "SELECT * FROM events WHERE status = 'HOT' OR status = 'COLD' ORDER BY ID DESC LIMIT 1";
    // 执行查询逻辑
    return $rows;
}

方案一：MySQL原生查询（推荐） #

直接通过SQL为每条记录匹配最近的前置状态和下一个不同状态，效率更高，避免PHP循环中多次查询数据库。

查询最近的前置状态 #

使用关联子查询，为每条记录找到ID小于当前记录ID的最后一条带HOT/COLD的记录：

SELECT 
    e.id,
    e.status AS original_status,
    e.frequency,
    (SELECT status 
     FROM events 
     WHERE status IN ('HOT', 'COLD') 
       AND id < e.id 
     ORDER BY id DESC 
     LIMIT 1) AS latest_status
FROM events e
ORDER BY e.id ASC;

查询下一个不同状态 #

如果需要获取当前记录之后的下一个不同状态，可以用另一个关联子查询：

SELECT 
    e.id,
    e.status AS original_status,
    e.frequency,
    (SELECT status 
     FROM events 
     WHERE status IN ('HOT', 'COLD') 
       AND id < e.id 
     ORDER BY id DESC 
     LIMIT 1) AS latest_status,
    (SELECT status 
     FROM events 
     WHERE status IN ('HOT', 'COLD') 
       AND id > e.id 
       AND status != (SELECT status FROM events WHERE status IN ('HOT','COLD') AND id < e.id ORDER BY id DESC LIMIT 1)
     ORDER BY id ASC 
     LIMIT 1) AS next_different_status
FROM events e
ORDER BY e.id ASC;

方案二：PHP优化方案 #

如果必须用PHP处理，可以预先查询所有带状态的记录，然后在循环中为每条无状态记录匹配最近的前置状态，避免每次循环都查数据库：

优化后的PHP代码 #

// 先查询所有带HOT/COLD的记录，按ID排序
$statusRecordsQuery = "SELECT id, status FROM events WHERE status IN ('HOT', 'COLD') ORDER BY id ASC";
$statusResult = mysqli_query($conn, $statusRecordsQuery);
$statusRecords = [];
while($statusRow = mysqli_fetch_assoc($statusResult)){
    $statusRecords[] = $statusRow;
}

// 查询所有事件记录
$result = mysqli_query($conn, "SELECT * FROM events ORDER BY id ASC");

$currentStatusIndex = 0;
$totalStatusRecords = count($statusRecords);

while($row = mysqli_fetch_assoc($result)){
    if(in_array($row['status'], ['HOT', 'COLD'])){
        // 更新当前状态索引，确保后续记录用最新的前置状态
        foreach($statusRecords as $idx => $sr){
            if($sr['id'] == $row['id']){
                $currentStatusIndex = $idx;
                break;
            }
        }
        echo $row['status'];
    }else{
        // 找到当前记录之前的最近状态
        while($currentStatusIndex < $totalStatusRecords - 1 && $statusRecords[$currentStatusIndex + 1]['id'] < $row['id']){
            $currentStatusIndex++;
        }
        echo $statusRecords[$currentStatusIndex]['status'];
    }
}

获取下一个不同状态（PHP版） #

可以预先把状态记录按顺序存储，然后为每条记录找到第一个ID大于当前记录且状态不同的条目：

// 承接上面的$statusRecords数组
function getNextDifferentStatus($currentId, $currentStatus, $statusRecords){
    foreach($statusRecords as $sr){
        if($sr['id'] > $currentId && $sr['status'] != $currentStatus){
            return $sr['status'];
        }
    }
    return null; // 没有下一个不同状态时返回null
}

// 在循环中使用：
if(in_array($row['status'], ['HOT', 'COLD'])){
    $currentStatus = $row['status'];
    echo $currentStatus;
    $nextStatus = getNextDifferentStatus($row['id'], $currentStatus, $statusRecords);
    echo " | 下一个不同状态：".($nextStatus ?? '无');
}else{
    $currentStatus = $statusRecords[$currentStatusIndex]['status'];
    echo $currentStatus;
    $nextStatus = getNextDifferentStatus($row['id'], $currentStatus, $statusRecords);
    echo " | 下一个不同状态：".($nextStatus ?? '无');
}

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