【MySQL】表的约束(上)-EW帮帮网

欢迎拜访：雾里看山-CSDN博客
本篇主题：【MySQL】表的约束(上)
发布时间：2025.2.10
隶属专栏：MySQL

在这里插入图片描述

表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。约束的本质是通过技术的手段，倒逼程序员插入正确的数据。反过来，站在MySQL的视角，凡是插入进来的数据，都是符合数据约束的！
约束的最终目标：保证数据的完整性和可预期性。

空属性

基本介绍

两个值：null(默认的)和not null(不为空)
数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参加运算。

mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+null;
+--------+
| 1+null |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)

功能使用

案例：
创建一个班级表，包含班级名和班级所在的教室以及其他属性。站在正常的业务逻辑中：

如果班级没有名字，你不知道你在哪个班级
如果教室名字可以为空，就不知道在哪上课

所以我们在设计数据库表的时候，一定要在表中进行限制，满足插入条件的数据才能插入到表中。这就是“约束”。

mysql> create table if not exists myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(20) not null,
    -> other varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | NULL    |       |
| other      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into myclass values ('高三2班','101教室','重点班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into myclass (class_name, class_room) values ('高三1班','103教室');
Query OK, 1 row affected (0.01 sec)

mysql> insert into myclass (class_name, other) values ('高三1班','普通班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

mysql> insert into myclass (class_name, class_room) values ('高三1班', NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null

mysql> select * from myclass;
+------------+------------+-----------+
| class_name | class_room | other     |
+------------+------------+-----------+
| 高三2班    | 101教室    | 重点班    |
| 高三1班    | 103教室    | NULL      |
+------------+------------+-----------+
2 rows in set (0.00 sec)

默认值（default）

默认值：某一种数据会经常出性的出现某一个具体的值，可以在一开始就设定好，在需要真实数据的时候，用户可以选择性的使用默认值。

mysql> create table if not exists t13(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) default '男'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t13;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

默认值的生效：当数据在插入的时候不给该字段赋值，就使用默认值。

mysql> insert into t13 (name,age,gender) values ('张三',19,'女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t13 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

not null 和 default

如果我们没有明确指定一列要插入，用的是default，如果建表中，对应列默认没有设置default值，则插入会报错。
在没有设置not null 的情况下，MySQL会默认设置default的值为NULL。
在设置not null的情况下，default将不会被默认设置。
not null和default并不冲突，而是相互补充。
当两者共同设置的时候，只能用户插入合法数据，当用户忽略插入时，使用default的默认值。
在实际应用中，not null和default一般不需要同时被设置。

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA 来进行了解。

mysql> create table if not exists t14(
    -> name varchar(20) not null comment '用户名',
    -> age tinyint unsigned default 18 comment '用户的年龄',
    -> gender char(1) default '男' comment '用户的性别'
    -> );
Query OK, 0 rows affected (0.01 sec)

通过desc查看不到注释信息：

mysql> desc t14;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

通过show可以看到：

mysql> show create table t14\G
*************************** 1. row ***************************
       Table: t14
Create Table: CREATE TABLE `t14` (
  `name` varchar(20) NOT NULL COMMENT '用户名',
  `age` tinyint(3) unsigned DEFAULT '18' COMMENT '用户的年龄',
  `gender` char(1) DEFAULT '男' COMMENT '用户的性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

zerofill

在刚学习数据库时，很多人对数字类型后面的长度是很迷茫。
例如下面的这个表：

mysql> create table if not exists t15(
    -> a int unsigned not null,
    -> b int unsigned not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t15;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| a     | int(10) unsigned | NO   |     | NULL    |       |
| b     | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？其实没有zerofill这个属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，如下：

mysql> insert into t15 (a,b) values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t15;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

但是对列添加了zerofill属性后，显示的结果就有所不同了。修改t15表的属性：

mysql> alter table t15 modify b int(4) unsigned zerofill not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t15;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| a     | int(10) unsigned         | NO   |     | NULL    |       |
| b     | int(4) unsigned zerofill | NO   |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

对a列添加了zerofill属性，再进行查找，返回如下结果：

mysql> select * from t15;
+---+------+
| a | b    |
+---+------+
| 1 | 0002 |
+---+------+
1 row in set (0.00 sec)

这次可以看到b的值由原来的2变成0002，这就是zerofill属性的作用。

如果宽度小于设定的宽度（这里设置的是4），自动填充0。
如果宽度大于设定的宽度，则会正常显示所有位数。
要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是原来的数据。

mysql> insert into t15 (a,b) values (1,22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t15 (a,b) values (1,222);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t15 (a,b) values (1,2222);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t15 (a,b) values (1,22222);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t15 (a,b) values (1,222222);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t15;
+---+--------+
| a | b      |
+---+--------+
| 1 |   0002 |
| 1 |   0022 |
| 1 |   0222 |
| 1 |   2222 |
| 1 |  22222 |
| 1 | 222222 |
+---+--------+
6 rows in set (0.00 sec)

为什么是这样呢？我们可以用hex函数来证明。

mysql> select b,hex(b) from t15;
+--------+--------+
| b      | hex(b) |
+--------+--------+
|   0002 | 2      |
|   0022 | 16     |
|   0222 | DE     |
|   2222 | 8AE    |
|  22222 | 56CE   |
| 222222 | 3640E  |
+--------+--------+
6 rows in set (0.00 sec)

可以看出数据库内部存储的还是原来的数据,0002只是设置了zerofill属性后的一种格式化输出而已。

int长度的解释

为什么 unsigned int 默认长度是10 ，int 默认是11：
int最大的值为2³¹ -1，unsigned int最大的值为2³² -1，最大数的长度都是10位，但是int有正负之分，所以最长需要11位。

主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

创建表并指定主键

mysql> create table if not exists test_key(
    -> id int unsigned primary key comment '这个是学生的学号',
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键约束

主键对应的字段中不能重复，一旦重复，操作失败。

mysql> insert into test_key values (1,'张飞');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_key values (1,'刘备');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test_key values (2,'刘备');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
+----+--------+
2 rows in set (0.00 sec)

依据主键查找和更新数据

mysql> select * from test_key where id=2;
+----+--------+
| id | name   |
+----+--------+
|  2 | 刘备   |
+----+--------+
1 row in set (0.10 sec)

mysql> update test_key set name='曹操' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 曹操   |
+----+--------+
2 rows in set (0.00 sec)

删除主键

mysql> alter table test_key drop primary key;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

添加主键

当表创建好以后但是没有主键的时候，可以再次追加主键

mysql> alter table test_key add primary key(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

复合主键

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> create table pick_course(
    -> id int unsigned,
    -> course_id int unsigned comment '课程编号',
    -> score tinyint unsigned comment '成绩',
    -> primary key(id,course_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc pick_course;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id        | int(10) unsigned    | NO   | PRI | NULL    |       |
| course_id | int(10) unsigned    | NO   | PRI | NULL    |       |
| score     | tinyint(3) unsigned | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into pick_course values(1234,40,90);
Query OK, 1 row affected (0.00 sec)

mysql> insert into pick_course values (1235, 40, 90);
Query OK, 1 row affected (0.01 sec)

mysql> insert into pick_course values (1234, 41, 85);
Query OK, 1 row affected (0.00 sec)

mysql> insert into pick_course values(1234, 40, 80);
ERROR 1062 (23000): Duplicate entry '1234-40' for key 'PRIMARY'
mysql> select * from pick_course;
+------+-----------+-------+
| id   | course_id | score |
+------+-----------+-------+
| 1234 |        40 |    90 |
| 1234 |        41 |    85 |
| 1235 |        40 |    90 |
+------+-----------+-------+
3 rows in set (0.00 sec)

⚠️ 写在最后：以上内容是我在学习以后得一些总结和概括，如有错误或者需要补充的地方欢迎各位大佬评论或者私信我交流！！！

【MySQL】表的约束(上)

目录

空属性

基本介绍

功能使用

默认值（default）

not null 和 default

列描述

zerofill

int长度的解释

主键

创建表并指定主键

主键约束

依据主键查找和更新数据

删除主键

添加主键

复合主键

网站公告

今日签到

热门文章

最新发布

【MySQL】 表的约束(上)

目录

空属性

基本介绍

功能使用

默认值（default）

not null 和 default

列描述

zerofill

int长度的解释

主键

创建表并指定主键

主键约束

依据主键查找和更新数据

删除主键

添加主键

复合主键

网站公告

今日签到

热门文章

最新发布

【MySQL】表的约束(上)