MySQL 表的约束：主键、自增长、唯一键、外键、空属性、默认值、列描述、零填充、

MySQL 表的约束：主键、自增长、唯一键、外键、空属性、默认值、列描述、零填充、

发布时间：2026/8/14 15:40:38
MySQL 表的约束：主键、自增长、唯一键、外键、空属性、默认值、列描述、零填充、 目录1. 空属性NULL2. 默认值defaultnot null 和 default 同时设置3. 列描述comment4. 零填充zerofill5. 主键primary key删除主键追加主键复合主primary key(x,x)6. 自增长auto_incrementAUTO_INCREMENT 获取函数索引7. 唯一键unique8. 外键foreign key外键命名如何理解外键约束综合案例9.真正约束字段的是数据类型但是数据类型约束很单一需要有一些额外的约束更好的保证数据的合法性从业务逻辑角度保证数据的正确性。比如有一个字段是邮箱要求是唯一的。表的约束很多这里主要介绍如下几个空值或非空默认值注释零填充主键自增唯一键。1. 空属性NULL两个值空值默认的和非空不为空。数据库默认字段基本都是字段为空但是实际开发时尽可能保证字段不为空因为数据为空没办法参与运算。mysql select null; ------ | NULL | ------ | NULL | ------ 1 row in set (0.00 sec) mysql select 1null; -------- | 1null | -------- | NULL | -------- 1 row in set (0.00 sec)例子创建一个班级表包含班级名和班级所在的教室。站在正常的业务逻辑中如果班级没有名字你不知道你在哪个班级如果教室名字可以为空就不知道在哪上课所以我们在设计数据库表的时候一定要在表中进行限制满足上面条件的数据就不能插入到表中。这就是约束。mysql create table myclass( - class_name varchar(20) not null, - class_room varchar(20) not null - ); 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 | | ---------------------------------------------------- 2 rows in set (0.01 sec) mysql insert into myclass(class_name,class_room) values(高三一班,301); Query OK, 1 row affected (0.01 sec) //插入数据时,没有给教室数据插入失败 mysql insert into myclass(class_name,class_room) values(高三二班); ERROR 1136 (21S01): Column count doesnt match value count at row 1 mysql select *from myclass; -------------------------- | class_name | class_room | -------------------------- | 高三一班 | 301 | -------------------------- 1 row in set (0.00 sec)2. 默认值default默认值某一种数据会经常性的出现某个具体的值可以在一开始就指定好在需要真实数据的时候用户可以选择性的使用默认值。mysql create table t11( - name varchar(20) not null, - age tinyint unsigned default 18, - gander char(1) default 男 - ); Query OK, 0 rows affected (0.02 sec) mysql desc t11; ----------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------------- | name | varchar(20) | NO | | NULL | | | age | tinyint unsigned | YES | | 18 | | | gander | char(1) | YES | | 男 | | ----------------------------------------------------- 3 rows in set (0.00 sec)默认值的生效数据在插入的时候不给该字段赋值就使用默认值mysql insert into t11(name) values(张三); Query OK, 1 row affected (0.00 sec) mysql select *from t11; ---------------------- | name | age | gander | ---------------------- | 张三 | 18 | 男 | ---------------------- 1 row in set (0.00 sec)not null 和 default 同时设置not null和defalut一般不需要同时出现因为default本身有默认值不会为空但也可以同时设置以防用户主动设置 nullmysql create table t13( - name varchar(20) not null default 用户 - ); Query OK, 0 rows affected (0.02 sec) mysql desc t13; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | name | varchar(20) | NO | | 用户 | | ----------------------------------------------- 1 row in set (0.00 sec)3. 列描述comment列描述注释没有实际含义专门用来描述字段会根据表创建语句保存用来给程序员或数据库管理员来进行了解。mysql create table t12( - 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)通过 desc 查看不到注释信息通过 show 可以看到mysql desc t12; ----------------------------------------------------- | 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 show create table t12\G; *************************** 1. row *************************** Table: t12 Create Table: CREATE TABLE t12 ( name varchar(20) NOT NULL COMMENT 用户名, age tinyint unsigned DEFAULT 18 COMMENT 用户的年龄, gender char(1) DEFAULT 男 COMMENT 用户的性别 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified4. 零填充zerofillmysql show create table tt3\G ***************** 1. row ***************** Table: tt3 Create Table: CREATE TABLE tt3 ( a int(10) unsigned DEFAULT NULL, b int(10) unsigned DEFAULT NULL ) ENGINEMyISAM DEFAULT CHARSETgbk 1 row in set (0.00 sec)可以看到整数类型10这个代表什么意思呢整型不是 4 字节吗这个 10 又代表什么呢其实没有零填充这个属性括号内的数字是毫无意义的。a 和 b 列就是前面插入的数据如下mysql insert into tt3 values(1,2); Query OK, 1 row affected (0.00 sec) mysql select * from tt3; ------------ | a | b | ------------ | 1 | 2 | ------------但是对列添加了零填充属性后显示的结果就有所不同了。修改 tt3 表的属性mysql alter table tt3 modify b int(5) unsigned zerofill; mysql show create table tt3\G *************************** 1. row *************************** Table: tt3 Create Table: CREATE TABLE tt3 ( a int(10) unsigned DEFAULT NULL b int(5) unsigned zerofill DEFAULT NULL, --具有了zerofill ) ENGINEMyISAM DEFAULT CHARSETgbk 1 row in set (0.00 sec)对 a 列添加了零填充属性再进行查找返回如下结果mysql select * from tt3; ---------------- | a | b | ---------------- | 1 | 00001 | ----------------这次可以看到 a 的值由原来的 1 变成 00001这就是零填充属性的作用如果宽度小于设定的宽度这里设置的是 5 自动填充 0。要注意的是这只是最后显示的结果在 MySQL 中实际存储的还是 1 。为什么是这样呢我们可以用十六进制函数来证明。mysql select b, hex(b) from tt3; --------------- | b | hex(b) | --------------- | 00001 | 1 | ---------------可以看出数据库内部存储的还是 1, 00001 只是设置了 zerofill 属性后的一种格式化输出而已。插入的值超过 5 位也能正常显示这个位数存在的意义就是设置为 zerofill 后输出显示对齐。mysql insert into tt3 values(1,11111111); Query OK, 1 row affected (0.00 sec) mysql select *from tt3; ---------------- | a | b | ---------------- | 1 | 00001 | | 1 | 11111111 | ---------------- 2 rows in set (0.00 sec)2的32次方 4,294,967,296一共 10 位数所以默认 int(10)5. 主键primary key主键用来唯一的约束该字段里面的数据不能重复不能为空一张表中最多只能有一个主键主键所在的列通常是整数类型。创建表的时候直接在字段上指定主键mysql create table t16( - id int unsigned primary key, -- 学号为主键 - name varchar(20) not null - ); Query OK, 0 rows affected (0.01 sec) mysql desc t16; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | ------------------------------------------------ 2 rows in set (0.00 sec)主键约束主键对应的字段中不能重复一旦重复操作失败。mysql insert into t16 values(1,张三); Query OK, 1 row affected (0.00 sec) mysql insert into t16 values(1,李四); ERROR 1062 (23000): Duplicate entry 1 for key t16.PRIMARY mysql insert into t16 values(2,李四); Query OK, 1 row affected (0.00 sec) mysql select *from t16; ------------ | id | name | ------------ | 1 | 张三 | | 2 | 李四 | ------------ 2 rows in set (0.00 sec)删除主键alter table 表名 drop pirmary key;mysql alter table t16 drop primary key; Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql desc t16; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int unsigned | NO | | NULL | | | name | varchar(20) | NO | | NULL | | ------------------------------------------------ 2 rows in set (0.00 sec)追加主键alter table 表名 add primary key(字段列表);mysql alter table t16 add primary key(id); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc t16; ------------------------------------------------ | Field | Type | Null | Key | Default | Extra | ------------------------------------------------ | id | int unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | ------------------------------------------------ 2 rows in set (0.01 sec)复合主primary key(x,x)在创建表的时候在所有字段之后使用主键字段列表来创建主键如果有多个字段作为主键可以使用复合主键。mysql create table t17( - id int unsigned comment 学号, - course_id char(10) comment 课程代码, - primary key(id,course_id) - ); Query OK, 0 rows affected (0.02 sec) mysql desc t17; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int unsigned | NO | PRI | NULL | | | course_id | char(10) | NO | PRI | NULL | | ---------------------------------------------------- 2 rows in set (0.00 sec)主键冲突mysql insert into t17 values(1,101); Query OK, 1 row affected (0.00 sec) mysql insert into t17 values(2,101); Query OK, 1 row affected (0.00 sec) // 主键冲突 mysql insert into t17 values(1,101); ERROR 1062 (23000): Duplicate entry 1-101 for key t17.PRIMARY mysql select *from t17; --------------- | id | course_id | --------------- | 1 | 101 | | 2 | 101 | --------------- 2 rows in set (0.00 sec)6. 自增长auto_incrementauto_increment当对应的字段不给值会自动的被系统触发系统会从当前字段中已经有的最大值 1操作得到一个新的不同的值。通常和主键搭配使用作为逻辑主键。自增长的特点:任何一个字段要做自增长前提是本身是一个索引key一栏有值自增长字段必须是整数一张表最多只能有一个自增长mysql create table t18( - id int unsigned primary key auto_increment, - name varchar(10) not null - ); Query OK, 0 rows affected (0.02 sec) mysql insert into t18(name) values(a); Query OK, 1 row affected (0.01 sec) mysql insert into t18(name) values(a); Query OK, 1 row affected (0.00 sec) mysql insert into t18(id,name) values(1000,a); --指定id Query OK, 1 row affected (0.00 sec) mysql insert into t18(name) values(a); Query OK, 1 row affected (0.00 sec) mysql select *from t18; ------------ | id | name | ------------ | 1 | a | | 2 | a | | 1000 | a | | 1001 | a | --id从1001开始 ------------ 4 rows in set (0.00 sec)mysql show create table t18\G; *************************** 1. row *************************** Table: t18 Create Table: CREATE TABLE t18 ( id int unsigned NOT NULL AUTO_INCREMENT, name varchar(10) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci 1 row in set (0.00 sec)AUTO_INCREMENT 表示下一次插入的时候自增长的起始值。我们也可以在创建表的时候直接设置mysql create table t18( - id int unsigned primary key auto_increment, - name varchar(10) not null - )auto_increment1000; --起始值设为1000 Query OK, 0 rows affected (0.02 sec) mysql insert into t19(name) values(a); Query OK, 1 row affected (0.00 sec) mysql select *from t19; ------------ | id | name | ------------ | 1000 | a | ------------ 1 row in set (0.00 sec)AUTO_INCREMENT 获取函数在插入后获取上次插入的 AUTO_INCREMENT 的值批量插入获取的是第一个值不需要指定表。LAST_INSERT_ID() 是连接级别connection‑level的函数它返回的是当前连接中最近一次通过 AUTO_INCREMENT 生成的值跟具体是哪张表无关mysql select last_insert_id(); ------------------ | last_insert_id() | ------------------ | 1001 | ------------------ 1 row in set (0.00 sec)索引在关系数据库中索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快可快速访问数据库表中的特定信息。7. 唯一键unique一张表中往往有很多字段需要唯一性数据不能重复但是一张表中只能有一个主键唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多唯一键允许为空而且可以多个为空空字段不做唯一性比较。关于唯一键和主键的区别 我们可以简单理解成主键更多的是标识唯一性的。而唯一键更多的是保证在业务上不要和别的信息出现重复。比如在公司我们需要学生管理系统系统中有一个学生信息表表中有两列信息一个学号一个是电话号我们可以选择学号作为主键。但同时学生的电话号也不能重复这时候我们就可以把电话号设置为唯一键。mysql create table student( - id int unsigned primary key comment 学号, - telephone varchar(11) unique comment 电话号不能重复可以为空 - ); Query OK, 0 rows affected (0.02 sec) mysql insert into student(id,telephone) values(1,11111111111); Query OK, 1 row affected (0.00 sec) mysql insert into student(id,telephone) values(2,11111111111); --唯一键重复 ERROR 1062 (23000): Duplicate entry 11111111111 for key student.telephone mysql insert into student(id,telephone) values(2,11111111112); Query OK, 1 row affected (0.00 sec) mysql insert into student(id,telephone) values(3,null); Query OK, 1 row affected (0.01 sec) mysql insert into student(id,telephone) values(4,null); --唯一键允许都为null Query OK, 1 row affected (0.00 sec) mysql select *from student; ----------------- | id | telephone | ----------------- | 3 | NULL | | 4 | NULL | | 1 | 11111111111 | | 2 | 11111111112 | ----------------- 4 rows in set (0.00 sec)唯一键允许都插入 null因为 null 不参与计算8. 外键foreign key外键用于定义主表和从表之间的关系外键约束主要定义在从表上主表则必须是有主键约束或unique约束。当定义外键后要求外键列数据必须在主表的主键列存在或为null。foreign key字段名references 主表列创建主键表mysql create table myclass( - id int unsigned primary key, - name varchar(30) not null - ); Query OK, 0 rows affected (0.02 sec)创建从表mysql create table mystudent( - id int unsigned primary key, - name varchar(30) not null, - class_id int unsigned, - foreign key(class_id) references myclass(id) - ); Query OK, 0 rows affected (0.03 sec)正常插入数据mysql insert into myclass values(1,高二1班); Query OK, 1 row affected (0.00 sec) mysql insert into myclass values(2,高二2班); Query OK, 1 row affected (0.00 sec) mysql select *from myclass; ---------------- | id | name | ---------------- | 1 | 高二1班 | | 2 | 高二2班 | ---------------- 2 rows in set (0.00 sec)mysql insert into mystudent values(1,张三,1); Query OK, 1 row affected (0.00 sec) mysql insert into mystudent values(2,李四,2); Query OK, 1 row affected (0.01 sec) mysql select *from mystudent; ---------------------- | id | name | class_id | ---------------------- | 1 | 张三 | 1 | | 2 | 李四 | 2 | ---------------------- 2 rows in set (0.00 sec)插入一个班级号为 3 的学生因为没有这个班级所以插入不成功mysql insert into mystudent values(3,王五,3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test_db.mystudent, CONSTRAINT mystudent_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))删除外键字段但因为从表中还存在与之关联的数据所以无法删除mysql delete from myclass where id1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (test_db.mystudent, CONSTRAINT mystudent_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id))外键命名CONSTRAINT mystudent_ibfk_1 就是在给外键命名不去主动设置的话也会给我们默认设置的mysql show create table mystudent\G; *************************** 1. row *************************** Table: mystudent Create Table: CREATE TABLE mystudent ( id int unsigned NOT NULL, name varchar(30) NOT NULL, class_id int unsigned DEFAULT NULL, PRIMARY KEY (id), KEY class_id (class_id), CONSTRAINT mystudent_ibfk_1 FOREIGN KEY (class_id) REFERENCES myclass (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci 1 row in set (0.00 sec)如何理解外键约束首先我们承认这个世界上很多数据都是具有相关性的。理论上上面的例子我们不创建外键约束就正常建立学生表以及班级表该有的字段我们都有。此时在实际使用的时候可能会出现什么问题有没有可能插入的学生信息中有具体的班级但是该班级却没有在班级表中比如只开了 1班、2班但是在上课的学生里面竟然有 3 班的学生这个班目前并不存在这很明显是有问题的。因为此时两张表在业务上是有相关性的但是在业务上没有建立约束关系那么就可能出现问题。解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给MySQL去审核了提前告诉MySQL表之间的约束关系那么当用户插入不符合业务逻辑的数据的时候MySQL不允许你插入。综合案例9.有一个商店的数据记录客户及购物情况有以下三个表组成商品goods商品编号goods_id商品名goods_name单价unitprice商品类别category供应商provider客户customer客户号customer_id姓名name住址address邮箱email性别sex身份证card_id购买purchase购买订单号order_id客户号customer_id商品号goods_id购买数量nums要求每个表的主外键客户的姓名不能为空值邮箱不能重复客户的性别男女-- 创建数据库 mysql create database store; Query OK, 1 row affected (0.00 sec) -- 选择数据库 mysql use store; Database changed -- 创建商品表 mysql create table goods( - goods_id int primary key auto_increment comment 商品编号, - goods_name varchar(30) not null comment 商品名, - unitprice decimal(10,2) not null comment 单价, - category varchar(30) comment 商品类别, - provider varchar(64) comment 供应商 - ); Query OK, 0 rows affected (0.02 sec) -- 创建客户表 mysql create table customer( - customer_id int primary key auto_increment comment 客户编号, - name varchar(30) not null comment 客户姓名, - address varchar(256) comment 客户地址, - email varchar(64) unique key comment 客户邮箱唯一, - sex enum(男,女) not null comment 性别二选一, - card_id char(18) unique key comment 客户身份证唯一 - ); Query OK, 0 rows affected (0.04 sec) -- 创建购买表 mysql create table purchase( - order_id int primary key auto_increment comment 购买订单号, - customer_id int comment 客户编号, - goods_id int comment 商品号, - nums int default 0 comment 购买数量, - foreign key(customer_id) references customer(customer_id), - foreign key(goods_id) references goods(goods_id) - );