当前位置：首页 > article >正文

从新手到高手的蜕变：MySQL 约束进阶全攻略

article 2025/1/20 1:18:40

一、前言

如果你正在使用 MySQL 进行数据管理，那么你一定知道，高效的数据库管理离不开对数据的严格约束。约束不仅可以帮助我们避免数据错误，还能提高数据的查询效率，优化数据库的性能。无论是小型的个人项目，还是大型的企业级应用，掌握 MySQL 约束都是至关重要的。接下来，我们将详细介绍 MySQL 中的各种约束类型及其应用场景，让你轻松成为数据库管理的高手。

二、约束分类详解

实体完整性约束：

1. 主键约束：数据库表中的主键约束，如同身份证号，唯一且不能为空，能标识每一行记录。

# 例如，当我们创建数据库的时候，指定student_id为数据库主键
mysql> create table students (
    ->     student_id int,
    ->     name varchar(20),
    ->     primary key (student_id)
    -> );

# 当创建主键约束以后，student_id 独一无二且不能为空，
mysql> insert into students select 1, 'aa';
Query OK, 1 row affected (0.01 sec)

# 当插入两条相同的数据的时候，会出现异常禁止插入
mysql> insert into students select 1, 'aa';
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

# 主键约束不能为空
mysql> insert into students select null, 'aa';
ERROR 1048 (23000): Column 'student_id' cannot be null

# 思考一下，前面我们说过，在创建表的时候没有指定主键，
会生成一个默认主键，我们可以通过修改表结构添加主健吗?

# 尝试删除主键，我们发现可以删除，当我们删除注解后，系统会使用默认主键
mysql> ALTER TABLE students DROP PRIMARY KEY;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0


# 再次添加主键，可以发现添加成功了，
mysql> ALTER TABLE students ADD PRIMARY KEY (student_id);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 再次添加 name 为主键，我们发现出现异常
mysql> ALTER TABLE students ADD PRIMARY KEY (name);
ERROR 1068 (42000): Multiple primary key defined

# 总结，当我们没有指定主键的时候，数据库会默认生成一个主键
# 主键可以在建表的时候创建，也可以后期修改表创建，同时也可以删除主键定义，当删除后，系统继续默认生成一个主键
# 注解只能定义一个，当定义多个时，会抛出异常禁止操作。

2. 唯一约束：唯一约束确保表中某列或某组列的值具有唯一性，但允许有空值。

# 删除表 students 
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

# 创建表 students, 并指定唯一索引
create table students  (
	id int,
	name varchar(20),
	unique key uk_id_card ( name )
);	
Query OK, 0 rows affected (0.03 sec)

# 插入数据验证约束是否唯一
mysql> insert into students select null, 'aa';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 当我们重复插入的时候，可以发现，约束成功防止插入相同数据
mysql> insert into students select null, 'aa';
ERROR 1062 (23000): Duplicate entry 'aa' for key 'uk_id_card'

# 插入 null 验证是否只能插入一条
mysql> insert into students select 1, null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 我们发现，当重复插入的值为 null 的数据时，唯一约束不起作用
mysql> insert into students select 1, null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 删除唯一索引
mysql> ALTER TABLE students DROP index  uk_id_card;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#当我们发现单个字段为空时，唯一索引不起作用，
如果是多个字段创建的联合索引，当插入值为null的数据约束会成功吗？
mysql> alter table students add unique key uk_id_card ( id, name );
Query OK, 0 rows affected (0.03 sec)

# 可以发现，当插入的数据唯一约束字段存在 null, 唯一约束失效
mysql> insert into students select 1, null;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into students select 1, null;
Query OK, 1 row affected (0.00 sec)

# 总结
# 唯一约束可以在建表的时候创建，也可以后期添加
# 当插入的唯一索引值不为 null，只能成功插入一条数据
# 当单个字段或者多个字段唯一约束插入值为 null 时，可以成功添加，唯一约束失效，存在多条唯一约束值等于 null 的数据

域完整性约束：

1. 非空约束：非空约束规定表中的某列不允许为空值

# 删除表结构
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

# 创建表 students ， 并为 id 添加非空约束
create table students  (
	id int not null,
	name varchar(20)
);


# 可以发现， 当正常插入数据 id值不为空的时候，可以成功插入
# 当 id 值为空的时候，约束成功，数据插入失败
mysql> insert into students select 1, null;
Query OK, 1 row affected (0.01 sec)

