postgreSQL联表删除与联表更新

需求(不能使用外键约束)

一张网关表，一张子设备表，一张设备日志表，一个网关下有多个子设备，一个子设备可以上报多条日志。

  • 网关表与子设备表依靠网关的mac进行关联。

  • 日志表保存子设备表的id字段，依靠子设备id进行关联。

  • 现需要物理删除或者逻辑删除(更新)网关，要求网关下的子设备和子设备日志一起被删除。

    表结构如下所示

    image-20220817110549192

分析

  1. 删除网关以及子设备很简单，按网关id查出网关，然后通过mac删除子设备。
  2. 删除子设备日志就有点复杂了，因为网关和子设备没有直接联系。

思路

  • 应用层查询出所有的子设备，然后通过foreach标签配合in进行批量删除，这种方法效率低，不适合数据量大的情况。
  • 网关和子设备日志建立联系，子设备日志冗余网关的mac字段，通过该字段进行删除，但是有老数据的存在，处理起来比较麻烦，这里可以使用级联更新，对老数据插入网关mac，使用这种方式需要注意老数据的数据量。
  • 先使用联表删除，删除子设备日志表，再删除子设备表，最后删除网关设备。

下面主要演示联表更新、联表删除

联表更新

对设备老数据进行处理

image-20220817114816594

现在进行联表更新，SELECT子句可以跟where，跟放在外部进行where效率相差不大

UPDATE "sub_device_log" A 
SET gateway_mac = b.gateway_mac 
FROM
	( SELECT ID, gateway_mac FROM sub_device ) b 
WHERE
	A.sub_device_id = b.ID

image-20220817115550904

更新成功

image-20220817115234802

联表删除

DELETE 
FROM
	demo.sub_device_log A USING demo.sub_device b 
WHERE
	A.sub_device_id = b.ID

image-20220817120157989