MySQL で ENUM 型のカラムに無効な値を入れたときの動き

はじめに

MySQL の ENUM 型を使う機会があったのですが、ENUM 型のカラムに無効な値をインサートするとなんとそのままデータを投入できたということがあったので、どうしてそんなことが起こったのか調べてみました。

実験

MySQLのバージョンに関してですが、今回は 5.7.34 を利用しました。

まず、こんな感じのテーブルを作ってみます。

CREATE TABLE members(
    id INT PRIMARY KEY AUTO_INCREMENT,
    gender ENUM('MALE', 'FEMALE', 'OTHERS'),
    name VARCHAR(255)
);

そして、データを投入してみます。これはいいですね。

INSERT INTO members(gender, name) VALUES('MALE', 'Bob');
Query 1 OK: 1 row affected

gender に無効な値を入れてみます。なぜか投入できます。

INSERT INTO members(gender, name) VALUES('UNKNOWN', 'Alice');
Query 1 OK: 1 row affected

そして、SELECT COUNT(*) FROM members WHERE gender = ''; が 1 を返すことも確認できました。つまり、UNKNOWN は空文字列へと変換されて gender カラムに設定されたのです。

エラーにしたいのだが

実はこの動作は MySQL のドキュメントにしっかりと載っていました。

An enumeration value can also be the empty string ('') or NULL under certain circumstances:

If you insert an invalid value into an ENUM (that is, a string not present in the list of permitted values), the empty string is inserted instead as a special error value. This string can be distinguished from a “normal” empty string by the fact that this string has the numeric value 0. See Index Values for Enumeration Literals for details about the numeric indexes for the enumeration values.

If strict SQL mode is enabled, attempts to insert invalid ENUM values result in an error.

ドキュメントによると strict SQL mode が有効であれば、エラーを吐いてくれるそうです。

strict SQL mode に関してはMySQL のドキュメントで以下のように述べています。

Strict SQL mode is in effect if either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, although the effects of these modes differ somewhat:

For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back.

トランザクションテーブルなら STRICT_ALL_TABLES or STRICT_TRANS_TABLES が有効であれば strict SQL mode になるようです。MySQL 5.5.5 以降はデフォルトのストレージエンジンが InnoDB なので、ENGINE を指定せずにテーブルを作ればトランザクションテーブルになります。

なので、STRICT_ALL_TABLES を sql_mode に設定すればインサートが失敗するはずです。やってみましょう。

SET SESSION sql_mode = 'STRICT_ALL_TABLES';

そして、インサート

INSERT INTO members(gender, name) VALUES('UNKNOWN', 'Alice');
Query 1 ERROR: Data truncated for column 'gender' at row 1

今度はエラーになりましたね。