MySQL - ON DELETE CASCADE

MySQL: 理解 ON DELETE CASCADE

ON DELETE CASCADE 的作用

ON DELETE CASCADE 约束是 MySQL 等关系型数据库中外键的一个特性。它建立了一个规则，可以自动维护父表和子表之间的数据一致性，即参照完整性。

当您定义外键时附带 ON DELETE CASCADE，从父表中删除一条记录将自动触发子表中所有相应记录的删除。如果没有此约束，数据库服务器将默认（ON DELETE RESTRICT）阻止您删除被任何子记录引用的父记录，强制您必须先手动删除子记录。

实战示例

让我们为用户及其博客文章建模一个简单系统。一个用户可以拥有多篇文章，形成一对多关系。如果一个用户被删除，我们也希望其所有文章都被删除。

步骤 1：创建父表

首先，我们创建 users 表。最佳实践是使用 InnoDB 存储引擎，这是现代 MySQL 的默认引擎，支持外键约束。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

现在，让我们插入一些示例用户：

INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');

users 表现在包含：

user_id	username	email	created_at
1	alice	alice@example.com	…
2	bob	bob@example.com	…
3	charlie	charlie@example.com	…

步骤 2：创建带有 ON DELETE CASCADE 的子表

接下来，我们创建 posts 表。author_id 列将作为外键引用 users 表中的 user_id。这里，我们指定了 ON DELETE CASCADE。

CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    published_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

让我们为用户添加一些帖子：

INSERT INTO posts (author_id, title, content) VALUES
(1, 'My First Post', 'Hello world!'),
(2, 'Databases are Fun', 'A post about SQL.'),
(2, 'Advanced SQL', 'Exploring window functions.'),
(3, 'Web Development Trends', 'A look at modern frameworks.');

posts 表现在看起来像这样：

post_id	author_id	title	content	published_at
1	1	My First Post	Hello world!	…
2	2	Databases are Fun	A post about SQL.	…
3	2	Advanced SQL	Exploring window functions.	…
4	3	Web Development Trends	A look at modern frameworks.	…

步骤 3：从父表中删除记录

现在，让我们看看 ON DELETE CASCADE 约束的实际应用。我们将删除用户 ‘bob’，其 user_id = 2。Bob 创作了两篇帖子。

DELETE FROM users WHERE user_id = 2;

查询成功执行。让我们验证两个表的内容。

查询 users 表：

SELECT * FROM users;

正如所料，user_id 为 2 的用户已不存在：

user_id	username	email	created_at
1	alice	alice@example.com	…
3	charlie	charlie@example.com	…

查询 posts 表：

SELECT * FROM posts;

结果

级联删除完美地生效了。由 ‘bob’ 创作的两篇帖子（author_id = 2）已自动从 posts 表中删除，从而维护了参照完整性。

post_id	author_id	title	content	published_at
1	1	My First Post	Hello world!	…
4	3	Web Development Trends	A look at modern frameworks.	…

关键考量与最佳实践

• 谨慎使用： ON DELETE CASCADE 功能强大，但如果使用不当，可能导致意外的大量数据丢失。请确保仅将其应用于子实体不能独立于父实体而存在的关系（例如，没有用户就没有帖子）。
• 替代方案： 对于其他场景，可以考虑 ON DELETE SET NULL（将子表中的外键设置为 NULL）或 ON DELETE RESTRICT（默认值，它会阻止删除）。选择哪种方案完全取决于您的业务逻辑。
• 性能： 在非常大的表上进行级联删除可能会消耗大量资源，并可能长时间锁定表。请务必在测试环境中测试性能。
• 引擎支持： 此功能主要由 InnoDB 存储引擎支持，它是需要关系完整性和事务的现代应用程序的标准选择。