NULL and NOT NULL in MySQL 8.4: Semantics, Strict Mode, and Safe Migration

NULL means unknown or missing. It is not the empty string, numeric zero, or the null literal inside a JSON document. NOT NULL is a real constraint; the historical claim that it is no longer a constraint in MySQL is incorrect. Confusing results usually come from the session sql_mode, omitted columns, explicit defaults, IGNORE, implicit conversion, or an application rendering different states as the same blank.

This guide targets MySQL 8.4 and InnoDB. Reproduce behavior in isolation before changing the application or production data. Do not make errors disappear by disabling strict mode or replacing the complete sql_mode value.

Table of Contents

Five states that must remain distinct

Value or state	Meaning	Correct test
SQL `NULL`	Unknown, missing, or inapplicable; not an ordinary value	`col IS NULL`
Empty string `''`	A known string of length zero	`col = ''`
Numeric `0`	A known numeric zero	`col = 0`
JSON `null`	A null scalar that exists inside a JSON document	The path exists and `JSON_TYPE(...) <=> 'NULL'`
Omitted column	The `INSERT` supplied no expression for the column	Determined by explicit default, nullability, and current mode

The business model must decide whether these states differ. For example, “note not supplied” can be SQL NULL, “user deliberately left it blank” can be '', and a retry count of 0 is a valid number. Do not merge meanings merely because a UI renders all of them as blank.

Observe the differences safely with a temporary table

This example contains no sensitive data and uses a session-scoped temporary table. It is not a production schema template:

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'));

One result can distinguish SQL NULL, the empty string, zero, JSON null, and a missing path:

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's JSON type documentation distinguishes SQL NULL from JSON null. The JSON_TYPE documentation says the JSON null type is the string NULL, while a SQL NULL argument produces SQL NULL. Testing path existence prevents a missing path from being mistaken for an existing path whose value is JSON null.

Three-valued logic and NULL-safe equality

An ordinary comparison involving NULL usually produces UNKNOWN, displayed as NULL. WHERE retains only rows for which the predicate is TRUE, so col = NULL cannot find nulls:

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 is the clearest explicit null test. MySQL's <=> is NULL-safe equality: it returns 1 when both sides are NULL and 0 when only one is NULL. It is useful when equality must include nulls, but should not conceal an unclear data model. A NOT IN set that contains NULL can also make the predicate UNKNOWN; exclude nulls deliberately or use a semantically clear NOT EXISTS.

How aggregates treat NULL

COUNT(*) counts rows, whereas COUNT(expr) counts only rows where the expression is not NULL. SUM, AVG, MIN, and MAX generally ignore SQL NULL; check each function's result for all-null or empty input. A safe inventory shape is:

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;

Do not treat COUNT(note) as the total row count, and do not use COALESCE to turn unknown business values into zero indiscriminately. Here it only normalizes an empty-input counting result to a count.

Omitted values, explicit NULL, and DEFAULT

These forms differ: omitting a column asks the server to choose default behavior; writing DEFAULT requests that column's default; writing NULL explicitly requests SQL NULL. Inspect the real DDL instead of guessing from a blank administration screen:

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);

When a nullable column has no explicit default, MySQL defines it with DEFAULT NULL. For a NOT NULL column with no explicit default, strict mode rejects an omitted value or explicit NULL; non-strict mode can insert the type's implicit default and issue a warning. AUTO_INCREMENT, generated columns, and expression defaults have their own rules, so use the exact DDL and version.

Strict mode, IGNORE, errors, and warnings

A fresh MySQL 8.4 installation normally enables strict mode, but upgrades, managed services, connection pools, and application initialization can change the effective session value. Always inspect the current connection and global server value:

SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'sql_mode';

In strict mode, an invalid InnoDB data-change statement normally errors and rolls back the statement. A nontransactional table can be partially changed, so verify the storage engine too. Non-strict mode can adjust values and issue warnings. The data type default documentation describes the exact conditions for omitted values and implicit defaults.

INSERT IGNORE or UPDATE IGNORE does not mean “validation passed.” IGNORE turns some errors into warnings, skips conflicting rows, or adjusts invalid values. UPDATE IGNORE can also be unsafe under statement-based replication. Do not use IGNORE to hide failures during migration or data repair.

Read diagnostics immediately after any statement that may adjust data because a subsequent statement replaces them:

SHOW COUNT(*) WARNINGS;
SHOW WARNINGS LIMIT 100;

Zero warnings, the expected affected-row count, and no application errors should all be part of the acceptance evidence. A successful query status alone is insufficient.

Never replace the entire sql_mode

Running SET sql_mode = 'STRICT_TRANS_TABLES' directly removes ONLY_FULL_GROUP_BY, date checks, and every other existing mode. Save the value first, then use MySQL sys.list_add() to add only the target mode and restore the session after testing:

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;

If the sys schema is unavailable, a DBA should parse, deduplicate, and rebuild the list in the change-management system. Do not manually copy a possibly stale default string.

SESSION, GLOBAL, and PERSIST boundaries

Scope	Effect	After restart	Important limitation
`SESSION`	Current connection only	Gone	A pool or application can reset it on a new connection
`GLOBAL`	Default for future connections	Gone	Existing connections do not change and must be recycled for observation
`PERSIST`	Changes runtime global and writes `mysqld-auto.cnf`	Retained	Requires administrative privilege and coordination with configuration management
`PERSIST_ONLY`	Writes only the next-start value	Retained	Creates current-versus-restart drift and is unsuitable for a temporary test