mysql> insert into students select null, 'aaa';
ERROR 1048 (23000): Column 'id' cannot be null

# 总结
# 当一个字段要求不为空的时候，可以创建非空约束，用于保障数据完整
# 防止因为程序问题或者恶意插入为空数据导致数据异常，影响系统稳定

2. 默认值约束：默认值约束为表中的列提供一个默认值。当插入数据时，如果没有为该列指定值，就会自动使用默认值

# 删除表结构
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

# 创建表，并为 name 指定默认值
create table students  (
	id int,
	name varchar(20) default '系统'
);
# 插入数据 ，并指定 name 为 null
mysql> insert into students select 1, null;
Query OK, 1 row affected (0.01 sec)

# 插入数据，name 不指定插入值
mysql> insert into students (id) select 1;
Query OK, 1 row affected (0.01 sec)

# 查询结果，对于我们显示指定为 null 的数据，默认值是不生效的
# 对于我们没有指定的数据，会使用默认值填充数据
mysql> select * from students;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
|    1 | 系统 |
+------+------+
2 rows in set (0.00 sec)

# 总结
# 当列添加默认约束后，插入数据只有在不指定当前列, 默认约束才会生效
# 当我们指定字段为 null 时，会将 null 插入数据库，默认值不生效

3. 合适的数据类型进行约束：选择合适的数据类型本身就是一种重要的约束方式

# 删除表结构
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

# 我们创建一个 类型为int enum, set 的数据表
mysql> create table students(
    -> id int,
    -> sex enum('male', 'female'),
    -> hobby set('book', 'mysql', 'innodb', 'java')
    -> );
Query OK, 0 rows affected (0.02 sec)

# 插入数据时，当我们的数据类型不属于 enum 中的规范，会抛出异常
mysql> insert into  students select '1', 'male', 'aaa';
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

# 插入数据时，当我们的数据类型不属于 set 中的规范，会抛出异常,
mysql> insert into  students select '1', 'male', 'aaa';
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

# 什么情况下，数据类型约束会出现失效？
# sql_mode 值为 STRICT_TRANS_TABLES 约束数据正确性，当我们删除验证规则，数据会添加成功

mysql> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+----------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+----------------------------------------------------------------+

mysql> set sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> insert into  students select '1', 'male', 'aaa';
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

# 查看数据，发现值被设置为 '' 字符串
mysql> select * from students;
+------+------+-------+
| id   | sex  | hobby |
+------+------+-------+
|    1 | male |       |
+------+------+-------+

# 总结
# 当我们使用数据类型约束时，需要 设置 sql_mode 值为 STRICT_TRANS_TABLES 
# 否则，数据还是会插入成功，数据会替换为 系统值，导致数据出现问题

参照完整性约束

外键约束：外键约束用于建立两个表之间的关联关系，保证数据的一致性和完整性。

# 创建表结构，并指定 child 的 parent_id 关联 parent 的 id 
create table parent (
	id int not null,
	primary key(id)
);
Query OK, 0 rows affected (0.03 sec)

create table child (
	id int not null,
	parent_id int,
	foreign key (parent_id) references parent(id)	
);
Query OK, 0 rows affected (0.02 sec)


# 此时 我们先给 parent 插入两条数据
mysql> insert into parent select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into parent select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 再给 child 插入两条数据
mysql> insert into child select 1, 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into child select 2, 2;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

# 当有外键约束时，child  插入 parent 不存在的数据时，会抛出异常，以保障数据完整性
mysql> insert into child select 3, 3;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`innodb_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

# 修改也是同理，当 child 修改的值是 parent 不存在的数据时，会抛出异常，以保障数据完整性
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`innodb_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

# 修改 当 child 修改的值是 parent 存在的数据，可以修改成功
mysql> update child set parent_id = 2 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 那对 parent 的约束呢？
# 当 parent 的列在 child 存在时，当前列不能修改和删除，我们发现，在修改和删除parent id 为 2 的数据时，会出现异常，表明约束成立，
mysql> update parent set id = 1 where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`innodb_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

mysql> delete from  parent where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`innodb_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

# 删除  parent 的列在 child 不存在的数据时，可以删除成功
mysql> delete from parent where id = 1;
Query OK, 1 row affected (0.00 sec)

