またまたMySQLでハマったのでメモ。
現象
実行できないはず（エラーになるはず）のSQL文が、MySQLでは実行できてしまいます。
具体的には、not null 制約のある列に対して null 値を格納させようとしても、実行できてしまう場合があります。
サンプルデータ（PostgreSQLとMySQLで動作確認済み）
create table test20130205 ( id int not null primary key, value1 int not null default 42, value2 text not null ); insert into test20130205 (id, value1, value2) values (1, 2, 'hogehoge1'); insert into test20130205 (id, value1, value2) values (2, null, 'hogehoge2'); insert into test20130205 (id, value1, value2) values (3, 5, null); insert into test20130205 (id, value1, value2) values (4, null, null); insert into test20130205 (id, value1, value2) select 5, null, null;
PostgreSQLでの実行結果
postgres=# insert into test20130205 (id, value1, value2) values (1, 2, 'hogehoge1'); INSERT 0 1 postgres=# insert into test20130205 (id, value1, value2) values (2, null, 'hogehoge2'); ERROR: null value in column "value1" violates not-null constraint postgres=# insert into test20130205 (id, value1, value2) values (3, 5, null); ERROR: null value in column "value2" violates not-null constraint postgres=# insert into test20130205 (id, value1, value2) values (4, null, null); ERROR: null value in column "value1" violates not-null constraint postgres=# insert into test20130205 (id, value1, value2) select 5, null, null; ERROR: null value in column "value1" violates not-null constraint
もちろん、全部エラーになります。テーブル定義にはデフォルト値を明示的に指定していますが、これらが適用されることもありません。
MySQLでの実行結果
mysql> insert into test20130205 (id, value1, value2) values (1, 2, 'hogehoge1'); Query OK, 1 row affected (0.00 sec) mysql> insert into test20130205 (id, value1, value2) values (2, null, 'hogehoge2'); ERROR 1048 (23000): Column 'value1' cannot be null mysql> insert into test20130205 (id, value1, value2) values (3, 5, null); ERROR 1048 (23000): Column 'value2' cannot be null mysql> insert into test20130205 (id, value1, value2) values (4, null, null); ERROR 1048 (23000): Column 'value1' cannot be null mysql> insert into test20130205 (id, value1, value2) select 5, null, null; Query OK, 1 row affected, 2 warnings (0.00 sec) Records: 1 Duplicates: 0 Warnings: 2 mysql> show warnings; +---------+------+--------------------------------+ | Level | Code | Message | +---------+------+--------------------------------+ | Warning | 1048 | Column 'value1' cannot be null | | Warning | 1048 | Column 'value2' cannot be null | +---------+------+--------------------------------+ 2 rows in set (0.00 sec)
最後の insert select 構文のものは実行できてしまいました。警告が出てはいますが、（設定によるかもしれないけど）これは phpMyAdmin 等では表示されません。気付かずに放置してしまうこともあるでしょう（僕は放置してしまったので後でハマったんですが。。）。しかも、これらの not null な列に格納される値は、テーブル定義に明示してあるデフォルト値ではなくて、その型のデフォルト値（勝手ながら筆者の脳内ではこれを「暗黙的デフォルト値」と呼んでいます）です。数値型であればゼロ、文字列型であれば空文字列、といった具合です。ややこしいですね。
回避策その１：警告をエラーにするには？
いろいろ調べたところ、StackOverflow に出ている php - Preventing MySQL from inserting implicit default values into not null columns - Stack Overflow が役に立ちました。MySQLの設定「SQL_MODE」に「STRICT_ALL_TABLES」を指定する、というものです。
SQL_MODE について詳しくはこちら： MySQL :: MySQL 5.6 Reference Manual :: 5.1.7 Server SQL Modes
回避策その２：テーブル定義に明示的に宣言してあるデフォルト値を適用させるには？
PostgreSQLでもMySQLでも、実は僕はこの動作をさせたいのです。
もちろん、前述のようなinsert文であれば対象列から外してあげることで容易に実現可能です。
しかしながら、 create table (....) as select .... 構文だと、insert対象列から外すことはできないため、今回の制限が重くのしかかります。後半のselect文においてデフォルト値と同じリテラル値をselectすれば良いじゃんとも思いますが、長いSQLになると、それもあまり気が進みません。
何か、もっとクールな回避策は無いもんですかねぇ？
検証環境
- PostgreSQL : 9.1.4
- MySQL : 5.1.61 (engine = MyISAM)
