如何在mysql中删除四个表中的记录？

Question

mysql> desc courses;
+--------------------+---------------------------+------+-----+---------+----------------+
| Field              | Type                      | Null | Key | Default | Extra          |
+--------------------+---------------------------+------+-----+---------+----------------+
| course_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| course_name        | varchar(50)               | NO   | UNI | NULL    |                |
| course_description | text                      | NO   |     | NULL    |                |
| course_added       | datetime                  | NO   |     | NULL    |                |
| status             | enum('Active','Inactive') | NO   |     | Active  |                |
+--------------------+---------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> desc papers;
+-------------------+---------------------------+------+-----+---------+----------------+
| Field             | Type                      | Null | Key | Default | Extra          |
+-------------------+---------------------------+------+-----+---------+----------------+
| paper_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| course_id         | int(11)                   | NO   |     | NULL    |                |
| paper_name        | varchar(50)               | NO   |     | NULL    |                |
| paper_description | text                      | NO   |     | NULL    |                |
| paper_added       | datetime                  | NO   |     | NULL    |                |
| status            | enum('Active','Inactive') | NO   |     | Active  |                |
+-------------------+---------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc question_sets;
+-----------------+---------------------------+------+-----+---------+----------------+
| Field           | Type                      | Null | Key | Default | Extra          |
+-----------------+---------------------------+------+-----+---------+----------------+
| set_id          | int(11)                   | NO   | PRI | NULL    | auto_increment |
| paper_id        | int(11)                   | NO   |     | NULL    |                |
| set_name        | varchar(100)              | NO   |     | NULL    |                |
| set_description | text                      | NO   |     | NULL    |                |
| set_maxtime     | time                      | NO   |     | NULL    |                |
| created_date    | datetime                  | NO   |     | NULL    |                |
| status          | enum('Active','Inactive') | NO   |     | Active  |                |
+-----------------+---------------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> desc questions;
+---------------+---------------------------------------------------+------+-----+---------+----------------+
| Field         | Type                                              | Null | Key | Default | Extra          |
+---------------+---------------------------------------------------+------+-----+---------+----------------+
| question_id   | int(11)                                           | NO   | PRI | NULL    | auto_increment |
| set_id        | int(11)                                           | NO   |     | NULL    |                |
| question_text | varchar(50)                                       | NO   |     | NULL    |                |
| option_1      | varchar(50)                                       | NO   |     | NULL    |                |
| option_2      | varchar(50)                                       | NO   |     | NULL    |                |
| option_3      | varchar(50)                                       | NO   |     | NULL    |                |
| option_4      | varchar(50)                                       | NO   |     | NULL    |                |
| answer        | enum('option_1','option_2','option_3','option_4') | NO   |     | NULL    |                |
| status        | enum('Active','Inactive')                         | NO   |     | Active  |                |
+---------------+---------------------------------------------------+------+-----+---------+----------------+

这是四个表的表课程是连接的论文表论文连接到question_sets和question_set是连接到问题如果我删除表“课程”中的course_id=1，行对应于表论文中的course_id应该被删除，行对应于表中的paper_id表question_sets应该被删除，行对应于表中的question_id问题应该得到删除我已经厌倦了以下查询它删除从论文表中的记录，是否可以执行删除操作，在所有的表单查询？

delete from papers where course_id IN (select course_id from courses where course_id=7); 

注意:上面的查询删除了纸质表格中的行

Answer 1

为什么要在一个查询中从四个表中删除？四条delete语句有什么问题？如果您需要确保所有删除操作都是自动进行的，那么将这四个删除操作包装在一个事务中。

Answer 2

DELETE FROM courses, papers, question_sets , questions
USING courses JOIN papers JOIN question_sets JOIN questions
WHERE courses.course_id= 1
    AND papers.course_id= 1
    AND question_sets.paper_id=1
    AND questions.set_id=1

Answer 3

您可以使用以下查询禁用外键检查，然后尝试删除或删除表

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists courses;
drop table if exists papers;
drop table if exists question_sets;
SET FOREIGN_KEY_CHECKS = 1;