NULL は不明または欠落を表し、空文字列、数値のゼロ、JSON 文書内の null リテラルとは異なります。NOT NULL は実際の制約であり、過去の記事にある「MySQL ではもはや制約ではない」という説明は誤りです。結果が分かりにくく見える主因は、セッションの sql_mode、省略した列、明示的なデフォルト、IGNORE、暗黙の変換、またはアプリが異なる状態を同じ空欄として表示することです。
本ガイドは MySQL 8.4 と InnoDB を中心に扱います。アプリや本番データを変える前に隔離環境で再現し、strict モードを無効化したり sql_mode 全体を置き換えたりしてエラーを消してはいけません。
Table of Contents
混同してはいけない5つの状態
| 値または状態 | 意味 | 正しい確認 |
|---|---|---|
SQL NULL | 不明、欠落、非該当。通常の値ではない | col IS NULL |
空文字列 '' | 長さゼロと分かっている文字列 | col = '' |
数値 0 | 既知の数値ゼロ | col = 0 |
JSON null | JSON 文書内に存在する null スカラー | パスが存在し JSON_TYPE(...) <=> 'NULL' |
| 列を省略 | INSERT がその列の式を渡していない | 明示的デフォルト、NULL 許可、現在のモードで決まる |
これらを区別するかは、先に業務モデルで決めます。たとえば「備考が提供されていない」は SQL NULL、「利用者が明示的に空欄にした」は ''、再試行回数 0 は有効な数値です。画面ですべて空欄に見えるという理由で意味を統合しないでください。
一時テーブルで差を安全に観察する
次の例は機密データを含まず、現在のセッションに限定される一時テーブルを使います。本番スキーマのテンプレートではありません。
CREATE TEMPORARY TABLE null_demo (
id BIGINT UNSIGNED PRIMARY KEY,
note VARCHAR(80) NULL,
attempts INT NOT NULL DEFAULT 0,
metadata JSON NULL
);
INSERT INTO null_demo (id, note, attempts, metadata) VALUES
(1, NULL, 0, NULL),
(2, '', 0, JSON_OBJECT('state', NULL)),
(3, 'ready', 2, JSON_OBJECT('state', 'ready'));1 つの結果で SQL NULL、空文字列、ゼロ、JSON null、存在しないパスを区別できます。
SELECT
id,
note IS NULL AS note_is_sql_null,
note = '' AS note_is_empty,
attempts = 0 AS attempts_is_zero,
metadata IS NULL AS metadata_is_sql_null,
JSON_CONTAINS_PATH(metadata, 'one', '$.state') AS state_path_exists,
JSON_TYPE(JSON_EXTRACT(metadata, '$.state')) <=> 'NULL' AS state_is_json_null,
JSON_CONTAINS_PATH(metadata, 'one', '$.missing') AS missing_path_exists
FROM null_demo
ORDER BY id;MySQL の JSON 型文書は SQL NULL と JSON null を区別しています。JSON_TYPE 文書では JSON null の型結果が文字列 NULL で、SQL NULL 引数からは SQL NULL が返ると説明されています。パスの存在も調べることで、「パスなし」を「パスが存在し値が JSON null」と誤認せずに済みます。
三値論理とNULL安全等価
通常の比較に NULL が含まれると、多くの場合 UNKNOWN となり、結果では NULL と表示されます。WHERE は述語が TRUE の行だけを残すため、col = NULL では NULL を検索できません。
SELECT
NULL = NULL AS ordinary_equal,
NULL <=> NULL AS null_safe_equal,
1 <=> NULL AS one_null_safe_equal;
SELECT id FROM null_demo WHERE note IS NULL;
SELECT id FROM null_demo WHERE note <=> NULL;IS NULL が最も明確な NULL 判定です。MySQL 固有の <=> は NULL 安全等価で、両辺が NULL なら 1、片方だけが NULL なら 0 を返します。NULL を含めた等価比較には有用ですが、不明瞭なデータモデルを隠すために使うものではありません。NOT IN の集合に NULL が入ると条件が UNKNOWN になることもあります。NULL を明示的に除外するか、意味の明確な NOT EXISTS を使います。
集約関数がNULLを扱う方法
COUNT(*) は行数を数え、COUNT(expr) は式が NULL でない行だけを数えます。SUM、AVG、MIN、MAX は通常 SQL NULL を無視しますが、すべて NULL または入力なしの場合の結果は関数ごとに確認します。安全な棚卸しの形は次のとおりです。
SELECT
COUNT(*) AS total_rows,
COUNT(note) AS non_null_notes,
COUNT(*) - COUNT(note) AS null_notes,
COALESCE(SUM(note = ''), 0) AS empty_notes,
COALESCE(SUM(attempts = 0), 0) AS zero_attempt_rows
FROM null_demo;COUNT(note) を全行数とみなしてはいけません。また、不明な業務値を COALESCE で無条件にゼロへ変えないでください。ここでは空入力の計数結果を数値の件数に整えるためだけに使っています。
省略値、明示的NULL、DEFAULT
3 つの書き方は異なります。列の省略はサーバーにデフォルト動作を選ばせ、DEFAULT はその列のデフォルトを要求し、明示的な NULL は SQL NULL の書き込みを要求します。管理画面の空欄から推測せず、実際の DDL を確認します。
CREATE TEMPORARY TABLE write_demo (
id BIGINT UNSIGNED PRIMARY KEY,
optional_note VARCHAR(80) NULL,
required_note VARCHAR(80) NOT NULL,
state VARCHAR(20) NOT NULL DEFAULT 'new'
);
INSERT INTO write_demo (id, required_note) VALUES (1, 'ready');
INSERT INTO write_demo (id, optional_note, required_note, state)
VALUES (2, NULL, 'ready', DEFAULT);NULL 許可列に明示的デフォルトがない場合、MySQL は DEFAULT NULL として定義します。明示的デフォルトのない NOT NULL 列では、strict モードは省略値または明示的 NULL を拒否します。非 strict モードでは型の暗黙デフォルトを入れて warning を出すことがあります。AUTO_INCREMENT、生成列、式デフォルトには個別の規則があるため、正確な DDL とバージョンで判断してください。
strictモード、IGNORE、エラー、warning
MySQL 8.4 の新規インストールでは通常 strict モードが有効ですが、アップグレード、マネージドサービス、接続プール、アプリの初期化文が実際のセッション値を変えることがあります。現在の接続とサーバーのグローバル値を必ず確認します。
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'sql_mode';strict モードでは、不正な InnoDB データ変更は通常エラーとなり、その文がロールバックされます。非トランザクション表では部分書き込みがあり得るため、ストレージエンジンも確認します。非 strict モードは値を調整して warning を出すことがあります。データ型デフォルト文書に、省略値と暗黙デフォルトの正確な条件があります。
INSERT IGNORE または UPDATE IGNORE は「検証に合格」の意味ではありません。IGNORE は一部のエラーを warning に変え、競合行を飛ばしたり不正値を近い値へ調整したりします。UPDATE IGNORE は statement-based replication で安全でない場合もあります。移行やデータ修復の失敗を IGNORE で隠さないでください。
データを調整し得る文の後は、次の文で上書きされる前に診断を直ちに読み取ります。
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS LIMIT 100;warning がゼロ、影響行数が想定どおり、アプリエラーがない、という 3 点を受入証拠に含めます。「クエリ成功」だけでは不十分です。
sql_mode全体を置き換えない
SET sql_mode = 'STRICT_TRANS_TABLES' を直接実行すると、ONLY_FULL_GROUP_BY、日付検査、その他の既存モードがすべて消えます。先に値を保存し、MySQL の sys.list_add() で対象モードだけを追加し、試験後にセッションを復元します。
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;
SET @previous_session_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = sys.list_add(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES');
SELECT @@SESSION.sql_mode;
SET SESSION sql_mode = @previous_session_sql_mode;sys schema が使えない場合は、DBA が変更管理システム内でリストを解析、重複排除、再構成します。古い可能性のあるデフォルト文字列を手作業でコピーしないでください。
SESSION、GLOBAL、PERSISTの境界
| スコープ | 影響 | 再起動後 | 重要な制限 |
|---|---|---|---|
SESSION | 現在の接続だけ | 消える | プールやアプリが新規接続時に再設定し得る |
GLOBAL | 今後の接続のデフォルト | 消える | 既存接続は変わらず、観察には接続の入れ替えが必要 |
PERSIST | 実行時グローバルを変更して mysqld-auto.cnf へ書く | 残る | 管理権限と構成管理との調整が必要 |
PERSIST_ONLY | 次回起動値だけを書く | 残る | 現在と再起動後の差を作るため、一時試験には不適切 |
グローバル変更をせずに、候補値を作ってレビューします。
SET @previous_global_sql_mode = @@GLOBAL.sql_mode;
SET @candidate_sql_mode = sys.list_add(@previous_global_sql_mode, 'STRICT_TRANS_TABLES');
SELECT @previous_global_sql_mode, @candidate_sql_mode;アプリ互換性試験、承認、バックアップ、ロールバック演習の後、承認済みの公開方法を 1 つだけ選びます。
SET GLOBAL sql_mode = @candidate_sql_mode;
SET PERSIST sql_mode = @candidate_sql_mode;この 2 行は選択肢であり、順番に実行する手順ではありません。GLOBAL は非永続の実行時公開、PERSIST は実行時グローバルを変えて永続化します。ロールバックも、使った方式に対応する方だけを実行します。
SET GLOBAL sql_mode = @previous_global_sql_mode;
SET PERSIST sql_mode = @previous_global_sql_mode;RESET PERSIST IF EXISTS sql_mode は永続オーバーライドを除くだけで、現在の実行時値を自動復元しません。本番変更では、アプリのセッション初期化、永続値の出所、起動設定、副本の一貫性も確認します。
NULLからNOT NULLへの移行
既存列を NOT NULL へ変える作業は、単なる ALTER TABLE ではなくアプリとデータの移行です。安全な順序は、意味の定義、棚卸し、アプリ書き込み更新、バッチ補完、新規 NULL の防止、副本とバックアップの確認、最後に制約追加です。
1. DDL、データ、依存関係を棚卸しする
型、長さ、文字セット、照合順序、デフォルト、コメント、生成式、インデックスを含む完全な列定義を保持します。次の例は機密性のないテーブル名を使います。
SHOW CREATE TABLE customer_profile;
SELECT
COUNT(*) AS total_rows,
COUNT(display_name) AS non_null_rows,
COUNT(*) - COUNT(display_name) AS null_rows,
COALESCE(SUM(display_name = ''), 0) AS empty_rows
FROM customer_profile;
SELECT id
FROM customer_profile
WHERE display_name IS NULL
ORDER BY id
LIMIT 100;すべての writer、reader、バッチ、インポート、トリガー、外部キー、生成列、ビュー、CDC、バックアップ、レポート、副本も棚卸しします。InnoDB、バッチ可能な主キー、復元試験済みバックアップを確認し、テーブルサイズ、書き込み率、空きディスク、複製遅延を測定します。
2. アプリを先に互換化し、その後補完する
古い NULL をまだ読める一方、新しい NULL は書かないアプリ版を先に公開します。置換値には正しい業務上の出所を選び、すべてを '' や 0 にしないでください。たとえば fallback_name が空でなく承認済みの場合だけ、再現可能な主キー範囲で補完します。
UPDATE customer_profile
SET display_name = fallback_name
WHERE id >= 10000
AND id < 10500
AND display_name IS NULL
AND fallback_name IS NOT NULL
AND fallback_name <> '';
SHOW COUNT(*) WARNINGS;
SHOW WARNINGS LIMIT 100;各バッチで一致行、変更行、warning、アプリエラー、ロック待ち、副本遅延を照合します。主キー範囲とバッチサイズは実データから決め、例の数値をコピーしないでください。信頼できる出所から導けない NULL は、人または業務判断のキューに送り、データを捏造しません。
3. 管理された時間帯で制約を追加する
アプリが NULL を書かなくなり、棚卸しがゼロで、副本が正常になった後、本番と同等の schema、データ規模、バージョンを持つ環境で正確な DDL を演習します。MODIFY では完全な列定義を書き直す必要があり、文字セット、デフォルト、コメントなどを省くと意図せず変わる可能性があります。
ALTER TABLE customer_profile
MODIFY COLUMN display_name VARCHAR(120) NOT NULL,
ALGORITHM=INPLACE,
LOCK=NONE;
SHOW WARNINGS;これは例の列定義にすぎません。ALGORITHM=INPLACE と LOCK=NONE は、エンジンや操作が対応できない場合にエラーとなり、より強いロックやテーブルコピーへの暗黙フォールバックを防ぎますが、「ロックなし」を意味しません。オンライン DDL も初期化と commit 時にメタデータロックを取り、テーブル再構築や一時領域を使うことがあり、並行書き込み、長時間トランザクション、オンラインログ過大で失敗し得ます。失敗時は保護句を削って再実行せず、停止して調査してください。
4. 複製、監視、リリースゲート
変更前に、ソースと全副本の MySQL バージョン、エンジン、テーブル定義、文字セット、sql_mode、複製形式、DDL 対応が互換であることを確認します。不要な長時間トランザクションを止め、監視閾値を定め、メタデータロック、ディスク、I/O、CPU、エラーログ、複製遅延、複製エラーを観察します。
検証済みのローリング schema 計画がない限り、副本へ手作業で schema 差分を作らないでください。オンライン DDL は並行 DML を許せますが、新しい NULL の並行書き込みで最後に失敗することがあります。アプリ書き込みゲート、データベース制約変更、副本追従を 1 つの公開工程として扱います。
受入とロールバック
制約完了後に schema、データ、実行中セッションの状態を検証します。
SHOW CREATE TABLE customer_profile;
SELECT COUNT(*) AS remaining_nulls
FROM customer_profile
WHERE display_name IS NULL;
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;
SHOW WARNINGS;アプリの読み書き、バッチ、インポート、バックアップ復元、フェイルオーバー、副本読み取りも試験し、すべて合格してから保守時間を終了します。
制約を撤回する必要がある場合、可空・非 NULL の両方に対応するアプリ版へ先に戻し、管理された時間帯で正確な逆 DDL を実行します。
ALTER TABLE customer_profile
MODIFY COLUMN display_name VARCHAR(120) NULL,
ALGORITHM=INPLACE,
LOCK=NONE;逆 DDL もテーブルを再構築またはロックすることがあり、補完前の NULL を復元しません。データのロールバックには、復元試験済みバックアップ、変更ログ、または別に保持した対応表が必要です。アプリ障害を「戻す」方法として strict モードを無効化しないでください。
リリース前チェックリスト
- SQL
NULL、''、0、JSON null、存在しない JSON パスに明示的な業務上の意味がある。 - ソース、全副本、全アプリ接続種別の
sql_modeを棚卸しし、他のモードを置き換えていない。 - 省略値、明示的
NULL、明示的デフォルト、IGNORE経路を試験した。 - アプリが先に新規 NULL を止め、置換値に信頼できる出所があり、残り NULL はゼロ。
- 正確な DDL を同等環境で演習し、メタデータロック、空間、時間、並行性の証拠がある。
- 複製、CDC、バックアップ復元、監視、保守時間、停止閾値を確認した。
- アプリのロールバック、逆 DDL、データ復元に別々の担当者がいて、DDL を戻せばデータも戻るとは想定していない。
公式資料
- MySQL 8.4：Working with NULL Values
- MySQL 8.4：比較演算子とNULL安全等価
- MySQL 8.4：集約関数
- MySQL 8.4：JSONデータ型
- MySQL 8.4：JSON_TYPE
- MySQL 8.4：データ型デフォルト値
- MySQL 8.4：INSERTとIGNORE
- MySQL 8.4：SHOW WARNINGS
- MySQL 8.4：sys.list_add
- MySQL 8.4：永続化システム変数
- MySQL 8.4：ALTER TABLE
- MySQL 8.4：InnoDBオンラインDDL
- MySQL 8.4：オンラインDDL操作対応表
- MySQL 8.4：オンラインDDLの失敗条件
2011年原文アーカイブ
次の不活性なプレーンテキスト囲みは、source_export の可視本文を完全に保持します。リンク、個人データ、認証情報、バックスラッシュは含まれないため、安全上の削除はありません。原文 22 行目の末尾 ASCII 空白 1 文字だけを正規化しました。このアーカイブは過去の出典にすぎません。「NOT NULL はもはや制約ではない」「NULL と空文字列は実質的に同じ」という主張は維持版で訂正済みであり、現在の MySQL 8.4 の助言ではありません。
解决：MySQL中NULL和NOT NULL的混乱
本来，指定NOT NULL的意思是表中所有行的此属性必须有一个值。
如果没有指定或者指定为NULL，该列可以为空（NULL）。
但是，在MySQL中，你用NOT NULL，高版本的会自己解析默认值的:
- INT -> 0；
- CHAR -> **‘‘** （空值）；
- DATATIME -> ‘0000-00-00 00:00:00’ 等等。
说白了，MySQL中的NOT NULL已经不是约束条件“表中所有行的此属性必须有一个值”了。并且如果字段是“字符型”，在DEFAULT情况下和NULL基本上是一样的。
**注意**：我在实际操作中发现，“表1″的“字段” password 是“字符型”，未指定（也就是指定为NULL）；“字段” address 是“字符型”，指定为NOT NULL，在phpmyadmin中的显示如下:
表1
customerid name password address city 1 Julie Smith *NULL* Airport West
在 MySQL 中，为一个 NOT NULL 字段设置了一个 NULL 值，如果非STRICK模式，它并不会出错；但在SQL中却会出错。
MySQL 会自动将 NULL值转化为该字段的默认值， 那怕是你在表定义时**没有**明确地为该字段设置默认值，一般来说MySQL还是会自动为你添加默认值的，比如为一个 NOT NULL 的整型赋 NULL 值，结果是 0。
设为not null，不赋值的话字符串会为**‘‘**(空值)，int会为0，不会出现错误。也就是说，设为not null，空值仍可以入库。
如果设为NULL，默认值为**‘‘**(空值)。