MySQL道普請便り

第60回 SQLモードについて[その1]

この記事を読むのに必要な時間:およそ 3.5 分

NO_AUTO_VALUE_ON_ZERO

このモードは,AUTO_INCREMENTなカラムを持っているテーブルに対して影響がある場合があります。

通常AUTO_INCREMENTなカラムにNULLや0を挿入すると,自動的に次のシーケンス番号を生成して置き換えます。しかし,このモードが有効になっていると,0はシーケンス番号に置き換えられることはありません。そもそもAUTO_INCREMENTなカラムに0を入れるというのもどうかと思いますが,運用上必要になることもあるかと思います。

ただし,このモードを使った時にリストアには注意が必要です。うっかり移行先のサーバでこのモードを設定し忘れると,0が挿入されたデータに次のシーケンス番号が振られて,データに差異が生まれてしまうので注意をしましょう。

このモードの挙動を確認するために,AUTO_INCREMENTなidを持った簡単なusersテーブルを作成してみます。

CREATE TABLE users(
        id INT NOT NULL AUTO_INCREMENT,
        name TEXT NOT NULL,
        PRIMARY KEY (id));

作成したテーブルにNULLと0を入れた時にどうなるか確認してみましょう。

mysql> INSERT INTO users (id, name) VALUES  (NULL, 'suzuki'), (0, 'kimura');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from users;
+----+--------+
| id | name   |
+----+--------+
|  1 | suzuki |
|  2 | kimura |
+----+--------+
2 rows in set (0.01 sec)

NULLと0を指定した場合にシーケンシャルな番号が割り振られたのがわかると思います。ここで,NO_AUTO_VALUE_ON_ZEROを有効にしてみます。

mysql> SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected, 1 warning (0.00 sec)

そして,同様に0とNULLを挿入して結果を確認してみましょう。

mysql> INSERT INTO users (id, name) VALUES  (NULL, 'sato'), (0, 'tanaka');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from users;
+----+--------+
| id | name   |
+----+--------+
|  0 | tanaka |
|  1 | suzuki |
|  2 | kimura |
|  3 | sato   |
+----+--------+
4 rows in set (0.00 sec)

先ほどと挿入した結果が変わり,idがシーケンシャルな番号ではなく,指定をした0が入っていることがわかると思います。

NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS

これらのSQLモードは,MySQLの固有のカラムオプションやインデックスオプション,テーブルのオプションなどをSHOW CREATE TABLEで表示させないようにするSQLモードです。これらの設定は,mysqldumpでスキーマなどをバックアップしたときにも影響があるので注意しましょう。

使い所としては,たとえばMySQLから他のRDBMSにデータを移し替える場合に,MySQL固有のEngineの設定などが不要な場合に利用されます。

先ほど作成したusersテーブルで,NO_TABLE_OPTIONSを例に取り説明します。

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

この時点では,ENGINE=InnoDB~~~といったMySQL固有の情報が出力されていることがわかります。続いて,NO_TABLE_OPTIONSを有効にした状態でSHOW CREATE TABLEを実行してみましょう。

mysql> SET SESSION sql_mode='NO_TABLE_OPTIONS';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text NOT NULL,
  PRIMARY KEY (`id`)
)
1 row in set (0.00 sec)

以上のように,ストレージエンジンの設定などが消えていることがわかります。

まとめ

今回はMySQLのSQLモードについて紹介してみました。まだまだ紹介できていないオプションがたくさんあるので,これからも紹介していく予定です。

著者プロフィール

木村浩一郎(きむらこういちろう)

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter:@kk2170