読者です 読者をやめる 読者になる 読者になる

職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している(いた)組織の意見ではない。

暗黙的デフォルト値

パソコン・インターネット PostgreSQL MySQL

またまたMySQLでハマったのでメモ。

現象

実行できないはず(エラーになるはず)のSQL文が、MySQLでは実行できてしまいます。
具体的には、not null 制約のある列に対して null 値を格納させようとしても、実行できてしまう場合があります。

サンプルデータ(PostgreSQLMySQLで動作確認済み)

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 な列に格納される値は、テーブル定義に明示してあるデフォルト値ではなくて、その型のデフォルト値(勝手ながら筆者の脳内ではこれを「暗黙的デフォルト値」と呼んでいます)です。数値型であればゼロ、文字列型であれば空文字列、といった具合です。ややこしいですね。

回避策その1:警告をエラーにするには?

いろいろ調べたところ、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

回避策その2:テーブル定義に明示的に宣言してあるデフォルト値を適用させるには?

PostgreSQLでもMySQLでも、実は僕はこの動作をさせたいのです。
もちろん、前述のようなinsert文であれば対象列から外してあげることで容易に実現可能です。
しかしながら、 create table (....) as select .... 構文だと、insert対象列から外すことはできないため、今回の制限が重くのしかかります。後半のselect文においてデフォルト値と同じリテラル値をselectすれば良いじゃんとも思いますが、長いSQLになると、それもあまり気が進みません。
何か、もっとクールな回避策は無いもんですかねぇ?

検証環境