adiary開発日誌

adiaryではすべてのテーブルに pkey というシリアル値（PRIMARY KEY）を設定しています。その扱いについて。

特にMySQLで特別な加工をせず、安全にシリアル値を取得する方法。

PostgreSQLの場合

PostgreSQLではそのままserial型というものがあり、

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

とすることで、pkeyをプライマリキーとして自動的に生成することができます。

PostgreSQLではシーケンス操作関数というものがあり、SERIAL型を定義すると自動的に作成されます。

例えば、現在の値を取得したければ

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

とします。pkeyに値を設定して INSERT したとき、シーケンス関数は自動的に再定義されないため次の関数で再設定する必要があります。

SELECT setval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

現在の値（最後に生成された値）を取得するには次のようにします。

SELECT currval(pg_catalog.pg_get_serial_sequence('test', 'pkey'), (SELECT max(pkey) FROM test))

同じセッション内でテーブル問わず最後に生成された値を取得する場合は次で済みます。

SELECT lastval()

安全に（他のセッションともかぶらない唯一無二な）次のシリアル値を取得するには、次のようにします。

SELECT nextval(pg_catalog.pg_get_serial_sequence('test', 'pkey'))

MySQLの場合

MySQLにも5.1以降（それより前から？）SERIAL型があります。

CREATE TABLE test(pkey SERIAL PRIMARY KEY, x INT);
INSERT INTO test(x) VALUES(10);

これによりpkeyに自動的にシリアル値を設定することができます。

• SERIALは「BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE」のエイリアスです。
• AUTO_INCREMENTは１つのテーブルに１カラムしか指定できない。
• AUTO_INCREMENTを指定したカラムに、例えばpkeyを指定してINSERTしても、AUTO_INCREMENTがよきにはからってくれる（PostgreSQLのように値を再設定する必要なし）。

現在の値（最後に生成された値）を取得するには次のようにします。

SELECT LAST_INSERT_ID();

ただしC API等では mysql_insert_id()というものがあるため、こちらを使用するほうが効率が良いようです。例えばPerl DBIならば $sth->{mysql_insertid}。

追記。

SELECT * FROM test WHERE pkey IS NULL;

以前はこの方法でも取得が可能でしたが、いつの間にか使えなくなっているようなので注意しましょう。

値を取得、値の変更

SHOW TABLE STATUS WHERE NAME = 'test';

で得られたテーブル情報の中からAuto_incrementカラムの値を参照します。得られるのは次に挿入される値です。*1

何かの都合で値を設定するときは次のようにします。

ALTER TABLE test AUTO_INCREMENT=1;

安全に次の値を取得

MySQLでもっとも厄介なのはこれです。auto_incrementには安全に次の値を得る方法が提供されていません。別にシーケンステーブルを作る方法などがありますが、面倒です。

INSERTでしか得られないならINSERTしてしまえばいいやという単純な方法です。

INSERT INTO test() VALUES();
DELETE FROM test WHERE pkey=LAST_INSERT_ID();
SELECT LAST_INSERT_ID();

NOT NULL制約等が付いているとINSERTが失敗するので、CREATE TABLE等であらかじめ制約を満たすDEFAULT値を設定しておく必要があります。MyISAMではトランザクションが使えないため、NOT NULLかつUNIQUE制約が付いていると、複数のプロセスが同時に"INSERT INTO test() VALUES();"を実行したとき一方が失敗します。

MySQLの場合はINSERTしてUPDATEした方がスマートなのはたしかなのですが、PostgreSQLではシーケンス型の次の値を安全に取得できるし、INSERTに成功してUPDATEに失敗した場合を考えるとややこしいので。