INSERT 〜 SELECTで一括INSERTするときの注意点

2011-11-03

SELECT結果を別テーブルにINSERTしたい時があります。そんな時は INSERT (IGNORE) SELECT文を使うのですが、master/slaveでレプリケーションしている環境にて注意すべき点があります。

備忘録、というか事故ったので、戒めとして。

環境は以下の通り:

MySQL: 5.0.51a

例えば以下の2つのテーブルがあるとして、

CREATE TABLE `members` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `member_logs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `year` int(10) unsigned NOT NULL,
  `month` int(10) unsigned NOT NULL,
  `member_id` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

今月のmember_logsレコードをmemberレコード分だけINSERTしたいときは、以下のようにします。

INSERT IGNORE INTO member_logs (year, month, member_id)
SELECT
    YEAR(NOW()),
    MONTH(NOW()),
    id
FROM
     members

サービスがmasterDBだけであればこれでいいのですが、複数台レプリケーションしている場合は不整合が起きる可能性があります。
サブクエリのSELECTのORDERが指定されていないため、masterでの実行結果とslave側での結果が必ずしも一致しません。よって、

INSERT IGNORE INTO member_logs (year, month, member_id)
SELECT
    YEAR(NOW()),
    MONTH(NOW()),
    id
FROM
     members
ORDER BY
     id ASC

のように明示的にORDER BYしましょう。

普通にSELECT文だけ実行するとPRIMARY KEYの昇順で返ってくるので早とちりしてました。

INSERT 〜 SELECTで一括INSERTするときの注意点

参考