5. 自增列：AUTO_INCREMENT

5. 1 作用

某个字段的值自增

5. 2 关键字

auto_increment

5. 3 特点和要求

（ 1 ）一个表最多只能有一个自增长列

（ 2 ）当需要产生唯一标识符或顺序值时，可设置自增长

（ 3 ）自增长列约束的列必须是键列（主键列，唯一键列）

（ 4 ）自增约束的列的数据类型必须是整数类型

（ 5 ）如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接

赋值为具体值。

错误演示：

create table employee(
eid int auto_increment,
ename varchar(20)
);
# ERROR 1075 (42000): Incorrect table definition; there can be only one auto column
and it must be defined as a key

create table employee(
eid int primary key,
ename varchar(20) unique key auto_increment
);
# ERROR 1063 (42000): Incorrect column specifier for column 'ename' 因为ename不是整数类
型

5. 4 如何指定自增约束

（ 1 ）建表时

create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);

create table employee(
eid int primary key auto_increment,
ename varchar(20)
);

mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

（ 2 ）建表后

alter table 表名称 modify 字段名 数据类型 auto_increment;

例如：

create table employee(
eid int primary key ,
ename varchar(20)
);

alter table employee modify eid int auto_increment;

mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

5. 5 如何删除自增约束

#alter table 表名称 modify 字段名 数据类型 auto_increment;#给这个字段增加自增约束
alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除

alter table employee modify eid int;

mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5. 6 MySQL 8. 0 新特性—自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。在MySQL 5.7版本中，测试步骤如

下：创建的数据表中包含自增主键的id字段，语句如下：

CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT
);

插入 4 个空值，执行如下：

INSERT INTO test1
VALUES(0),(0),(0),(0);

查询数据表test1中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

删除id为 4 的记录，语句如下：

DELETE FROM test1 WHERE id = 4;

再次插入一个空值，语句如下：

INSERT INTO test1 VALUES(0);

查询此时数据表test1中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 5 |
+----+
4 rows in set (0.00 sec)

从结果可以看出，虽然删除了id为 4 的记录，但是再次插入空值时，并没有重用被删除的 4 ，而是分配了5 。删除id为 5 的记录，结果如下：

DELETE FROM test1 where id=5;

重启数据库，重新插入一个空值。

INSERT INTO test1 values(0);

再次查询数据表test1中的数据，结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)

从结果可以看出，新插入的 0 值分配的是 4 ，按照重启前的操作逻辑，此处应该分配 6 。出现上述结果的主要原因是自增主键没有持久化。在MySQL 5. 7 系统中，对于自增主键的分配规则，是由InnoDB数据字典

内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。

在MySQL 8. 0 版本中，上述测试步骤最后一步的结果如下：

mysql> SELECT * FROM test1;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 6 |
+----+
4 rows in set (0.00 sec)

从结果可以看出，自增变量已经持久化了。

MySQL 8. 0 将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

6. FOREIGN KEY 约束

6. 1 作用

限定某个表的某个字段的引用完整性。

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

6. 2 关键字

FOREIGN KEY

6. 3 主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

6. 4 特点

（ 1 ）从表的外键列，必须引用/参考主表的主键或唯一约束的列

为什么？因为被依赖/被参考的值必须是唯一的

（ 2 ）在创建外键约束时，如果不给外键约束命名， 默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。

（ 3 ）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表

（ 4 ）删表时，先删从表（或先删除外键约束），再删除主表

（ 5 ）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据

（ 6 ）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（ 7 ）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR 1005 (HY000): Can’t create table’database.tablename’(errno: 150)”。

例如：都是表示部门编号，都是int类型。

（ 8 ） 当创建外键约束时，系统默认会在所在的列上建立对应的普通索引 。但是索引名是外键的约束名。（根据外键查询效率很高）

（ 9 ）删除外键约束后，必须手动删除对应的索引

6. 5 添加外键约束

（ 1 ）建表时

create table 主表名称(
字段 1 数据类型 primary key,
字段 2 数据类型
);
create table 从表名称(
字段 1 数据类型 primary key,
字段 2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

（ 2 ）建表后

create table dept( #主表
did int primary key, #部门编号
dname varchar( 50 ) #部门名称
);
create table emp(#从表
eid int primary key,  #员工编号
ename varchar( 5 ), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（ 1 ）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（ 2 ）删除表时，先删除从表emp，再删除主表dept

一般情况下，表与表的关联都是提前设计好了的，因此，会在创建表的时候就把外键约束定义好。不

过，如果需要修改表的设计（比如添加新的字段，增加新的关联关系），但没有预先定义外键约束，那

么，就要用修改表的方式来补充定义。

格式：

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段) [on update xx][on delete xx];

举例：

ALTER TABLE emp ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) REFERENCES dept(dept_id);

