DELETE tb1,tb2,tb3 FROM tb1 LEFT JOIN tb1.id=tb2.id LEFT JOIN tb1.id=tb3.id WHERE tb1.id='1'
とすれば可能なのですが、削除したいテーブルが増えた場合、またはテーブル内の対象レコードが多い場合、LEFTJOINで結合していると、かなり負荷がかかって削除出来ません。(インデックスを設定していても)
ちなみにLEFT JOINにしているのは、各テーブルに対象IDが存在しない場合があるからです。
GROPが使えればと思ったのですが、駄目でした。
何か良い方法がありましたら、ご意見いただければと思います。
※MySQLは4.1.22を利用しています。
※1テーブル毎にIDの存在を確認して削除する方法以外で教えて下さい。
http://sqltips.blog40.fc2.com/blog-entry-6.html
http://0-9.sakura.ne.jp/blog/archives/2008/07/10013846.html
JOINは使用してよいので、とりあえず上記URLのようにテーブル毎に削除するようにしたら良いです。
表を構成する列と、表と表の関連を示してもらえれば、より具体的なアドバイスができると思うのですけどね。
参照整合性制約、外部キー制約を使ってみてはいかがでしょうか?
整合性が崩れる操作を制約する方法と、整合性が崩れないように子も操作する方法があります。
なお、MySQLでは、InnoDBのみ、対象にできます。
MySQL :: MySQL 4.1 リファレンスマニュアル :: 6.5.3 CREATE TABLE 構文
1.制約
(1)親の削除時、子がいると削除できない
(2)親の主キー更新時、子がいると更新できない
(3)子を追加する場合、外部キーに対応する主キーを持つ親がいないと、追加できない。
(4)子の外部キー更新時、更新後の値と同じ主キーを持つ親がいないと更新できない
2.動作
(1)親の削除時、子も削除
(2)親の主キー更新時、子の外部キーも更新
create table tbl1 (pkey int primary key, data1 varchar(10)) engine=innodb ; create table tbl2 (fkey int, data2 varchar(10), foreign key(fkey) references tbl1(pkey), index(fkey)) engine=innodb ;
詳しく回答していただいてありがたいのですが、innodbではなく、MyISMを使っていて、それを変更する気はありません。質問に書かずにすみませんでした。
実際に試してみました
http://modperlis.bounceme.net/2008/11/hatenaquestion.html
EXPLAIN SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.id LEFT JOIN tb3 ON tb1.id = tb3.id WHERE tb1.id = '6';
などの結果を教えていただけると助かります。
※idというカラムを見ると、PRIMARYと思ってしまうので・・・
質問の仕方が悪かった(と言うか誤り)がありました。
ブログにまで書いていただいたのに、申し訳ありませんでした。結合しているIDはPRIMARY ではないです。
質問文のソースを短くしようと、単に「id」としたのですが、それが回答者様を惑わす原因になったようです。
お手数おかけして申し訳ありませんでした。
#2回答者です。
いくつか確認事項があります。
1.
>テーブル内の対象レコードが多い場合、LEFTJOINで結合していると、かなり負荷がかかって削除
>出来ません。(インデックスを設定していても)
対象レコード数が多いとのことですが、母体件数がどのくらいあり、その内、何件くらいを対象にしているのでしょうか?
LEFT JOINに関しては、インデクスが有効利用できているのでしょうか?
DELETEでなく、SELECTなら、それなりに性能は確保されているのでしょうか?
2.
>innodbではなく、MyISMを使っていて、それを変更する気はありません
これは、レンタルサーバ等の制限があるといった理由でしょうか?
せっかく具体的な提案をしても、「そういった対処をする気がない」、「そういった対処はできない」と返事されたのでは、どういう提案をすればいいのか判断できなくなります。
3.
結合キーにしているID列は、通番のようなデータでしょうか?
auto_incrementで生成しているような、
(1)一意である値を自動生成できることに意味がある
(2)値の内容自体に意味はない
といったデータでしょうか?
もし、そうであれば、DELETEするのでなく、「削除を示す」付加情報、つまり「削除フラグ」を付け、DELETEするのでなく、UPDATEで擬似削除する方法は考えられないのでしょうか?
通常、DELETEに比べ、UPDATEは負荷は軽いです。
4.
実際の表やインデクスの定義。表と表の関係。
(1)表とその構成列、そのデータ型・・・特にTEXT系やVARCHAR系の有無や定義長
(2)どういうインデクス定義にしているか
(a)PRIMARY KEY
(b)UNIQUE・・・表定義でのUNIQUE指定またはCREATE UNIQUE INDEX
(c)INDEX・・・表定義でのINDEX指定またはCREATE INDEX
5.
>ちなみにLEFT JOINにしているのは、各テーブルに対象IDが存在しない場合があるからです。
親に対応する子でないものを、本来、DELETEの対象にすべきではないのではないでしょうか?
最終的に「WHERE tb1.id='1'」で絞り込んでいるので、これはINNER JOINと同じです。
1:
母体件数は1万件ぐらいで、実際に対象となるキーは100件ぐらいだと思うのですが、LEFT JOINでいくつもテーブルを繋げていくと、サーバがダウンするので、物凄い数が結合されているのでしょう。
ちなみにSELECTなら一瞬です。GROUP BYで結合する対象のIDを指定してるというのも大きいと思います。
2:
書き方が悪かったら申し訳ありません。ただ、今あるテーブルはすべてMyISAMです。それをinnodbに変えるのは躊躇します。トランザクションなどが使えるからいいとは思うのですが、データ表示・検索の際にパフォーマンスが落ちるので。ですので、極端な話、innodbじゃないと私の質問した事が出来ないのなら、諦めようと思います。
3:
auto_incrementではありません。質問ではわかりやすく「id」として書きましたが、逆にわかりづらかったかもしれませんね。
利用用途で言えば、利用者IDとかカテゴリIDとかその類です。auto_incrementのIDとはまた別です。
4:
3でも書きましたが、おそらく私の質問の書き方が悪かったのだと思います。それは大変申し訳ありませんでした。
テーブルの構成は
id|user_id|body がフィールド
idがauto_incrementであり、PRIMARY KEY
user_idがINDEX
このような構成のテーブルでテストしていました。
5:
これも質問文の問題ですね。すみません。
例えば
SELECT * FROM tb1 LEFT JOIN tb1.user_id=tb2.user_id LEFT JOIN tb1.user_id=tb3.user_id WHERE tb1.user_id='1'
とした場合の結果をご想像いただければLEFT JOINを使っている意味もおわかりいただけるのではないでしょうか。
質問にも書きましたが、LEFT JOINの数が増えると負荷がかかるので、他の方法を模索しています。(おっしゃっているのは、INNER JOINを使う方法ではありませんか?)