MySQL 插入和更新过程

Question

我需要一些帮助来在 MySQL 中创建过程。

我有两个表:

1. 连接
2. connection_temporary

我想创建一个程序来执行以下操作:

• 如果SELECT keyname FROM connection WHERE keyname NOT IN (SELECT keyname FROM connection_temporary) 将connection.connection_status更新为离线
• 如果SELECT keyname FROM connection_temporary WHERE keyname NOT IN (SELECT keyname FROM CONNECTION) 插入与connection_temporary的行内容相关的内容
• 如果SELECT keyname, real_ip, virtual_ip, returned_bytes, sent_bytes, connection_tstamp FROM connection_temporary WHERE keyname IN (SELECT keyname FROM CONNECTION) 使用connection_temporary的行内容更新连接

我该怎么做？

Answer 1

您应该发布两个表的架构，不过，您可以根据以下内容自行尝试:

DELIMITER $$
CREATE PROCEDURE `proc`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    UPDATE `connection`
    SET `connection`.connection_status = 'offline'
    WHERE NOT EXISTS(
        SELECT 1 
        FROM connection_temporary
        WHERE connection_temporary.keyname = `connection`.keyname
    );

    INSERT INTO `connection` (keyname, real_ip, virtual_ip, received_bytes, sent_bytes, connection_tstamp)
    SELECT keyname, real_ip, virtual_ip, received_bytes, sent_bytes, connection_tstamp
    FROM connection_temporary
    WHERE NOT EXISTS(
        SELECT 1
        FROM `connection`
        WHERE connection_temporary.keyname = `connection`.keyname
    );

    UPDATE `connection`
    JOIN connection_temporary ON connection_temporary.keyname = `connection`.keyname
    SET `connection`.keyname = connection_temporary.keyname
    ,`connection`.real_ip = connection_temporary.real_ip
    ,`connection`.virtual_ip = connection_temporary.virtual_ip
    ,`connection`.received_bytes = connection_temporary.received_bytes
    ,`connection`.sent_bytes = connection_temporary.sent_bytes
    ,`connection`.connection_tstamp = connection_temporary.connection_tstamp; 
END
$$ DELIMITER ;