MySQLではテーブル作成やカラムの追加時などに、特定のカラムに対して明示的にデフォルト値を指定することができます。
下記のテーブル作成時の例では、CREATE TABLEステートメントにて、col1カラムに対してdefault_
という明示的なデフォルト値を指定しています。またカラム追加時の例では、col2カラムに対して明示的なデフォルト値100
を指定しています。カラムの明示的なデフォルト値を挿入するにはカラム名を省略するか、カラムに対する値をDEFAULT
として指定します。
MySQL 8.0以降の明示的なデフォルト値
MySQL 5.
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.text_
をデフォルト値として指定しています。
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.
式や関数をデフォルト値として使えるので、以下のようなカラム追加が可能です。
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.ALGORITHM=INSTANT
)
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.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_
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_
まとめ
今回は明示的なデフォルト値について紹介しました。
式のデフォルト値により、すべてのカラム型に対して明示的なデフォルト値を指定することが可能になりました。ただし、注意点に記載しましたが、カラム追加はALGORITHM=COPY
のみで動作することは覚えておきましょう。カラム追加はインスタントDDLが使えるからといって、何も考えずにオンラインで大きなテーブルに実行してしまうとサービス障害につながります。ALGORITHM=INSTANT
句を必ずつけるなど、運用方法を確立しておいたほうがいいでしょう。
- 参考資料
- 11.
6 データ型デフォルト値