Build and review the candidate value without making a global change:

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;

After application compatibility testing, approval, backup, and rollback rehearsal, choose exactly one approved rollout route:

SET GLOBAL sql_mode = @candidate_sql_mode;
SET PERSIST sql_mode = @candidate_sql_mode;

These lines are alternatives, not sequential steps. GLOBAL is a nonpersistent runtime rollout; PERSIST changes runtime global and persists it. For rollback, execute only the matching alternative that was used:

SET GLOBAL sql_mode = @previous_global_sql_mode;
SET PERSIST sql_mode = @previous_global_sql_mode;

RESET PERSIST IF EXISTS sql_mode removes the persisted override but does not restore the current runtime value automatically. A production change must also account for application session initialization, the source of persisted values, startup configuration, and replica consistency.

Migrating from NULL to NOT NULL

Changing an existing column to NOT NULL is an application and data migration, not just an ALTER TABLE. The safe order is: define semantics, inventory, update application writes, backfill in batches, prevent new nulls, verify replicas and backups, and only then add the constraint.

1. Inventory DDL, data, and dependencies

Retain the full column definition, especially its type, length, character set, collation, default, comment, generated expression, and indexes. This example uses a nonsensitive table name:

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;

Also inventory every writer, reader, batch job, import, trigger, foreign key, generated column, view, CDC stream, backup, report, and replica. Confirm InnoDB, a batchable primary key, a tested restore, and measurements for table size, write rate, free disk, and replication lag.

2. Make the application compatible, then backfill

First deploy an application version that can still read old nulls but no longer writes new ones. Select a truthful business source for each replacement; never turn all nulls into '' or 0. For example, backfill only when fallback_name is nonempty and approved, using repeatable primary-key ranges:

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;

For every batch, reconcile matched rows, changed rows, warnings, application errors, lock waits, and replica lag. Choose primary-key ranges and batch sizes from actual data rather than copying the example numbers. Nulls that cannot be derived from a trusted source belong in a human or business decision queue, not in fabricated data.

3. Add the constraint in a controlled window

After the application stops writing nulls, the inventory reaches zero, and replicas are healthy, rehearse the exact DDL in an environment with production-equivalent schema, data scale, and version. MODIFY must restate the full column definition; omitting character set, default, comment, or other attributes can change them unexpectedly.

ALTER TABLE customer_profile
  MODIFY COLUMN display_name VARCHAR(120) NOT NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

SHOW WARNINGS;

This is only an example definition. ALGORITHM=INPLACE and LOCK=NONE cause an error when the engine or operation cannot honor them, preventing a silent fallback to a stronger lock or table copy; they do not mean “lock free.” Online DDL still takes metadata locks during initialization and commit, can rebuild a table and consume temporary space, and can fail because of concurrent writes, long transactions, or an oversized online log. Stop and investigate a failure instead of deleting the protective clauses and retrying blindly.

4. Replication, monitoring, and release gates

Before the change, verify that the source and every replica have compatible MySQL versions, engines, table definitions, character sets, sql_mode, replication format, and DDL support. Quiesce unnecessary long transactions, define monitoring thresholds, and watch metadata locks, disk, I/O, CPU, error logs, replication lag, and replication errors.

Do not manually create schema drift on replicas unless following a tested rolling-schema plan. Online DDL can allow concurrent DML, but a concurrent new null can make the operation fail at the end. The application write gate, database constraint change, and replica catch-up belong to one release process.

Acceptance and rollback

After the constraint completes, verify the schema, data, and running session state:

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;

Also test application reads and writes, batch jobs, imports, backup restoration, failover, and replica reads. Close the maintenance window only when every check passes.

If the constraint must be removed, first return to an application version compatible with both nullable and non-null data, then run the exact reverse DDL in a controlled window:

ALTER TABLE customer_profile
  MODIFY COLUMN display_name VARCHAR(120) NULL,
  ALGORITHM=INPLACE,
  LOCK=NONE;

Reverse DDL can also rebuild or lock the table and does not restore the original nulls that were backfilled. Data rollback requires a tested backup, change log, or separately retained mapping. Do not disable strict mode as a way to “roll back” application failures.

Pre-release checklist

SQL NULL, '', 0, JSON null, and a missing JSON path have explicit business meanings.
The source, every replica, and every application connection class have inventoried sql_mode values without replacing other modes.
Omitted values, explicit NULL, explicit defaults, and IGNORE paths are tested.
The application stops producing new nulls first, replacement values have trusted provenance, and remaining nulls are zero.
The exact DDL was rehearsed in an equivalent environment, with evidence for metadata locks, space, duration, and concurrency.
Replication, CDC, backup restoration, monitoring, maintenance windows, and stop thresholds are verified.
Application rollback, reverse DDL, and data restoration have separate owners; no one assumes reversing DDL restores data.

Official references

Archived 2011 source

The inert plain-text fence below preserves the complete visible source_export body. It contains no links, personal data, credentials, or backslashes, so no safety redaction was needed. One trailing ASCII space on source line 22 was normalized. This archive is historical provenance only: claims that “NOT NULL is no longer a constraint” or that NULL and the empty string are effectively the same have been corrected by the maintained guide and are not current MySQL 8.4 advice.

解决：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，默认值为**‘‘**(空值)。