举例：

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int #员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意

alter table emp add foreign key (deptid) references dept(did);

6. 6 演示问题

（ 1 ）失败：不是键列

create table dept(
did int , #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是dept的did不是键列

（ 2 ）失败：数据类型不一致

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid char, #员工所在的部门
foreign key (deptid) references dept(did)
);
#ERROR 1215 (HY000): Cannot add foreign key constraint 原因是从表的deptid字段和主表的did字
段的数据类型不一致，并且要它俩的逻辑意义一致

（ 3 ）成功，两个表字段名一样

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
did int, #员工所在的部门
foreign key (did) references dept(did)
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
#是否重名没问题，因为两个did在不同的表中
);

（ 4 ）添加、删除、修改问题

create table dept(
did int primary key, #部门编号
dname varchar( 50 ) #部门名称
);
create table emp(
eid int primary key,  #员工编号
ename varchar( 5 ), #员工姓名
did int, #员工所在的部门
foreign key (did) references dept(did)
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
#是否重名没问题，因为两个did在不同的表中
);

insert into dept values( 1001 ,'教学部');
insert into dept values( 1003 , '财务部');
insert into emp values( 1 ,'张三', 1001 ); #添加从表记录成功，在添加这条记录时，要求部门表有 1001 部门
insert into emp values( 2 ,'李四', 1005 );#添加从表记录失败
ERROR 1452 ( 23000 ): Cannot add（添加） or update（修改） a child row: a foreign key
constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptid`)
REFERENCES `dept` (`did`)) 从表emp添加记录失败，因为主表dept没有 1005 部门

mysql> select * from dept;
+------+--------+
| did | dname |
+------+--------+
| 1001 | 教学部 |
| 1003 | 财务部 |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+-----+-------+--------+
| eid | ename | deptid |
+-----+-------+--------+
| 1 | 张三 | 1001 |
+-----+-------+--------+
1 row in set (0.00 sec)

update emp set deptid = 1002 where eid = 1;#修改从表失败
ERROR 1452 (23000): Cannot add（添加） or update（修改） a child row（子表的记录）: a
foreign key constraint fails（外键约束失败） (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1`
FOREIGN KEY (`deptid`) REFERENCES `dept` (`did`)) #部门表did字段现在没有1002的值，所以员工
表中不能修改员工所在部门deptid为1002
update dept set did = 1002 where did = 1001;#修改主表失败
ERROR 1451 (23000): Cannot delete（删除） or update（修改） a parent row（父表的记录）: a
foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY
(`deptid`) REFERENCES `dept` (`did`)) #部门表did的1001字段已经被emp引用了，所以部门表的1001字
段就不能修改了。
update dept set did = 1002 where did = 1003;#修改主表成功 因为部门表的1003部门没有被emp表引
用，所以可以修改

delete from dept where did=1001; #删除主表失败
ERROR 1451 (23000): Cannot delete（删除） or update（修改） a parent row（父表记录）: a
foreign key constraint fails (`atguigudb`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY
(`deptid`) REFERENCES `dept` (`did`)) #因为部门表did的1001字段已经被emp引用了，所以部门表的
1001字段对应的记录就不能被删除

总结：约束关系是针对双方的

添加了外键约束后，主表的修改和删除数据受约束
添加了外键约束后，从表的添加和修改数据受约束
在从表上建立外键，要求主表必须存在
删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

MySQL数据库，从入门到精通：第十三篇——MySQL数据表约束详解（三）

5. 自增列：AUTO_INCREMENT

5. 1 作用

5. 2 关键字

5. 3 特点和要求

5. 4 如何指定自增约束

5. 5 如何删除自增约束

5. 6 MySQL 8. 0 新特性—自增变量的持久化

6. FOREIGN KEY 约束

6. 1 作用

6. 2 关键字

6. 3 主表和从表/父表和子表

6. 4 特点

6. 5 添加外键约束

6. 6 演示问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL数据库，从入门到精通：第十三篇——MySQL数据表约束详解（三）

5. 自增列：AUTO_INCREMENT

5. 1 作用

5. 2 关键字

5. 3 特点和要求

5. 4 如何指定自增约束

5. 5 如何删除自增约束

5. 6 MySQL 8. 0 新特性—自增变量的持久化

6. FOREIGN KEY 约束

6. 1 作用

6. 2 关键字

6. 3 主表和从表/父表和子表

6. 4 特点

6. 5 添加外键约束

6. 6 演示问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像