解決：MySQL における NULL と NOT NULL の混乱

解決：MySQL における NULL と NOT NULL の混乱

SQL の感覚で理解すると、NOT NULL の意味は、テーブル内の各行でそのフィールドには必ず値が必要であり、NULL を格納できない、ということです。

フィールドに NOT NULL が指定されていない、または明示的に NULL と指定されている場合、その列は空を許可します。つまり、本当の NULL 値を保存できます。

混乱を招くのは、MySQL ではエラーになるかどうかが現在の sql_mode にも左右される点です。非 strict モードでは、MySQL は不正な NULL や欠落値を直接拒否せず、そのフィールド型の暗黙のデフォルト値へ変換し、warning を出すことがあります。strict モードでは、この種の書き込みは通常エラーになります。

よくある暗黙のデフォルト値には、次のようなものがあります。

  • INT：0
  • CHAR / VARCHAR：空文字列 ''
  • DATETIME：ゼロ日時、たとえば '0000-00-00 00:00:00' に変換されることがあります。実際に許可されるかどうかは現在の sql_mode にも依存します。

つまり、NOT NULL 自体は依然として制約です。「制約がないように見える」本当の原因は、MySQL が非 strict モードで型変換やデフォルト値の補完を代わりに行うことにあります。

NULL と空文字列は同じではない

特に注意が必要なのは、NULL と '' は同じ値ではないという点です。

  • NULL は未知、欠落、値がないことを表します。
  • '' は長さ 0 の文字列であり、実際に存在する値です。

たとえば、文字列型フィールドが NULL を許可している場合、そのフィールドには NULL を保存できます。一方、NOT NULL と定義され、明示的なデフォルト値がない場合、非 strict モードでは MySQL が不正な NULL 書き込みを空文字列 '' に変換することがあります。

これが誤解を招きやすいところです。phpMyAdmin ではフィールドが「空」に見えても、それが NULL なのか空文字列なのかは、SQL で明確に確認する必要があります。

SELECT
  password IS NULL AS password_is_null,
  password = '' AS password_is_empty_string,
  address IS NULL AS address_is_null,
  address = '' AS address_is_empty_string
FROM table1;

例

次のようなテーブルがあるとします。

CREATE TABLE table1 (
  customerid INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  password VARCHAR(100) NULL,
  address VARCHAR(100) NOT NULL,
  city VARCHAR(100)
);

ここでは、次のようになっています。

  • password は文字列型で、NULL を許可する。
  • address は文字列型で、NOT NULL が指定されている。

非 strict モードで address に NULL を書き込むと、MySQL はエラーを出さず、それを空文字列に変換することがあります。

INSERT INTO table1 (customerid, name, password, address, city)
VALUES (1, 'Julie Smith', NULL, NULL, 'Airport West');

その後、warning を確認できます。

SHOW WARNINGS;

実際に保存された値を確認することもできます。

SELECT
  customerid,
  name,
  password,
  password IS NULL AS password_is_null,
  address,
  address IS NULL AS address_is_null,
  address = '' AS address_is_empty_string,
  city
FROM table1;

password が NULL と表示され、address が空白として表示される場合、通常は、password には本当の NULL が保存され、address には空文字列 '' が保存されていることを意味します。

現在のモードを確認する

MySQL がこの種の書き込みを厳格に拒否するかどうかを判断するには、まず現在の sql_mode を確認します。

SELECT @@sql_mode;

STRICT_TRANS_TABLES または STRICT_ALL_TABLES が含まれている場合、多くの不正な書き込みは直接失敗します。strict モードがない場合、MySQL はデータを変換して warning を出すことがあります。

推奨事項

NULL と空文字列が混在するのを避けるには、フィールドの制約とデフォルト値を明示的に書くのが最善です。

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL,
  password VARCHAR(255) NULL,
  address VARCHAR(255) NOT NULL DEFAULT ''
);

業務上、「不明」と「未入力」を区別する必要があるなら NULL を許可します。業務上、空のテキストだけで十分なら NOT NULL DEFAULT '' を使用します。非 strict モードで MySQL が自動的にデフォルト値を補完することに依存してはいけません。

より堅実な方法は、strict モードを有効にし、アプリケーション層で欠落値を明示的に処理することです。

SET sql_mode = 'STRICT_TRANS_TABLES';

まとめると、NOT NULL が無効になっているわけではありません。MySQL の非 strict モードが、一部の不正な値を自動的にデフォルト値へ変換しているのです。フィールド内の値が NULL なのか空文字列なのかを判断するには、IS NULL と = '' を使ってそれぞれ確認する必要があります。

Leave a Reply