【MySQL】005.MySQL表的约束(上)

表的约束

1. 约束概念

真正约束字段的是数据类型，但数据类型约束很单一，需要一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。

表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。就好比我们在vs上编译代码时，vs会对我们进行各种约束，保证我们的代码在语法上是正确的。

约束本质是通过技术手段倒逼程序员插入正确的数据。反过来，站在mysql的视角，插入进来的数据，都是符合数据约束的。

约束的最终目标：保证数据的完整性和可预期性。

2. 空属性

2.1 基本语法

两个值：null（默认的）和not null (不为空)

• null是不能参与数据运算的

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

2.2 使用示例

创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：

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

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

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.04 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    |       |
+------------+-------------+------+-----+---------+-------+

插入数据：

//全插入
mysql> insert into myclass (class_name,class_room,other) values ('高三二班','101教室','普通班');
mysql> select * from myclass;
+--------------+------------+-----------+
| class_name   | class_room | other     |
+--------------+------------+-----------+
| 高三二班     | 101教室    | 普通班    |
+--------------+------------+-----------+//最后一行不做插入
mysql>insert into myclass (class_name,class_room) values ('高三三班','102教室');
mysql> select * from myclass;
+--------------+------------+-----------+
| class_name   | class_room | other     |
+--------------+------------+-----------+
| 高三二班     | 101教室    | 普通班    |
| 高三三班     | 102教室    | NULL      |
+--------------+------------+-----------+//只插入班级,我们会发现不让我们插入
mysql>insert into myclass (class_name) values ('高三三班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value//插入教室为NULL,我们会发现提示我们class_room不能为空
mysql> insert into myclass (class_name,class_room) values ('高三三班',NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null

3. 默认值

3.1 基本概念

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

3.2 使用示例

mysql> create table if not exists t12(-> name varchar(20) not null,-> age tinyint unsigned default 18,-> gender char(1) default '男'-> );
Query OK, 0 rows affected (0.03 sec)mysql> desc t12;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | 18      |       |
| gender | char(1)          | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+

插入：

mysql> insert into t12 (name,age,gender) values ('张三',19,'女');
Query OK, 1 row affected (0.01 sec)mysql> select * from t12;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
+--------+------+--------+
1 row in set (0.00 sec)
//插入时并未指明性别和年纪
mysql> insert into t12 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)mysql> select * from t12;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

default如果设置了，用户将来插入如果有具体的数据，就用用户给的，如果没有，就用默认的。

mysql> create table t13(-> name varchar(20) not null,-> age tinyint default 18,-> gender char(1) not null default '男'-> );
Query OK, 0 rows affected (0.03 sec)mysql> desc t13;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | NO   |     | NULL    |       |
| age    | tinyint     | YES  |     | 18      |       |
| gender | char(1)     | NO   |     | 男      |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t13 (name,age,gender) values (NULL,'18','男');
ERROR 1048 (23000): Column 'name' cannot be null
mysql> insert into t13 (age,gender) values ('18','男');//如果我们没有明确指定一列要插入，用的是default,如果建表中对应列默认没有设置default值，将无法插入。
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into t13 (name,age,gender) values ('张三','20',NULL);
ERROR 1048 (23000): Column 'gender' cannot be null
mysql> insert into t13 (name,age) values ('张三','20');
Query OK, 1 row affected (0.01 sec)mysql> select * from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
+--------+------+--------+mysql> insert into t13 (name,age,gender) values ('张三',NULL,'男');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t13 (name,gender) values ('张三','男');
Query OK, 1 row affected (0.00 sec)mysql> select * from t13;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
| 张三   | NULL | 男     |
| 张三   |   18 | 男     |
+--------+------+--------+
3 rows in set (0.00 sec)

1. 如果我们没有明确指定一列要插入，用的是default,如果建表中对应列默认没有设置default值，将无法插入。
2. default和not null不冲突，而是互相补充的。
3. null和not null是作用在用户想插的时候。
4. default是作用在用户忽略了这一列的时候，如果设置了使用默认值，如果没有设置，直接报错。

如果我们在建表时没有设置默认值，mysql会对我们的表进行优化。

mysql> create table t14(-> name varchar(20),-> age varchar(20)-> );
Query OK, 0 rows affected (0.02 sec)mysql> show create table t14 \G;
*************************** 1. row ***************************Table: t14
Create Table: CREATE TABLE `t14` (`name` varchar(20) DEFAULT NULL,`age` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

设置了not null后mysql就不设置默认的default了。

4. 列描述

4.1 基本概念

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

4.2 使用示例

mysql>  create table if not exists t15(-> name varchar(20) not null comment '这个是用户的用户名',-> age tinyint unsigned default 18 comment '这个是用户的年纪',-> gender char(1) default '男' comment '这个是用户的性别'-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc t15;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | 18      |       |
| gender | char(1)          | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t15 (name,age,gender) values ('张三',19,'女');
Query OK, 1 row affected (0.07 sec)
//通过desc查看不到注释信息：
mysql> select * from t15;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
+--------+------+--------+
1 row in set (0.00 sec)
//通过show可以看到：
mysql> show create table t15;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t15   | CREATE TABLE `t15` (`name` varchar(20) NOT NULL COMMENT '这个是用户的用户名',`age` tinyint unsigned DEFAULT '18' COMMENT '这个是用户的年纪',`gender` char(1) DEFAULT '男' COMMENT '这个是用户的性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

首先这个数据不是给数据库看的，它是给数据库的管理员

5. zerofill

zerofil是一种关于显示方面的约束

5.1 基本功能

1. 自动填充零：当显示数值时，如果数值的位数小于列定义的宽度，MySQL 会在左侧用零填充。

2. 隐式 UNSIGNED：从 MySQL 8.0.17 开始，ZEROFILL 属性已被标记为已弃用，并且使用它会自动将列设置为 UNSIGNED（无符号）。

5.2 使用示例

CREATE TABLE example (id INT(5) ZEROFILL,code INT(10) ZEROFILL
);INSERT INTO example VALUES (12, 12345);

查询结果会显示为：

+-------+------------+
| id    | code       |
+-------+------------+
| 00012 | 0000012345 |
+-------+------------+

MySQL 8.0 中的变化

1. 弃用通知：从 MySQL 8.0.17 开始，ZEROFILL 已被标记为弃用，未来版本可能会移除。

2. 替代方案：建议使用 LPAD 函数或应用程序逻辑来实现零填充效果：

   SELECT LPAD(column_name, desired_length, '0') FROM table_name;
   

3. 与显示宽度结合：ZEROFILL 需要与显示宽度（如 INT(5)）一起使用，但显示宽度本身在 MySQL 8.0 中也已变得不那么重要。

5.3 注意事项

• ZEROFILL 只影响显示格式，不影响实际存储的值
• 它不适用于 FLOAT 或 DOUBLE 类型
• 在 MySQL 8.0 中，考虑迁移到其他格式化方法，因为此功能可能在未来版本中移除

虽然 ZEROFILL 仍然可以在 MySQL 8.0 中使用，但建议在新应用中避免使用它，转而采用更灵活的格式化方法。

6. 主键

6.1 基本概念

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键（不意味着一个表中的主键只能添加一列，一个主键可以被添加到一列或者多列中【复合主键】）；主键所在的列通常是整数类型。

6.2 使用示例

• 创建表的时候直接在字段上指定主键

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.03 sec)mysql> desc test_key;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |  <= key 中 pri表示该字段是主键
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> show create table test_key \G;
*************************** 1. row ***************************Table: test_key
Create Table: CREATE TABLE `test_key` (`id` int unsigned NOT NULL COMMENT '这个是学生的学号',`name` varchar(20) NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

mysql> insert into test_key values (1,'刘备');
ERROR 1062 (23000): Duplicate entry '1' for key 'test_key.PRIMARY'

• 有了主键可以准确性的对数据进行增删查改

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.00 sec)mysql> update test_key set name='曹老板' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from test_key;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 张飞      |
|  2 | 曹老板    |
+----+-----------+
2 rows in set (0.00 sec)

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

alter table 表名 add primary key(字段列表)

• 删除主键

alter table 表名 drop 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.03 sec)mysql> desc pick_course;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int unsigned     | NO   | PRI | NULL    |       |
| course_id | int unsigned     | NO   | PRI | NULL    |       |
| score     | tinyint unsigned | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into pick_course values(12346,1,90);
Query OK, 1 row affected (0.00 sec)mysql> insert into pick_course values(12347,1,90);
Query OK, 1 row affected (0.01 sec)mysql> insert into pick_course values(12346,1,90); //主键冲突
ERROR 1062 (23000): Duplicate entry '12346-1' for key 'pick_course.PRIMARY'
mysql> insert into pick_course values(12346,1,80);
ERROR 1062 (23000): Duplicate entry '12346-1' for key 'pick_course.PRIMARY'

---

👍 如果对你有帮助，欢迎：

• 点赞 ⭐️
• 收藏 📌
• 关注 🔔