第206回　カラムへの明示的なデフォルト値について

MySQLではテーブル作成やカラムの追加時などに、特定のカラムに対して明示的にデフォルト値を指定することができます。

下記のテーブル作成時の例では、CREATE TABLEステートメントにて、col1カラムに対してdefault_valueという明示的なデフォルト値を指定しています。またカラム追加時の例では、col2カラムに対して明示的なデフォルト値100を指定しています。カラムの明示的なデフォルト値を挿入するにはカラム名を省略するか、カラムに対する値をDEFAULTとして指定します。

テーブル作成時の例

mysql> CREATE TABLE t1 (id int key, col1 varchar(255) NOT NULL DEFAULT 'default_value')

カラム追加時の例

mysql> ALTER TABLE t1 ADD col2 INT NOT NULL DEFAULT 100;

MySQL 8.0以降の明示的なデフォルト値

MySQL 5.7とそれ以前では、BLOB、TEXT、GEOMETRYとJSON型に明示的なデフォルト値を指定することができませんでした。

mysql> ALTER TABLE t1 ADD col3 TEXT NOT NULL DEFAULT 'text_data';
ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'col3' can't have a default value

MySQL 8.0（MySQL 8.0.13）以降からは、すべてのカラム型に対して明示的なデフォルト値を指定することができます。BLOB、TEXT、GEOMETRYとJSON型の場合には、デフォルト値をカッコで囲むことで実現できます。下記の例では、col3カラムにリテラル値のtext_dataをデフォルト値として指定しています。

mysql> ALTER TABLE t1 ADD col3 TEXT NOT NULL DEFAULT ('text_data');
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL 8.0から式のデフォルト値の機能がサポートされました。式のデフォルト値とは、式や関数をデフォルト値として利用することです。前述の、型に対して明示的なデフォルト値を指定できるのも、式としてリテラルの値を指定することで可能になったことです。構文としてはDEFAULT句のあとの値や式をカッコで囲みます。

式や関数をデフォルト値として使えるので、以下のようなカラム追加が可能です。

mysql>  ALTER TABLE t1 ADD col5 INT NOT NULL DEFAULT (col4/2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

式のデフォルト値にはいくつかのルールがあり、許可されていない構造が式に含まれているとエラーになります。

リテラル、組込み関数（決定的および非決定的の両方）と演算子が使用可能
サブクエリー、パラメータ、変数、ストアドファンクションとユーザー定義関数は使用不可
AUTO_INCREMENT属性を持つカラムに依存不可
仮想列または式のデフォルト値を持つカラムへのフォワード参照を含めることはできない

式のデフォルト値の運用における注意点

式のデフォルト値の運用において注意点があります。MySQL 8.0から、カラムの追加などいくつかの操作に対して即時で完了できるインスタントDDLが（ALGORITHM=INSTANT）サポートされました。しかし、この式のデフォルト値を指定したカラム追加においてはインスタントDDLはサポートされていません。

mysql> ALTER TABLE t1 ADD col6 text NOT NULL DEFAULT ('test_default') , ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

MySQL 8.0.34現在、式のデフォルト値を指定したカラム追加はALGORITHM=COPYのみで動作します。そのため、実行中は書き込みロックを伴う操作となる点にご注意ください。オンラインで実行するには、以前この連載で紹介したgh-ost[1]を利用したり、サービスをメンテナンスに入れてからカラム追加することになるでしょう。

varchar型などで式ではないデフォルト値のカラム追加においては、インスタントDDLは動作します。

mysql> ALTER TABLE t1 ADD col7 varchar(255) NOT NULL DEFAULT 'test_default' , ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL8.0.34の追加機能

varchar型とtext型のカラムに対してSESSION_USER()、USER()、SYSTEM_USER()、およびCURRENT_USER()の関数をデフォルト値として使用できるようになりました。対象のカラムには、少なくとも288文字（ユーザー名255文字、ホスト名32文字、セパレータ@1文字）を格納できるようにする必要があります。

mysql> CREATE TABLE t2 ( id serial,username varchar(288) NOT NULL DEFAULT(CURRENT_USER()));
Query OK, 0 rows affected (0.01 sec)

暗黙のデフォルト値

明示的なデフォルト値を指定してないと、暗黙のデフォルト値が入ります。それは以下の通りです。

数値型は 0
文字列型は空文字
ENUM型は最初の列挙値
TIMESTAMP型はexplicit_defaults_for_timestampシステム変数により異なる

たとえば、以下のようなテーブルに対して、デフォルト値を指定していないNOT NULLを指定したint（数値型）のカラムを追加してみます。

mysql> SELECT * FROM t3;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> ALTER TABLE t3 ADD col8 int NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

そうすると、数値型の暗黙のデフォルト値の0が挿入されます。

mysql> SELECT * FROM t3;
+----+------+
| id | col8 |
+----+------+
|  1 |    0 |
|  2 |    0 |
+----+------+
2 rows in set (0.00 sec)

管理や運用面からも、暗黙のデフォルト値を利用するのではなく、明示的にデフォルト値を指定するのが良いでしょう。

TIMESTAMP型のデフォルト値はややクセがあります。第196回 MySQLのexplicit_defaults_for_timestampオプションによって意図せずデータとテーブル定義変更をしてしまう現象についてで説明していますので、併せてご確認ください。

まとめ

今回は明示的なデフォルト値について紹介しました。

式のデフォルト値により、すべてのカラム型に対して明示的なデフォルト値を指定することが可能になりました。ただし、注意点に記載しましたが、カラム追加はALGORITHM=COPYのみで動作することは覚えておきましょう。カラム追加はインスタントDDLが使えるからといって、何も考えずにオンラインで大きなテーブルに実行してしまうとサービス障害につながります。ALGORITHM=INSTANT句を必ずつけるなど、運用方法を確立しておいたほうがいいでしょう。

参考資料: 11.6 データ型デフォルト値