如何用单条MySQL语句实现芯片状态表的条件插入逻辑？

问题背景 #

现有数据表结构及示例数据如下：

CREATE TABLE chip_status (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    CHIP_ID INT,
    STATUS VARCHAR(1),
    MOD_STATUS_DATE_TIME DATETIME
);

-- 示例数据
INSERT INTO chip_status VALUES 
(1, 123, 'w', '2024-02-07 11:20:06'),
(2, 456, 'i', '2024-02-07 11:21:00');

需要实现以下插入逻辑：

• 若不存在对应CHIP_ID的行，则插入CHIP_ID、新status及当前时间NOW()；
• 若存在对应CHIP_ID的行，但最新（按MOD_STATUS_DATE_TIME排序）的status与新status不同，则插入新记录；
• 若存在对应CHIP_ID的行且最新status与新status相同，则跳过插入。

解决方案 #

可以通过INSERT ... SELECT结合子查询实现，无需额外代码逻辑：

-- 单条插入示例
INSERT INTO chip_status (CHIP_ID, STATUS, MOD_STATUS_DATE_TIME)
SELECT NEW_CHIP_ID, NEW_STATUS, NOW()
FROM (SELECT 123 AS NEW_CHIP_ID, 'i' AS NEW_STATUS) AS new_data
WHERE NOT EXISTS (
    SELECT 1
    FROM chip_status cs
    WHERE cs.CHIP_ID = new_data.NEW_CHIP_ID
    ORDER BY cs.MOD_STATUS_DATE_TIME DESC
    LIMIT 1
    HAVING cs.STATUS = new_data.NEW_STATUS
);

逻辑说明 #

1. new_data子查询：定义要插入的目标芯片ID和状态（替换为实际业务值即可）。
2. NOT EXISTS条件判断：
   • 首先定位对应CHIP_ID的最新记录（按MOD_STATUS_DATE_TIME倒序取第一条）；
   • 若最新记录的STATUS与新状态一致，HAVING条件成立，NOT EXISTS返回假，跳过插入；
   • 若没有对应CHIP_ID的记录，或最新记录的STATUS与新状态不同，NOT EXISTS返回真，执行插入。

批量插入扩展 #

如果需要批量处理多条数据，可扩展new_data子查询为多行：

INSERT INTO chip_status (CHIP_ID, STATUS, MOD_STATUS_DATE_TIME)
SELECT NEW_CHIP_ID, NEW_STATUS, NOW()
FROM (
    SELECT 123 AS NEW_CHIP_ID, 'i' AS NEW_STATUS
    UNION ALL
    SELECT 456 AS NEW_CHIP_ID, 'i' AS NEW_STATUS
    UNION ALL
    SELECT 789 AS NEW_CHIP_ID, 'w' AS NEW_STATUS
) AS new_data
WHERE NOT EXISTS (
    SELECT 1
    FROM chip_status cs
    WHERE cs.CHIP_ID = new_data.NEW_CHIP_ID
    ORDER BY cs.MOD_STATUS_DATE_TIME DESC
    LIMIT 1
    HAVING cs.STATUS = new_data.NEW_STATUS
);

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