2024Mysql And Redis基础与进阶操作系列（4-2）作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]

2024-11-04 8

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

Redis 开源版，标准版 2GB

简介： 24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法（4-2）学不会你来砍我！！！

欢迎各位彦祖与热巴畅游本人专栏与博客

你的三连是我最大的动力

以下图片仅代表专栏特色 [点击箭头指向的专栏名即可闪现]

专栏跑道一

➡️网络空间安全——全栈前沿技术持续深入学习

专栏跑道二

➡️ 24 Network Security -LJS

专栏跑道三

➡️ MYSQL REDIS Advance operation

专栏跑道四

➡️HCIP；H3C-SE;CCIP——LJS[华为、华三、思科高级网络]

专栏跑道五

➡️RHCE-LJS[Linux高端骚操作实战篇]

专栏跑道六

➡️数据结构与算法[考研+实际工作应用+C程序设计]

专栏跑道七

➡️RHCSA-LJS[Linux初级及进阶骚技能]

上节回顾

4. PRIMARY KEY 约束

4.1 作用:用来唯一标识表中的一行记录。

4.2 关键字primary key

4.3 特点

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值。
编辑

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建。

主键约束对应着表中的一列或者多列（复合主键）

如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用

当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引
（能够根据主键查询的，就根据主键查询，效率更高)。
如果删除主键约束了，主键约束对应的索引就自动删除了。

需要注意的一点是，不要修改主键字段的值。
因为主键是数据记录的唯一标识，
如果修改了主键的值，就有可能会破坏数据的完整性。

4.4 添加主键约束
(1)建表时指定主键约束

create table 表名称(    
    字段名  数据类型  primary key, //列级模式     
    字段名  数据类型,       
    字段名  数据类型   ); 
create table 表名称(     
    字段名  数据类型,     
    字段名  数据类型,       
    字段名  数据类型,     
[constraint 约束名] primary key(字段名)); //表级模式

举例：

create table temp(     
    id int primary key,        
    name varchar(20) );

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

insert into temp values(1,'张三');
//成功 
insert into temp values(2,'李四');
//成功

mysql> select * from temp; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | 张三 | 
|  2 | 李四 | 
+----+------+ 
2 rows in set (0.00 sec)

insert into temp values(1,'张三');
//失败 
ERROR 1062 (23000): Duplicate（重复） entry（键入，输入） '1' for key 'PRIMARY' 
insert into temp values(1,'王五');
//失败 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into temp values(3,'张三');
//成功

mysql> select * from temp; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | 张三 | 
|  2 | 李四 | 
|  3 | 张三 | 
+----+------+ 
3 rows in set (0.00 sec)
insert into temp values(4,null);
//成功 
insert into temp values(null,'李琦');
//失败 
ERROR 1048 (23000): Column 'id' cannot be null

mysql> select * from temp; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | 张三 | 
|  2 | 李四 | 
|  3 | 张三 | 
|  4 | NULL | 
+----+------+ 
4 rows in set (0.00 sec)

举例：一个表建立两个主键约束

CREATE TABLE emp4(     
    id INT PRIMARY KEY AUTO_INCREMENT ,     
    
    NAME VARCHAR(20) );

列级约束

create table temp(     
    id int primary key,     
    name varchar(20) primary key ); 
ERROR 1068 (42000): Multiple（多重的） primary key defined（定义）

表级约束

CREATE TABLE emp5( 
id INT NOT NULL AUTO_INCREMENT, 
NAME VARCHAR(20), pwd VARCHAR(15), 
CONSTRAINT emp5_id_pk PRIMARY KEY(id) );

(2)建表后增加主键约束

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 
//字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键 ;
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd)

4.5 关于符合主键

create table 表名称(     
    字段名  数据类型,     
    字段名  数据类型,       
    字段名  数据类型,     
    primary key(字段名1,字段名2)  
//表示字段1和字段2的组合是唯一的，也可以有更多个字段 );

学生表

create table student(     
    sid int primary key,  //学号     
    sname varchar(20)     //学生姓名 
);

课程表

create table course(     
    cid int primary key,  //课程编号     
    cname varchar(20)     //课程名称 
);

选课表

create table student_course(     
    sid int,     
    cid int,     
    score int,     
    primary key(sid,cid)  
    //复合主键 
);

insert into student values(1,'张三'),(2,'李四'); 
insert into course values(1001,'Java'),(1002,'MySQL'); 
mysql> select * from student; 
+-----+-------+ 
| sid | sname | 
+-----+-------+ 
|   1 | 张三  | 
|   2 | 李四  | 
+-----+-------+ 
2 rows in set (0.00 sec) 
mysql> select * from course; 
+------+-------+ 
| cid  | cname | 
+------+-------+ 
| 1001 | Java  | 
| 1002 | MySQL | 
+------+-------+ 
2 rows in set (0.00 sec) 
insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),(2,1002,56); 
mysql> select * from student_course; 
+-----+------+-------+ 
| sid | cid  | score | 
+-----+------+-------+ 
|   1 | 1001 |    89 |
|   1 | 1002 |    90 |
|   2 | 1001 |    88 |
|   2 | 1002 |    56 | 
+-----+------+-------+ 
4 rows in set (0.00 sec) 
insert into student_course values(1, 1001, 100); 
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY' 
mysql> desc student_course; 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| sid   | int(11) | NO   | PRI | NULL    |       | 
| cid   | int(11) | NO   | PRI | NULL    |       | 
| score | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

CREATE TABLE emp6( 
   id INT NOT NULL, 
   NAME VARCHAR(20), 
   pwd VARCHAR(15), 
    CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd) 
);

4.6 删除主键约束

alter table 表名称 drop primary key;

举例：

ALTER TABLE student DROP PRIMARY KEY; 
ALTER TABLE emp5 DROP PRIMARY KEY;

注意：
删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在。

5. 自增列 AUTO_INCREMENT

5.1 作用某个字段的值自增

5.2 关键字

auto_increment

5.3 特点和要求

一个表最多只能有一个自增长列设置自增长

当需要产生唯一标识符或顺序值时，可列

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

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

如果自增列指定了 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) 
); 
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> SELECT * FROM test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  6 | 
+----+ 
4 rows in set (0.00 sec)

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

小结

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