使用 MySQL CHECK 约束来保证数据的正确性

对于任何应用，都对数据的正确性有要求。比如，用户的年龄必须是大于零的，用户的登录名中不能包含空格，用户的密码必须满足一定的复杂度，等等。

对于这些要求，虽然我们可以在应用界面来对用户输入的数据进行验证，但是这并不能替代数据库层面的数据验证。这能增加应用的安全性。

MySQL 提供了 CHECK 约束来保证存储到表中的数据是符合你的要求的。不符合 CHECK 约束的数据会被拒绝。

请注意，直到 MySQL 8.0.16，MySQL 才真正的支持 CHECK 约束。在更早的版本中，您只能通过触发器或者带有 WITH CHECK OPTION 的视图来模拟 CHECK 约束。

MySQL `CHECK` 语法

下面是 MySQL CHECK 的语法：

CHECK(expr)

这里， expr 是一个布尔表达式，此表达式针对一行的数据进行计算。如果返回为真，则 MySQL 允许此行插入到表中，否则 MySQL 拒绝此行插入到表中并给出错误。

您可以在 CREATE TABLE 语句中的列定义或者约束定义中使用 CHECK 约束或者在 ALTER TABLE 语句中添加 CHECK 约束。

如果你在列定义中使用 CHECK 约束，则 CHECK 表达式只能引用此列。

如果你在使用独立的 CHECK 约束，则 CHECK 表达式可以应用表上的所有列。

下面以 age 列需要大于 0 为例，使用不同的方法添加此约束：

在创建表的时候在列定义中使用 CHECK 约束

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT NOT NULL CHECK(age > 0)
);

在创建表的时候在表上定义 CHECK 约束

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT NOT NULL,
    CONSTRAINT CHECK(age > 0)
);

使用修改表语句的为表添加 CHECK 约束

ALTER TABLE user
ADD CONSTRAINT CHECK(age > 0);

MySQL `CHECK` 约束实例

通过下面的例子，你会很容易理解 MySQL CHECK 约束的用法和作用。

假设，您需要一个表存储用户的名称，登录名，密码，且需要符合以下要求：

用户的名称不能为空。
登录名的长度不少于 4 个字符。
密码的长度不少于 8 个字符。
密码不能和登录名相同。

您可以使用以下的 CREATE TABLE 语句创建表：

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(45) NOT NULL,
    login_name VARCHAR(45) NOT NULL CHECK(length(login_name) >= 4),
    password VARCHAR(45) NOT NULL CHECK(length(password) >= 8),
    CONSTRAINT CHECK(login_name <> password)
);

这里，在 CREATE TABLE 语句中有 3 个 CHECK 约束：

在 login_name 列定义中的 CHECK(length(login_name) >= 4) 保证登录名的长度不小于 4。
在 password 列定义中的 CHECK(length(password) >= 8) 保证登录名的长度不小于 8。
在表上的约束 CONSTRAINT CHECK(login_name <> password) 保证密码不能和登录名相同。

您可以通过以下 SHOW CREATE TABLE 语句查看表 user 上的约束：

SHOW CREATE TABLE user\G

*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `login_name` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_chk_1` CHECK ((length(`login_name`) >= 4)),
  CONSTRAINT `user_chk_2` CHECK ((length(`password`) >= 8)),
  CONSTRAINT `user_chk_3` CHECK ((`login_name` <> `password`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

您能够在上面的输出中发现， user 表中有 3 个 CHECK 约束。约束的名称都是 MySQL 按默认规则生成的。

注意， name 列的 NOT NULL 也是一种特殊的约束。

要验证有关登录名长度的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim', 'timisok');

由于上面语句中给出的登录名 tim 的长度小于 4，因此 MySQL 会给出以下错误：

ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

要验证有关密码长度的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'tim1', 'timisok');

由于上面语句中给出的密码 timisok 的长度小于 8，因此 MySQL 会给出以下错误：

ERROR 3819 (HY000): Check constraint 'user_chk_2' is violated.

要验证密码不能和登录名相同的 CHECK 约束是否生效，请使用下面的 INSERT 语句尝试插入一行：

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'timisgood', 'timisgood');

由于上面语句中给出的登录名和密码都是 timisgood，因此 MySQL 会给出以下错误：

ERROR 3819 (HY000): Check constraint 'user_chk_3' is violated.

您可以使用下面的语句插入一个完全符合 CHECK 约束的行。

INSERT INTO user (name, login_name, password)
VALUES ('Tim', 'hitim', 'timisgood');

这一行成功插入到了 user 表中。

结论

MySQL 提供了 CHECK 约束来保证存储到表中的数据是符合你的要求的。不符合 CHECK 约束的行会被拒绝插入到表中。

您可以在列上或者表上使用 CHECK 约束。

MySQL CHECK 语法

MySQL CHECK 约束实例

结论

本页目录

MySQL `CHECK` 语法

MySQL `CHECK` 约束实例