mysql 关联id删除

基础概念

MySQL中的关联删除（也称为级联删除）是指当删除一个表中的记录时，同时删除与之关联的其他表中的记录。这种操作通常用于维护数据的一致性和完整性。

类型

级联删除：删除主表记录时，自动删除所有关联表中的记录。
设置为空：删除主表记录时，将关联表中的外键字段设置为空。
限制删除：删除主表记录时，如果关联表中有记录，则禁止删除。

应用场景

假设我们有两个表：users 和 orders，其中 orders 表有一个外键 user_id 关联到 users 表的 id 字段。如果我们删除一个用户，通常希望同时删除该用户的所有订单。

示例代码

-- 创建 users 表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

-- 创建 orders 表，并设置外键约束
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 插入示例数据
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO orders (user_id, amount) VALUES (1, 100.00), (1, 200.00), (2, 150.00);

-- 删除用户 Alice 及其所有订单
DELETE FROM users WHERE id = 1;

遇到的问题及解决方法

问题：为什么删除主表记录时，关联表中的记录没有被删除？

原因：

外键约束未设置级联删除：如果没有在创建外键时指定 ON DELETE CASCADE，则删除主表记录时不会自动删除关联表中的记录。
外键约束未启用：即使设置了级联删除，如果外键约束未启用，也不会生效。

解决方法：

确保在创建外键时指定了 ON DELETE CASCADE。
确保外键约束已启用。

-- 确保外键约束已启用
ALTER TABLE orders ENABLE KEYS;

问题：删除主表记录时，关联表中的记录被删除，但希望保留某些记录怎么办？

解决方法：可以使用 ON DELETE SET NULL 或 ON DELETE SET DEFAULT，而不是 ON DELETE CASCADE。

-- 使用 ON DELETE SET NULL
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);