MySQL-用一个查询从多个表中删除 [英] Mysql - delete from multiple tables with one query

问题描述

我有4个表，每个表中存储着有关用户的不同信息.每个表都有一个带有user_id的字段，用于标识哪个行属于哪个用户.如果要删除用户，这是从多个表中删除该用户信息的最佳方法吗?我的目标是在一个查询中做到这一点.

I have 4 tables that stores different information about a user in each. Each table has a field with user_id to identify which row belongs to which user. If I want to delete the user is this the best way to delete that users information from multiple tables? My objective is to do it in one query.

查询:

"DELETE FROM table1 WHERE user_id='$user_id';
DELETE FROM table2 WHERE user_id='$user_id';
DELETE FROM table3 WHERE user_id='$user_id';
DELETE FROM table4 WHERE user_id='$user_id';";

推荐答案

您可以使用ON DELETE CASCADE选项在表上定义外键约束.

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

然后从父表中删除记录将从子表中删除记录.

Then deleting the record from parent table removes the records from child tables.

检查此链接: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

这篇关于MySQL-用一个查询从多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！