Solved: Clearing Up the Confusion Around NULL and NOT NULL in MySQL

Solved: Clearing Up the Confusion Around NULL and NOT NULL in MySQL

According to the usual SQL intuition, NOT NULL means that this field must have a value in every row of the table, and NULL cannot be stored in it.

If a field does not specify NOT NULL, or is explicitly specified as NULL, then the column allows nulls, meaning it can store a real NULL value.

What makes this confusing is that, in MySQL, whether an error is reported also depends on the current sql_mode. In non-strict mode, MySQL sometimes does not directly reject an invalid NULL or missing value. Instead, it converts it to the implicit default value for that field type and issues a warning. In strict mode, this kind of write will usually fail with an error.

Common implicit default values include:

  • INT: 0
  • CHAR / VARCHAR: the empty string ''
  • DATETIME: it may be converted to a zero datetime, such as '0000-00-00 00:00:00'; whether this is allowed also depends on the current sql_mode

In other words, NOT NULL itself is still a constraint. The real reason it can appear to have no effect is that MySQL performs type conversion and fills in default values for you in non-strict mode.

NULL and the empty string are not the same thing

Pay special attention to this: NULL and '' are not the same value.

  • NULL means unknown, missing, or no value.
  • '' is a string with length 0. It is an actual existing value.

For example, if a character field allows NULL, it can store NULL. If it is defined as NOT NULL and has no explicit default value, then in non-strict mode MySQL may convert an invalid NULL write into the empty string ''.

This can easily cause misunderstanding: in phpMyAdmin the field may look blank, but whether it is actually NULL or an empty string needs to be checked explicitly with 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;

An example

Suppose we have this table:

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

Here:

  • password is a character field and allows NULL;
  • address is a character field and is specified as NOT NULL.

If you write NULL into address in non-strict mode, MySQL may not report an error. Instead, it may convert the value to an empty string:

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

You can then check the warning:

SHOW WARNINGS;

You can also inspect the value that was actually saved:

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;

If password is shown as NULL, while address appears blank, it usually means that password contains a real NULL, while address contains the empty string ''.

Check the current mode

To determine whether MySQL will strictly reject this kind of write, first check the current sql_mode:

SELECT @@sql_mode;

If it contains STRICT_TRANS_TABLES or STRICT_ALL_TABLES, many invalid writes will fail directly. If strict mode is not enabled, MySQL may convert the data and issue a warning.

Recommendations

To avoid mixing up NULL and empty strings, it is best to specify field constraints and default values explicitly:

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

If the business logic needs to distinguish between 'unknown' and 'not filled in', allow NULL. If the business logic only needs empty text, use NOT NULL DEFAULT ''. Do not rely on MySQL automatically filling in default values in non-strict mode.

A more robust approach is to enable strict mode and explicitly handle missing values in the application layer:

SET sql_mode = 'STRICT_TRANS_TABLES';

To summarize: NOT NULL has not stopped working. Rather, MySQL's non-strict mode automatically converts some invalid values to default values. To determine whether a field contains NULL or an empty string, use IS NULL and = '' to check them separately.

Leave a Reply