2011年6月 のアーカイブ
mysqlのAUTO_INCREMENTは、mysql再起動で再計算される
2011年6月10日
仕事関連で確認したメモ。ちょっと変わった手法なので、まぁそういうこと、といった程度の内容。
シーケンス代わりにAUTO_INCREMENTを使う
mysqlでは、シーケンスオブジェクトがなく、その代わりにAUTO_INCREMENTをカラムに指定する。AUTO_INCREMENTは、1テーブルに1カラム指定出来て、CREATE TABLEやALTER TABLEでも指定できる。
これを利用して、データは空なのだけどシーケンス値を取得するというトリックが使えないかと考えた。
CREATE TABLE test_nextval ( order_id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (order_id) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
とテーブルを作成し
SHOW TABLE STATUS LIKE 'test_nextval';
でシーケンス値を取得し
ALTER TABLE test_nextval AUTO_INCREMENT=シーケンス値+1;
とする。
AUTO_INCREMENTがリセットされる
一見、上記の方法はアリなように思えるけれど、AUTO_INCREMENTがリセットされるタイミングがある。mysqlを再起動した時だ。
mysql> SHOW CREATE TABLE test_nextval; +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_nextval | CREATE TABLE `test_nextval` ( `order_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> \q # service mysqld restart # mysql test mysql> SHOW CREATE TABLE test_nextval; +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_nextval | CREATE TABLE `test_nextval` ( `order_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB HARSET=utf8 | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
データベース内の整合性としては、中身が空なのにAUTO_INCREMENTが設定されているのはおかしいということか。
もしかしたらリセットされないオプションなどあるのかも知れないが、そもそもトリッキーな手法だし、そういうこともあるよねと納得した方が良いと思う。というかそういうオプションを用意するならば、シーケンスオブジェクトの実装をした方がまっとうな気がする。
もちろん値があればリセットされない
当然、AUTO_INCREMENTカラムに値があれば、その値+1に設定される。
mysql> INSERT INTO test_nextval (1000); Query OK, 1 row affected (0.00 sec) mysql> SHOW CREATE TABLE test_nextval; +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_nextval | CREATE TABLE `test_nextval` ( `order_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> \q # service mysqld restart # mysql test mysql> SHOW CREATE TABLE test_nextval; +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_nextval | CREATE TABLE `test_nextval` ( `order_id` int(11) NOT NULL auto_increment, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8 | +-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
結局古典的手法にもどる
つまりは、test_nextvalテーブルで最新のシーケンス値を実際に持って管理する、昔からある採番テーブルの手法でやるのがまっとうということに落ち着く。
当たり前といえばそれまでのお話。
なんでこんなことをやったかというのは、また時間が出来たら是非とも書きたい。そのくらいには苦労した。