Delete from multiple tables in one statement
P粉716228245
2023-08-18 10:28:49
<p>Using MySQL, I am trying to delete multiple records from multiple tables at once.
Initially I thought I could do this: </p>
<pre class="brush:php;toolbar:false;">DELETE t1, t2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t1.id = t3.table1_id
WHERE t1.id IN (?,?,?,?);</pre>
<p>However, if there are no existing records in table2, should I change <strong>JOIN</strong> to <strong>LEFT JOIN</strong>? Also, if I delete only two or three records from the eight tables (2x2x2x2x2x2x2x2), will this cause a delay? </p>
Yes, changing the join on
table2
toleft join
will achieve the effect you want. Rows intable1
that belong to the list andtable3
will be deleted, regardless of whether they also exist intable2
. At the same time, possible matching lines will also be deleted.I recommend rewriting the
join
ontable3
to theexists
condition. This makes the intent of the query clearer and may perform better, especially if there is an index ontable3(table1_id)
: