MariaDB 教程 / 用 NULL、DEFAULT、NOT NULL 和 CHECK 定义输入条件

NULL 表示缺失或未知信息,与数字零和空字符串不同。DEFAULT 在省略输入或请求默认值时提供值,NOT NULL 拒绝 NULL,CHECK 检查条件是否为假;它们解决不同问题,需要按业务含义组合。仅声明默认值或一个非负条件,并不能保证所有输入都完整有效。

区分 NULL、空字符串、零和省略列

输入状态 含义 示例
NULL 缺失、未知或尚未确定 尚未收集备注
空字符串 '' 已知长度为零的字符串 提交了备注字段,但没有文字
0 数值零 库存已知为零件
省略列 未出现在 INSERT 的列列表中 库存列使用默认值零

业务可以决定把“未收集备注”和“空备注”视为相同,但这是数据策略,不是 NULL 天然等于空字符串。自动将所有 NULL 替换为空字符串,会丢掉原来可以区分的状态。本例采用普通字符串处理,不启用 EMPTY_STRING_IS_NULL 等兼容模式。

大多数普通运算符将值与 NULL 比较时,返回的是以 NULL 表示的未知结果,而不是直接为真或假。判断是否为 NULL 应使用 IS NULL,不能用 = NULL 代替。专门的 NULL 安全比较运算符另有规则,详见 NULL 值官方说明。

让每种定义承担自己的职责

定义 作用 不能据此保证
DEFAULT 0 省略该列或显式请求 DEFAULT 时提供零 通常不会把所有显式传入的 NULL 都替换为零
NOT NULL 拒绝缺失值标记 NULL 不拒绝所有空字符串、零或负数
CHECK (stock >= 0) 条件为假时拒绝该行 可空列上的这个条件本身不拒绝 NULL
NOT NULL 与 CHECK 组合 要求有值,并检查该值符合条件 不替代应用反馈或既有数据复核

不要用默认值编造未知事实。如果库存尚未清点,默认零可能错误表达为“已确认售罄”。本例有意约定新项目的初始库存为零;其他业务也许需要 NULL 或独立的清点状态。

应用检查可以给出友好提示,数据库约束则在其他应用、客户端或 SQL 文件写入时维护存储边界。只检查网页表单,不能限制所有写入入口;只靠数据库报错,也未必给使用者足够清楚的反馈。

执行默认值与可空列的完整例子

按照命令行连接与 SQL 文件教程,用 course_user 连接练习服务器,确保 shopcourse 可用。在客户端内执行:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

selected_database 应为 shopcourse。下面的正常文件只插入有效行,先保存会话 SQL 模式与 CHECK 开关,再明确启用严格模式和 CHECK 检查。使用旧版本服务器时,核对实际版本的约束支持及执行状态,不能仅凭语法被接受就认定约束会生效。