# 外键约束的类型分为 4 种 
# RESTRICT（限制）模式，这是默认的外键约束模式。当在子表中插入或更新数据时，如果对应的外键值在父表中不存在，或者在父表中删除或更新数据导致子表中的外键引用无效，操作将会被拒绝
# CASCADE（级联）模式 当父表中的记录被更新或删除时，子表中对应的记录也会自动更新或删除
# SET NULL（设置为空）模式，当父表中的记录被更新或删除时，子表中对应的外键列的值会被设置为NULL。前提是子表中的外键列允许为NULL
# NO ACTION（无动作）模式 这种模式和RESTRICT模式类似，当父表中的记录被更新或删除时，如果会导致子表中的外键引用无效，操作会被阻止

# 对于第一种 RESTRICT 模式为默认外键模式，现在我们看一下第二种 CASCADE 模式
mysql> drop table child ;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table parent ;
Query OK, 0 rows affected (0.01 sec)

mysql> create table parent (
    -> id int not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

# 通过 ON UPDATE CASCADE ON DELETE CASCADE 指定修改和删除的外键模式为 级联
mysql> CREATE TABLE child (
    -> id INT NOT NULL,
    -> parent_id INT,
    -> FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE
    -> )
    -> ;
Query OK, 0 rows affected (0.03 sec)

# 插入数据
mysql> insert into parent select 1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into parent select 2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into child select 1, 1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into child select 2, 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

# 修改 parent  id 为 2 的主键 改为 3
mysql> update parent set id = 3 where id = 2;
Query OK, 1 row affected (0.00 sec)

# 查询子表，我们发现子表的父级id 跟着发生改变了 
mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         1 |
|  2 |         3 |
+----+-----------+
2 rows in set (0.00 sec)


# 同时在我们删除父表数据时，子表数据跟着一起删除了
mysql> delete from parent where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  2 |         3 |
+----+-----------+
1 row in set (0.00 sec)


# 现在我们看一下第三种 set null 模式
mysql> drop table child ;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table parent ;
Query OK, 0 rows affected (0.01 sec)

# 通过 ON UPDATE set null ON DELETE SET NULL 指定修改和删除的外键模式为 set null
mysql> create table parent (
    -> id int not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE child (
    -> id INT NOT NULL,
    -> parent_id INT,
    -> FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE set null ON DELETE SET NULL
    -> )
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into parent select 1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into parent select 2;
Query OK, 1 row affected (0.00 sec)

mysql> insert into child select 1, 1;
Query OK, 1 row affected (0.01 sec)

mysql> insert into child select 2, 2;
Query OK, 1 row affected (0.00 sec)


# 现在我们先修改一下看一下数据
mysql> update parent set id = 3 where id = 1;
Query OK, 1 row affected (0.00 sec)

# 我们发现子表数据变为 null
mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |      NULL |
|  2 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

# 再尝试 删除一下 id = 2 的数据，我们发现子表数据也设置为空
mysql> delete from parent where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from child;
+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |      NULL |
|  2 |      NULL |
+----+-----------+
2 rows in set (0.00 sec)

# 对于第 4 个 NO ACTION 和默认类型，在此就不进行演示，

# 对于外键约束，有时候我们在导入数据可能会因为约束而导入失败
# 通过参数 foreign_key_checks可以在导入的过程中忽略外键检查
mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set, 1 warning (0.00 sec)

触发器约束：触发器是一种特殊的存储过程，它在特定的数据库事件（如插入、更新、删除数据）发生时自动执行。通过触发器可以实现一些复杂的业务规则约束。

# 假设我们有一张用户消费表，每次购买一个物品后价格都是减少的。若此时一个用户不怀好意减去一个负值，这样用户的钱没有减少反而不断增加，如：

mysql> create table usercash (
    -> userid int not null,
    -> cash int unsigned not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into usercash select 1, 1000;
Query OK, 1 row affected (0.01 sec)

# 此时我们给 cash 减去一个负值，这在数据库的角度是没有问题，但在业务的角度是存在问题的
update usercash set cash = cash - (-20) where  userid = 1

# 此时 我们可以可以通过触发器来约束这个行为，当结果增加时，我们抛出自定义异常
delimiter $$
create trigger aft_usercash_update before update on usercash
for each row 
begin
if new.cash - old.cash > 0 then
	SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'cash不能增加';
end if;
end;
$$	

# 这样我们就能保证我们的业务逻辑是正确的
mysql> update usercash set cash = cash - (-20) where  userid = 1;
ERROR 1644 (45000): cash不能增加