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

MySQL表约束

news 2025/8/14 8:19:56

目录

  • 表的约束
    • 空属性
    • 默认值
    • 列描述
    • zerofill
    • 主键
    • 自增长
    • 唯一键
    • 外键

表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

空属性

  • 两个值：null（默认的）和not null(不为空)
  • 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办
    法参与运算    。
mysql> select null;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)mysql> select null+10;//无法参与运算,null就表示没有，''表示一个已知且确定的空值（长度为0的字符串）
+---------+
| null+10 |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

案例：

mysql> create table class(-> class_name varchar(20) not null,//设置为非空-> class_room varchar(20) not null//设置为非空-> );
Query OK, 0 rows affected (0.01 sec)mysql> insert into class (class_name,class_room) values ('math','12');
Query OK, 1 row affected (0.01 sec)mysql> insert into class (class_name,class_room) values ('','13');
Query OK, 1 row affected (0.00 sec)mysql> insert into class (class_room) values ('13');
ERROR 1364 (HY000): Field 'class_name' doesn't have a default value 
mysql> insert into class (class_name,class_room) values (null,'13');
ERROR 1048 (23000): Column 'class_name' cannot be null//设置了not null之后，不能为null

生活例子： 注册时，“用户名”这个格子后面标了红色的星号 *（表示必填项）。你不填用户名，系统就不让你注册成功（报错）。

默认值

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

mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | varchar(20)      | NO   |     | NULL    |       |
| age   | int(10) unsigned | YES  |     | 10      |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t1 (name) values('Tom');//使用默认值
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (name,age) values('Tom',null);//没有设置not null，所以可以为null
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (name,age) values('Tom',12);
Query OK, 1 row affected (0.00 sec)--注意：只有设置了default的列，才可以在插入值的时候，对列进行省略

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

生活例子： 注册时，“性别”这个格子。如果系统设置了一个默认值“保密”。如果你不选择性别，系统提交时就会自动帮你选上“保密”。

列描述

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

mysql> create table tt12 (
-> name varchar(20) not null comment '姓名',
-> age tinyint unsigned default 0 comment '年龄',
-> sex char(2) default '男' comment '性别'
-> );
--注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空

zerofill

刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。通过show看看t1表的建表语句：

mysql> create table t1(-> a int,-> b int);
Query OK, 0 rows affected (0.01 sec)mysql> show create table t1\GTable: t1
Create Table: CREATE TABLE `t1` (`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL
) 
1 row in set (0.00 sec)//int括号里面的11是什么？

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

mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)//这里看起里确实没有什么差别

我们修改一下，设置zerofill约束

mysql> alter table t1 change a a int(5) zerofill;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0mysql> show create table t1\GTable: t1
Create Table: CREATE TABLE `t1` (`a` int(5) unsigned zerofill DEFAULT NULL,`b` int(11) DEFAULT NULL //int默认的zerofill值为11
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)mysql> insert into t1 values(1,2);
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from t1;
+-------+------+
| a     | b    |
+-------+------+
| 00001 |    2 |
| 00001 |    2 |
+-------+------+
2 rows in set (0.00 sec)

这次可以看到a的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢？我们可以用hex函数来证明。

mysql> select a ,hex(a) from t1;
+-------+--------+
| a     | hex(a) |
+-------+--------+
| 00001 | 1      |
| 00001 | 1      |
+-------+--------+
2 rows in set (0.03 sec)

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

主键

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

  • 创建表的时候直接在字段上指定主键
mysql> create table t1(-> id int unsigned primary key ,//主键不能为空-> name varchar(20)not null-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| 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 t1  values(1,'Tom');
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (name) values('Tom');   //主键不能为空
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> insert into t1  values(1,'Mary');  //主键不能重复
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into t1  values(null,'Lisa');  //主键不能为空
ERROR 1048 (23000): Column 'id' cannot be null
  • 当表创建好以后但是没有主键的时候，可以再次追加主键
alter table 表名 add primary key(字段列表)

mysql> create table t5( id int null);
Query OK, 0 rows affected (0.02 sec)//id列可以在被设置为主键之前为null,设置主键之后，会自动设置为not null
mysql> alter table t5 add primary key (id);  
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • 删除主键
alter table 表名 drop primary key;

mysql> alter table t5 drop primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> desc t5;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • 复合主键
    在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。
mysql> create table t1(-> id int unsigned,-> course char(12),-> score tinyint unsigned,-> primary key(id,course) //可以通过这个方式设置primary key-> );
Query OK, 0 rows affected (0.02 sec)mysql> desc t1;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id     | int(10) unsigned    | NO   | PRI | NULL    |       |
| course | char(12)            | NO   | PRI | NULL    |       |
| score  | tinyint(3) unsigned | YES  |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> insert into t1 (id,course) values(1,'math');
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (id,course) values(2,'chinese');
Query OK, 1 row affected (0.00 sec)//id和course的组合是主键，所以保证id和course的组合的唯一性就可以了。
mysql> insert into t1 (id,course) values(3,'chinese');
Query OK, 1 row affected (0.00 sec)//主键冲突
mysql> insert into t1 (id,course) values(1,'math');
ERROR 1062 (23000): Duplicate entry '1-math' for key 'PRIMARY'

自增长

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:
任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
自增长字段必须是整数
一张表最多只能有一个自增长

mysql> desc t1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)//默认id从序号1开始增长
mysql> insert into t1 (name)values('Tom');
Query OK, 1 row affected (0.00 sec)mysql> insert into t1 (name)values('Mary');
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 (id,name)values(5,'Lisa');
Query OK, 1 row affected (0.01 sec)//从5开始增加
mysql> insert into t1 (name)values('Joe');
Query OK, 1 row affected (0.00 sec)mysql> select* from t1;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | Mary |
|  5 | Lisa |
|  6 | Joe  |
+----+------+
4 rows in set (0.01 sec)
  • 在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

mysql> insert into t1 (name)values('Tmme');
Query OK, 1 row affected (0.00 sec)mysql> select* from t1;
+----+------+
| id | name |
+----+------+
|  1 | Tom  |
|  2 | Mary |
|  5 | Lisa |
|  6 | Joe  |
|  7 | Tmme |
+----+------+
5 rows in set (0.00 sec)mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

唯一键

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)
比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。
一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

mysql> create table t1(-> id int unsigned unique,-> name varchar(12)-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc t1-> ;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  | UNI | NULL    |       |
| name  | varchar(12)      | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)mysql> insert into t1 (id,name)values(1,'Tom');
Query OK, 1 row affected (0.01 sec)//unique保证唯一性，不可以重复
mysql> insert into t1 (id,name)values(1,'Tom');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'//unique可以为null,主键不能为空
mysql> insert into t1 (id,name)values(null,'Lisa');
Query OK, 1 row affected (0.00 sec)mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | Tom  |
| NULL | Lisa |
+------+------+
2 rows in set (0.00 sec)

外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。
语法：

foreign key (字段名) references 主表(列)

案例：

在这里插入图片描述

  • 创建主表
mysql> create table myclass(-> id int primary key,-> name varchar(12));
Query OK, 0 rows affected (0.01 sec)
  • 创建从表
mysql> create table stu(-> id int primary key,-> class_id int not null,-> name varchar(20) not null,-> foreign key (class_id) references myclass(id)); //设置外键
Query OK, 0 rows affected (0.03 sec)
  • 插入数据
mysql> insert into myclass values(10,'C++'),(20,'Java');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> insert into stu values(1,10,'Tom'),(2,20,'Joe');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0mysql> select * from stu;
+----+----------+------+
| id | class_id | name |
+----+----------+------+
|  1 |       10 | Tom  |
|  2 |       20 | Joe  |
+----+----------+------+
2 rows in set (0.00 sec)mysql> select * from myclass;
+----+------+
| id | name |
+----+------+
| 10 | C++  |
| 20 | Java |
+----+------+
2 rows in set (0.00 sec)
  • 插入班级id为null，比如来了一个学生，目前还没有分配班级

mysql> insert into stu values(3,null,'Lisa');
Query OK, 1 row affected (0.00 sec)mysql> select * from stu;
+----+----------+------+
| id | class_id | name |
+----+----------+------+
|  1 |       10 | Tom  |
|  2 |       20 | Joe  |
|  3 |     NULL | Lisa |
+----+----------+------+
3 rows in set (0.00 sec)
  • 插入一个班级号为30的学生，因为没有这个班级，所以插入不成功
mysql> insert into stu values(4,30,'Mees');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mytest`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `myclass` (`id`))

建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

http://www.lryc.cn/news/619625.html

相关文章：

  • Spring Boot项目中线程池的全面教程
  • 中高级餐饮服务食品安全员考试核心知识点汇总
  • Spring Boot初级概念及自动配置原理
  • Spring Boot 3 连接池最大连接数设置建议
  • sample_kol里配置为 deep sleep mode，则系统进入 STR
  • Spring、Spring MVC、Spring Boot与Spring Cloud的扩展点全面梳理
  • Python【算法中心 03】Docker部署Django搭建的Python应用流程实例（Docker离线安装配置+Django项目Docker部署）
  • django name ‘QueryDict‘ is not defined
  • 更改webpack默认配置项
  • Git Bash
  • 导轨焊接机器人：重塑高效精准焊接的新标杆
  • VUE3中的内置 API
  • amis表单较验
  • SpringCloud(1)
  • 从“存得对”到“存得准”：MySQL 数据类型与约束全景指南
  • opencv：直方图
  • Java pdf工具
  • 想要PDF翻译保留格式？用对工具是关键
  • java中数组和list的区别是什么?
  • 双屏加固笔记本电脑C156-2：坚固与高效的完美融合
  • 如何在 Ubuntu 24.04 LTS Noble Linux 上安装 FileZilla Server
  • Prompt工程师基础技术学习指南：从入门到实战
  • 为什么要使用消息队列呢？
  • STM32学习笔记10—DMA
  • 408每日一题笔记 41-50
  • 2023 年全国硕士研究生招生考试真题笔记
  • C语言零基础第15讲：字符函数和字符串函数
  • 一个接口多个实现类，如何动态调用
  • 长篇音频制作（小说自动配音）完整教程
  • 15.卷积神经网络