解决：MySQL中NULL和NOT NULL的混乱

解决：MySQL 中 NULL 和 NOT NULL 的混乱

按 SQL 的直觉理解，NOT NULL 的意思是：表中每一行的这个字段都必须有一个值，不能存入 NULL。

如果字段没有指定 NOT NULL，或者显式指定为 NULL，那么该列允许为空，也就是可以保存真正的 NULL 值。

让人混乱的是：在 MySQL 中，是否报错还要看当前的 sql_mode。在非严格模式下，MySQL 有时不会直接拒绝非法的 NULL 或缺失值，而是把它转换成该字段类型的隐式默认值，并给出 warning；在严格模式下，这类写入通常会报错。

常见的隐式默认值包括：

  • INT：0
  • CHAR / VARCHAR：空字符串 ''
  • DATETIME：可能被转换为零日期时间，例如 '0000-00-00 00:00:00'，具体是否允许还取决于当前 sql_mode

也就是说，NOT NULL 本身仍然是约束；真正造成“看起来没有约束”的原因，是 MySQL 在非严格模式下会替你做类型转换和默认值补齐。

NULL 和空字符串不是一回事

需要特别注意：NULL 和 '' 不是同一个值。

  • NULL 表示未知、缺失、没有值。
  • '' 是一个长度为 0 的字符串，是一个实际存在的值。

例如，一个字符型字段如果允许 NULL，它可以保存 NULL；如果定义为 NOT NULL 且没有显式默认值，在非严格模式下，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。

如果在非严格模式下向 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，很多非法写入会直接失败；如果没有严格模式，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 ''。不要依赖 MySQL 在非严格模式下自动补默认值。

更稳妥的做法是开启严格模式，并在应用层显式处理缺失值：

SET sql_mode = 'STRICT_TRANS_TABLES';

总结一下：NOT NULL 并不是失效了，而是 MySQL 的非严格模式会把某些非法值自动转换为默认值。要判断字段里到底是 NULL 还是空字符串，应使用 IS NULL 和 = '' 分别检查。

Leave a Reply