MySQL Archive

MySQLのTimestampを勘違いしていました。
（そしてkazeburoさん素早い突っ込みありがとう（ `･ω･)） 

OracleのTimestampとは全然違って、そういう場合はDateTime型を使うのが正しいらしい。Timestampのサイズが妙に小さいので気に入っていたのですが(´・ω・)　で、何が違うのかと言うと、

• データ追加時、または更新時、現在日時を自動で設定する。
• データ更新時で更新前後で値の変化がない場合、TIMESTAMP 型の項目も更新しない。
• NULL 値を設定した場合、現在日時が設定される。

(引用元)http://minazuki.cocolog-nifty.com/kaimemo/2008/01/mysql_timestamp_6d3b.html

というわけで、基本的にはレコードの更新時間が入るみたいです。

危ない危ない。
この前の内製アプリにおもいっきりTimestamp使っていました(；´Д｀)

他にも色々勘違いしているものがありそうだ(((( ;ﾟдﾟ)))

GeneratedKeyを使う方法は以前書きましたが、今回はiBatisを使った場合の方法です。
1回の命令でシーケンスをエミュレートするやりかたを書いておきます。

まず、iBatisは以下の事が言えます。

• Insertの時だけSelectKeyで値を返すことができる（Updateの時は戻り値は更新件数）
• InsertタグでもUpdate文を発行する事は可能
  （JDBCレベルではexecuteUpdateを発行しているため）

これらの事をふまえると、
Insertタグの中にUpdate文を記述して、SelectKeyも併せて書くことでインクリメントした値を返すことができます。

さっそくサンプルを。

シーケンステーブルの準備

CREATE TABLE SEQUENCE (
    NAME VARCHAR(32) NOT NULL,
    SEQ INTEGER NOT NULL,
    CONSTRAINT PK_SEQUENCE PRIMARY KEY(NAME)
) Type=InnoDB;
INSERT INTO SEQUENCE (NAME, SEQ) VALUES('IMGID', 0);

iBatisの設定ファイル

<insert id="GetNextSequence" parameterClass="java.lang.String">
	UPDATE SEQUENCE SET SEQ = LAST_INSERT_ID(SEQ+1) WHERE NAME = #name#
	<selectKey resultClass="int">
		SELECT LAST_INSERT_ID()
	</selectKey>
</insert>

Javaプログラム

int id = (Integer) sqlMap.insert("GetNextSequence", "IMGID");

OracleやPostgreSQLにはSequenceがあるのですが、MySQLにはありません。
AUTO_INCREMENTは便利なのですが、外部キーの参照先となるような場合に困ってしまいます。
具体的には次のような感じです。

テーブル設計や正規化云々の話は面倒なので言及しません。

名簿テーブルのIDがAUTO_INCREMENTの場合に係り活動テーブルに入れるIDの取得に困ります。

1. 名簿テーブルにデータをINSERTする。（IDは自動生成で1が入ったとする）
2. 自動生成された1を取得するために以下の方法で取得する
3. 1. SELECTを発行する
   2. LAST_INSERT_ID（）をコールする
4. 係り活動テーブルにデータをINSERTする。

SELECTの場合、MAX関数使った場合はマルチスレッド、マルチプロセスで破綻します。
LAST_INSERT_ID（）の場合、発行する順番に気をつければ同一セッション内で値が保持されている・・・？
詳しくはわかりません(´・ω・｀)

またAUTO_INCREMENTをやめてシーケンステーブルを作成した場合、SELECTは上記と同じ理由で破綻（ロックかけるなら別ですけど。）、LAST_INSERT_IDは上手くいくが、どちらにせよ2往復している事になります。

もしDBがSEQUENCEをサポートしている場合（名簿テーブルのIDはAUTO_INCREMENTではありません）、

1. シーケンスを進めて値を取得する（仮に1とする）
2. 名簿テーブルにデータをINSERTする
3. 係り活動テーブルにデータをINSERTする

こんな感じで済みます。

とりあえず、2往復は効率が悪そう。でMySQLはシーケンスをサポートしていない。となれば作るしかなさそうです。

INSERTの戻り値として自動生成された値を取得する場合、PERLではDBIのmysql_insertidを使えば出来、JDBCの場合はStatement#getGeneratedKey()を使う事で取得が可能になるようです。ちなみにOracleの場合はgetGeneratedKey()でカラムを取得してもRowId型になった気がします。（誰かのBLOGに載っていた予感）

というわけで、早速コードを書いてみました。 

Continue reading

普通のSQLを発行するとINDEXが壊れて、自動リペアなども出来なくなります。
エラーログには日本語訳すると「自動でリペアしようとしたけど失敗しちゃった!!テヘッ」と書かれてます。
おのれ～・・・。こんなトラブルは初めてだ・・。

Got error 124 from storage engine  / ERRORNo:1030

MySQL5.0.33(ソースからビルド)で確認しました。
やり方は以下の通りで。

CREATE TABLE TEST(N1 INTEGER, N2 INTEGER);
CREATE INDEX IDX_TEST_01 ON TEST(N1,N2);
INSERT INTO TEST(N1,N2)
SELECT 1,(SELECT MAX(N2)+1 FROM TEST WHERE N1 = 1);

INDEXを貼っていなければ問題はありません。
また、INSERT文中のSELECTの1列目の"1"と2列目のWHERE句中の"1"が異なる場合も問題ありません。2列目のSELECT MAX(N2)がSELECT COUNT(N2)でも問題ありません。2列目のSELECT MAX文で1件でもHITすれば問題ありません。

酷い目にあった・・・。
MySQL5.0.37ではエラーにはなるものの、INDEXが壊れることはない感じです。

くそう・・・。ピンポイントですか(´・ω・)ぷんぷん