MySQLのトリガでシーケンスでの自動採番を実現する

同じデータベースの違うテーブルでプライマリキーを重複させたくない場合や、
クラスタ化した場合とかで AUTO_INCREMENT が使えない場合。

いわゆる採番テーブルってやつ。

DBICとかで

sub nextval {
    my $self = shift;
    my $dbh  = $self->result_source->storage->dbh;
    my $sql  = 'UPDATE sequence SET id = LAST_INSERT_ID(id + 1)';
    my $sth  = $dbh->prepare($sql);
    $sth->execute;
    my $id = $sth->{mysql_insertid};
    $sth->finish;
    $id;
}

とか手動でやってもよかったんだけど、せっかくなのでMySQLのトリガーでやってみた。

DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (id INT UNSIGNED NOT NULL);
INSERT INTO  sequence VALUES (100000);
DROP TABLE IF EXISTS hoge;
CREATE TABLE hoge (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    content TEXT
);

DROP TRIGGER IF EXISTS hoge_set_id;
delimiter //
CREATE TRIGGER hoge_set_id BEFORE INSERT ON hoge
FOR EACH ROW
BEGIN
    UPDATE sequence SET id = LAST_INSERT_ID(id + 1);
    SET NEW.id = LAST_INSERT_ID();
END;
//
delimiter ;

とこんな感じで作って、

(root@localhost) [test]> INSERT INTO hoge (content) VALUES ('hoge');
Query OK, 1 row affected, 1 warning (0.01 sec)

(root@localhost) [test]> SELECT * FROM hoge;
+--------+---------+
| id     | content |
+--------+---------+
| 100001 | hoge    |
+--------+---------+
1 rows in set (0.01 sec)

(root@localhost) [test]>

いけた。が、なんか warning でてる。

(root@localhost) [test]> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

まぁそうだけどさ・・・BEFOREトリガ呼んだあとに確認してくれないものか。

ダミーデータを入れたら当然出なくなるが、あまり気持ちのいいものではない。

(root@localhost) [test]> INSERT INTO hoge (id, content) VALUES (0, 'hoge');
Query OK, 1 row affected (0.01 sec)

sql/sql_insert.cc を軽く追ったところ、やはりBEFORE INSERT のトリガ呼ぶ前にエラーメッセージをセットしてるっぽい。

まぁ気にしないことにした。

意外とこの方法は使えるかも。割とお手軽だし、アプリケーションに依存しない。

サーバが複数にまたがってる場合は FEDERATED を使えばいけるんじゃないか(試してないけど)。

関係ないが、Data::YUIDはそろそろ使ってみたい。