关联表如何删除数据

建表语句

CREATE TABLE `stations` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `cur_id` int NOT NULL COMMENT '本小区ID',
  `cur_longitude` double NOT NULL COMMENT '本小区经度',
  `cur_latitude` double NOT NULL COMMENT '本小区纬度',
  `neighbor_id` int NOT NULL COMMENT '邻小区ID',
  `neighbor_longitude` double NOT NULL COMMENT '邻小区经度',
  `neighbor_latitude` double NOT NULL COMMENT '邻小区纬度',
  `distance` double NOT NULL COMMENT '距离，单位米',
  `file_id` bigint DEFAULT '1' COMMENT '文件id',
  PRIMARY KEY (`id`),
  KEY `sta_file_fk` (`file_id`),
  CONSTRAINT `sta_file_fk` FOREIGN KEY (`file_id`) REFERENCES `files` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6317 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `files` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `file_name` varchar(40) NOT NULL,
  `version` int DEFAULT '1' COMMENT '乐观锁，版本号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

需求描述

删除files表中一周前数据，并将表stations对应的相同file_id数据删除

方法一：使用外键的级联删除功能

如果你在创建 stations 表的外键约束时指定了

ON DELETE CASCADE

选项，那么当 files 表中的记录被删除时，stations 表中关联的记录会自动被删除。
修改 stations 表的外键约束

-- 删除原有的外键约束
ALTER TABLE stations
DROP FOREIGN KEY sta_file_fk;

-- 重新创建外键约束，并添加 ON DELETE CASCADE 选项
ALTER TABLE stations
ADD CONSTRAINT sta_file_fk
FOREIGN KEY (file_id) REFERENCES files(id)
ON DELETE CASCADE;

删除一周前的数据

现在可以安全地删除 files 表中一周前的数据，stations 表中关联的记录会自动被删除：

-- 删除 files 表中一周前的数据
DELETE FROM files
WHERE create_time < CURDATE() - INTERVAL 1 WEEK;

方法二：手动编写 SQL 语句

如果没有使用级联删除功能，可以手动编写 SQL 语句来实现删除操作。

手动删除关联数据

-- 首先删除 stations 表中与要删除的 files 记录关联的数据
DELETE FROM stations
WHERE file_id IN (
    SELECT id
    FROM files
    WHERE create_time < CURDATE() - INTERVAL 1 WEEK
);

-- 然后删除 files 表中一周前的数据
DELETE FROM files
WHERE create_time < CURDATE() - INTERVAL 1 WEEK;

注意事项

事务：使用事务可以确保数据的一致性。如果在删除过程中出现错误，可以使用 ROLLBACK 回滚事务，避免数据不一致。
数据备份：在执行删除操作之前，建议先备份数据，以防误操作导致数据丢失。
性能考虑：如果数据量较大，删除操作可能会比较耗时。可以考虑在业务低峰期执行，或者分批删除数据。