先确认 validation_lab 与 nullable_check_lab 没有被需要保留的对象占用。将完整示例保存为 UTF-8 SQL 文件,通过 SOURCE 和实际本机路径运行;故意失败的语句放在后面单独执行。

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET @saved_check_constraints = @@SESSION.check_constraint_checks;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET SESSION check_constraint_checks = ON;
CREATE TABLE validation_lab (
  item_id INT PRIMARY KEY,
  item_name VARCHAR(100) NOT NULL,
  note VARCHAR(100) NULL DEFAULT NULL,
  stock INT NOT NULL DEFAULT 0,
  CONSTRAINT ck_validation_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO validation_lab (item_id, item_name, note) VALUES
  (1, 'Notebook', NULL),
  (2, 'Mug', '');
SELECT item_id, note IS NULL AS note_is_null,
       note = '' AS note_is_empty, stock
FROM validation_lab ORDER BY item_id;
CREATE TABLE nullable_check_lab (
  item_id INT PRIMARY KEY,
  stock INT NULL,
  CONSTRAINT ck_nullable_stock CHECK (stock >= 0)
) ENGINE=InnoDB;
INSERT INTO nullable_check_lab VALUES (1, NULL);
SELECT item_id, stock IS NULL AS stock_is_null FROM nullable_check_lab;
行 note_is_null note_is_empty stock
1 1 NULL 0
2 0 1 0

第一行的 note 是 NULL,与空字符串比较时结果仍为 NULL。第二行保存空字符串,所以 IS NULL 为假,空字符串比较为真。两行都没有提供 stock,因此都使用默认值 0。Notebook 与 Mug 是示例商品名称,分别表示笔记本和杯子。

另一张 nullable_check_lab 接受 stock 为 NULL,其查询结果为 item_id 1、stock_is_null 1。原因是 CHECK (stock >= 0) 对 NULL 不产生假结果。若要求库存必须已知且非负,就要同时声明 NOT NULL 和 CHECK,不能仅靠一个非负比较。

分别测试 NOT NULL 和 CHECK 失败

正常示例结束后,在同一个启用严格模式与 CHECK 的连接中,逐条执行以下预期失败测试。第一条为必填名称传入 NULL:

INSERT INTO validation_lab (item_id, item_name) VALUES (3, NULL);

第二条提供了名称,但库存 -1 违反非负约束:

INSERT INTO validation_lab (item_id, item_name, stock) VALUES (3, 'Desk lamp', -1);

第一条应出现名称不能为 NULL 的错误;第二条应报告库存约束失败,并指出相应规则。每次失败后执行 SELECT COUNT(*) FROM validation_lab;,仍应是两行。第一次失败没有保存编号 3,所以第二次测试仍可使用该编号。错误措辞可能随版本或输出语言变化,判断时关注实际列名和约束名。

默认值、输入形式和数据类型共同决定显式 NULL 是被接受、被拒绝还是触发特殊处理。AUTO_INCREMENT 与某些 TIMESTAMP 定义有专门的 NULL 行为,不能把这个普通备注、库存例子概括为所有类型的绝对规则。对本例 stock 而言,DEFAULT 0 不会使显式 NULL 合法。

检查空白内容、规则名称与执行开关

NOT NULL 字符串仍可能为空,CHAR、VARCHAR 与 TEXT 的比较解释了字符串存储与长度。若需要额外拒绝空名称和只含普通空格的名称,可以考虑 CHECK (CHAR_LENGTH(TRIM(item_name)) > 0)。但默认 TRIM 不会去掉所有 Unicode 空白字符或不可见字形,应用仍需按需求规范化输入。

SHOW CREATE TABLE validation_lab;
SELECT @@SESSION.sql_mode, @@SESSION.check_constraint_checks;

ck_validation_stock 这样的名字使报错更容易定位。规则名应能够表达所属表、列或条件,支持不同服务器版本时也要确认命名作用域。CONSTRAINT 官方参考说明了 CHECK 语法和执行控制。

check_constraint_checks 关闭时,可能写入违反声明条件的数据。应修复输入或重新审视预期规则,而不是只为使写入成功就关闭检查。重新打开检查,也不等于已经复核、修复所有先前保存的非法行。查看表定义与当前开关是两个互补步骤。

恢复设置并限定重做范围

SET SESSION check_constraint_checks = @saved_check_constraints;
SET SESSION sql_mode = @saved_sql_mode;

在保存用户变量的同一连接里恢复设置。断开后变量不会传到新连接,应检查新会话,不能将空的保存值当成原设置。本例只调整当前会话,不需要改变服务器全局规则。

重做前使用 SELECT DATABASE(),并分别执行 SHOW CREATE TABLE validation_lab 和 SHOW CREATE TABLE nullable_check_lab,确认二者就是本文可丢弃的练习表。需要保留的数据先备份,确认后仅删除这两个目标:

DROP TABLE shopcourse.validation_lab, shopcourse.nullable_check_lab;

这会删除两张表及其行,普通 ROLLBACK 不能恢复。然后可重新执行完整正常示例。首次商品练习的 products 表不属于本次清理范围,其他业务数据也不应随之删除。