2010-05-18

1ヶ月半ぶりのエントリです。皆さんお元気ですか？
何故か最近 Eclipse ばっかり使ってる zigorou でございます。

12.2.1 DELETE 構文を見ていたら複合 DELETE 構文ってのが有ったので試してみました。

前提としてレコードがうんざりする程多いテーブル、、、と言う背景があります。

解説

とりあえず次のようなテーブルがあるとしましょう。

CREATE TABLE `diary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guid` int(11) NOT NULL,
  `subject` varchar(32) DEFAULT NULL,
  `body` text,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

comment
Create Table: CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `diary_id` int(11) NOT NULL,
  `guid` int(11) NOT NULL,
  `body` varchar(256) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

comment.diary_id には diary.id が入っていて、FOREIGN KEY とかは貼ってない、もっと言うと ON DELETE CASCADE みたいな定義が無いとして、diary.id 値が 1, 3 の diary のレコードと、comment.diary_id が 1, 3 のレコード全部消したい、みたいなシーンがあるとしましょう。

大人しく書けば両方で、

DELETE FROM diary WHERE id IN (1, 3);
DELETE FROM comment WHRRE diary_id IN (1, 3);

みたいな事をやると思うんですが、IN 文の id 値がたくさんある何て場合にこれらの DELETE 句を実行するのはちょっとはばかられますね。

これは実は、複合 DELETE 構文ってのを使うと、

DELETE c, d FROM comment c, diary d WHERE c.diary_id IN (1, 3) AND d.id = c.diary_id;

と言う風に書くと上記の条件を満たした diary, comment のレコードを1つの SQL で書く事が出来ます。ちょうど、

SELECT c.*, d.* FROM comment c, diary d WHERE c.diary_id IN (1, 3) AND d.id = c.diary_id\G

で取り出される comment, diary 全部が対象になります。親子関係に注意しないとダメです！

サンプルコード

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dump qw(dump);
use DBI qw(neat);
use SQL::Abstract;
use SQL::Abstract::Plugin::InsertMulti;

my @diaries = (
    # [ guid, subject, body, created_on, updated_on ]
    [ 100, 'aaa', 'aaaaaa', \'NOW()', \'NOW()' ],
    [ 101, 'bbb', 'bbbbbb', \'NOW()', \'NOW()' ],
    [ 102, 'ccc', 'cccccc', \'NOW()', \'NOW()' ],
    [ 102, 'ddd', 'dddddd', \'NOW()', \'NOW()' ],
);

my @comments = (
    # [ diary_id, guid, body, created_on, updated_on ],
    [ 1, 100, 'aaa', \'NOW()', \'NOW()', ],
    [ 1, 101, 'bbb', \'NOW()', \'NOW()', ],
    [ 2, 102, 'ccc', \'NOW()', \'NOW()', ],
    [ 2, 103, 'ddd', \'NOW()', \'NOW()', ],
    [ 3, 100, 'eee', \'NOW()', \'NOW()', ],
    [ 3, 101, 'fff', \'NOW()', \'NOW()', ],
    [ 4, 102, 'ggg', \'NOW()', \'NOW()', ],
    [ 4, 103, 'hhh', \'NOW()', \'NOW()', ],
);

my $dbh = DBI->connect( 'dbi:mysql:dbname=test', 'root', '', +{ AutoCommit => 0, RaiseError => 1, ShowErrorStatement => 1 } );

$dbh->do( 'ALTER TABLE diary auto_increment = 0' );
$dbh->do( 'ALTER TABLE comment auto_increment = 0' );
$dbh->do( 'TRUNCATE TABLE diary' );
$dbh->do( 'TRUNCATE TABLE comment' );

my ( $stmt, @bind );
my $sql = SQL::Abstract->new;
my $rv;

( $stmt, @bind ) = $sql->insert_multi(
    'diary',
    [qw/guid subject body created_on updated_on/],
    \@diaries,
);

$rv = $dbh->do( $stmt, undef, @bind );
printf( "affected rows %s at diary table\n", neat($rv) );

( $stmt, @bind ) = $sql->insert_multi(
    'comment',
    [qw/diary_id guid body created_on updated_on/],
    \@comments,
);

$rv = $dbh->do( $stmt, undef, @bind );
printf( "affected rows %s at comment table\n", neat($rv) );

$dbh->commit;

$dbh->do( 'CREATE TEMPORARY TABLE IF NOT EXISTS diary_delete ( id INT PRIMARY KEY NOT NULL ) ENGINE=Memory' );
$dbh->do( 'INSERT INTO diary_delete (id) VALUES (?), (?)', undef, 1, 3 );
$dbh->do( 'DELETE c, d FROM comment c, diary d, diary_delete dd WHERE c.diary_id = dd.id AND d.id = c.diary_id' );

print dump( $dbh->selectall_arrayref( 'SELECT * FROM diary', +{ Slice => +{} } ) );
print dump( $dbh->selectall_arrayref( 'SELECT * FROM comment', +{ Slice => +{} } ) );

$dbh->disconnect;

で試しに実行結果です。

affected rows 4 at diary table
affected rows 8 at comment table
[
  {
    body => "bbbbbb",
    created_on => "2010-05-18 15:50:31",
    guid => 101,
    id => 2,
    subject => "bbb",
    updated_on => "2010-05-18 15:50:31",
  },
  {
    body => "dddddd",
    created_on => "2010-05-18 15:50:31",
    guid => 102,
    id => 4,
    subject => "ddd",
    updated_on => "2010-05-18 15:50:31",
  },
][
  {
    body => "ccc",
    created_on => "2010-05-18 15:50:31",
    diary_id => 2,
    guid => 102,
    id => 3,
    updated_on => "2010-05-18 15:50:31",
  },
  {
    body => "ddd",
    created_on => "2010-05-18 15:50:31",
    diary_id => 2,
    guid => 103,
    id => 4,
    updated_on => "2010-05-18 15:50:31",
  },
  {
    body => "ggg",
    created_on => "2010-05-18 15:50:31",
    diary_id => 4,
    guid => 102,
    id => 7,
    updated_on => "2010-05-18 15:50:31",
  },
  {
    body => "hhh",
    created_on => "2010-05-18 15:50:31",
    diary_id => 4,
    guid => 103,
    id => 8,
    updated_on => "2010-05-18 15:50:31",
  },
]

実際に実行してみると、comment.diary_id が 1, 3 の comment のレコード及び diary.id が 1, 3 の diary のレコードが一括で削除されているのが分かるかと思います。

まとめ

そんな大量のデータを扱う必要の無い人はまったく要らない知識だと思います。

削除候補の id を一旦 temporary table に格納しておいてそれと join しつつ一括で複数のテーブルの条件に一致するレコードを削除する、、、なんてのに使えそうだなーと。
例えば削除候補が 100 万件とかあったら、、、って考えると temporary table にちまちま入れつつ繰り返し DELETE LOW_PRIORITY とかでやるとかは使えそうだなと。

ただ先ほども述べましたが親子関係に注意しないと思わぬレコードを消してしまって再帰不能なんて事もあると思うのでよくよく注意して使った方が良さそうです。

日向夏特殊応援部隊

俺様向けメモ

MySQL の複合 DELETE 構文

解説

サンプルコード

まとめ