mysql - 如果 2 个表(例如 B 和 C)中的一个存在值，我如何使用表 A 中的值插入或更新数据？

Question

我有 3 个表:

switches     (device_id char(20), room_id char(36), ...)
sensors      (device_id char(20), room_id char(36), ...)
device_rssi  (device_addr char(16), room_id char(36), rssi, ...)

这是我想做的:

If addr already exists in device_rssi
then 
    update the rssi in the device_rssi table
else
    if addr exists in either switches or sensors tables
        get the room_id for that addr from whichever table it exists in
        add the rssi along with the room_id in the device_rssi table

(注意device_id是'device_addr + 4 chars')

这是我当前的查询:

SELECT *
FROM
    (
    SELECT room_id
    FROM switches
    WHERE device_id like addr____
    UNION
    SELECT room_id
    FROM sensors
    WHERE device_id like addr____
    );

然后在我的 C 代码中，我检查查询是否返回了一个 room_id 后跟这个查询:

INSERT INTO device_rssi
(device_addr, room_id, rssi) VALUES (addr, room_id, rssi)
ON DUPLICATE KEY
UPDATE room_id = room_id, rssi = rssi;

首先，我希望有一个“设备”表，其中包含开关和传感器，它们都具有 device_id、room_id 字段的外键引用。那已经解决了我的“检查任一表中是否存在 device_addr 并获取 room_id”的问题。但我不允许这样做，因为这会给应用程序/网络服务人员增加太多工作量。所以，我必须利用现有的资源。

其次，我讨厌每次更新 rssi 时都要查询 room_id，即使开关/传感器的 room_id 很少会改变。

当然，更不用说本应非常简单的事情了，我提出了 2 个查询，其中 1 个查询具有 2 个子查询的并集。啊!

我相信即使不修改模式也必须有更好的方法来做到这一点，但我似乎找不到它。任何帮助将不胜感激!谢谢!

(顺便说一句，我是一个没有数据库经验的嵌入式人员，所以如果这是一个愚蠢的问题或者我公然错过了一个愚蠢的简单解决方案，请原谅我。)

Answer 1

我在没有测试的情况下编写了下面的查询，因此可能存在一些错误。请将“ADDR”和“RSSI”替换为其正确的值(已编码)或使用查询参数以避免 SQL 注入(inject)。

INSERT INTO device_rssi(device_addr, room_id, rssi) 
    select * from (
      select 'ADDR', room_id, 'RSSI' from device_rssi where device_addr='ADDR' 
      union
      select 'ADDR', room_id, 'RSSI' from switches where device_id like 'ADDR____' 
      union
      select 'ADDR', room_id, 'RSSI' from sensors where device_id like 'ADDR____' 
    ) tmp limit 1
ON DUPLICATE KEY
    UPDATE rssi = 'RSSI';

这并不理想，尤其是因为 device_addr 与 device_id 不同，并且因为您可以在传感器和开关中拥有相同的 device_id 记录。