‘mysql’ タグのついている投稿


仕事関連で確認したメモ。ちょっと変わった手法なので、まぁそういうこと、といった程度の内容。

シーケンス代わりに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テーブルで最新のシーケンス値を実際に持って管理する、昔からある採番テーブルの手法でやるのがまっとうということに落ち着く。
当たり前といえばそれまでのお話。

なんでこんなことをやったかというのは、また時間が出来たら是非とも書きたい。そのくらいには苦労した。


指定のレンタルサーバにwordpressを設置する作業で少しだけ特殊な例に当たった。
設置するサーバが決まる前に、別のテスト環境で作ったデータを流し込む必要があり、さてサーバの用意が出来たので作業しますか、という段階でmysqlがUNIXソケット設定なのを知った。
用途がローカルからのアクセスだけであれば、UNIXソケットで全然構わないのだけど、phpMyAdminもwordpressもデフォルト設定がTCP/IP前提だったのでちょっとつまづいた。

phpMyAdminの設定

config.inc.php で

$cfg['Servers'][$i]['connect_type'] = 'socket'; //デフォルトでは tcp となっているのを変更する
$cfg['Servers'][$i]['socket'] = 'UNIXソケットのファイル名';

を設定する。

wordpressの設定

wp-config.php で

define('DB_HOST', 'localhost:UNIXソケットのファイル名');

を設定する。
 
デフォルト設定がTCP/IPであるから、全体としてUNIXソケットであることの方が少ないのかも知れない。とはいえ設定変更はそんなでも無いので大きな問題では無かったのだけど、そういえばこういう問題があるなと思った。