Oracle/MySQL数据库重复Mac地址的计数增减逻辑实现需求

针对你描述的需求——当插入一条包含重复Mac地址的新记录时，先将原有相同Mac地址记录的total_Mac_Online_Count减1，再插入新记录（total_Mac_Online_Count设为1），我分别给出Oracle和MySQL的实现方案，确保操作的原子性（要么都成功，要么都回滚）。

Oracle 实现方案 #

你可以选择用PL/SQL块封装逻辑，或者直接在事务中执行两条SQL语句：

方法1：PL/SQL块（带事务控制） #

适合需要复用或添加异常处理的场景：

DECLARE
    v_state NUMBER := 26;
    v_rpd VARCHAR2(2) := 'AB';
    v_mac VARCHAR2(17) := 'aa:bb:12:cc:ab:ac';
BEGIN
    -- 第一步：更新原有相同Mac地址的记录，将在线数减1
    UPDATE your_table_name
    SET total_Mac_Online_Count = total_Mac_Online_Count - 1
    WHERE Mac_address = v_mac;

    -- 第二步：插入新的记录，在线数设为1
    INSERT INTO your_table_name (State, RPD, Mac_address, total_Mac_Online_Count)
    VALUES (v_state, v_rpd, v_mac, 1);

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 抛出异常便于排查问题
END;
/

方法2：单事务执行两条SQL #

如果只是单次执行，直接在事务中运行以下语句即可：

-- 先更新原有记录
UPDATE your_table_name
SET total_Mac_Online_Count = total_Mac_Online_Count - 1
WHERE Mac_address = 'aa:bb:12:cc:ab:ac';

-- 再插入新记录
INSERT INTO your_table_name (State, RPD, Mac_address, total_Mac_Online_Count)
VALUES (26, 'AB', 'aa:bb:12:cc:ab:ac', 1);

COMMIT;

MySQL 实现方案 #

MySQL同样通过事务包裹操作来保证原子性，以下是两种常用实现方式：

方法1：直接使用事务 #

START TRANSACTION;

-- 更新原有相同Mac地址的记录
UPDATE your_table_name
SET total_Mac_Online_Count = total_Mac_Online_Count - 1
WHERE Mac_address = 'aa:bb:12:cc:ab:ac';

-- 插入新记录
INSERT INTO your_table_name (State, RPD, Mac_address, total_Mac_Online_Count)
VALUES (26, 'AB', 'aa:bb:12:cc:ab:ac', 1);

COMMIT;

方法2：封装为存储过程（适合重复调用） #

DELIMITER //

CREATE PROCEDURE handle_mac_insert(
    IN p_state INT,
    IN p_rpd VARCHAR(2),
    IN p_mac VARCHAR(17)
)
BEGIN
    START TRANSACTION;

    UPDATE your_table_name
    SET total_Mac_Online_Count = total_Mac_Online_Count - 1
    WHERE Mac_address = p_mac;

    INSERT INTO your_table_name (State, RPD, Mac_address, total_Mac_Online_Count)
    VALUES (p_state, p_rpd, p_mac, 1);

    COMMIT;
END //

DELIMITER ;

-- 调用存储过程执行操作
CALL handle_mac_insert(26, 'AB', 'aa:bb:12:cc:ab:ac');

注意事项 #

• 请将your_table_name替换为你实际使用的表名
• 如果需要避免total_Mac_Online_Count变为负数，可以在UPDATE语句中添加AND total_Mac_Online_Count > 0条件
• 务必保证更新和插入操作在同一个事务中，防止出现更新成功但插入失败的不一致情况

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