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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 Tair(兼容Redis),内存型 2GB
简介: 24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2)学不会你来砍我!!!

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

你的三连是我最大的动力

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

专栏跑道一

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

image.gif

专栏跑道二

➡️ 24 Network Security -LJS

image.gif

image.gif

image.gif

专栏跑道三


➡️ MYSQL REDIS Advance operation

image.gif

专栏跑道四

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

image.gif

专栏跑道五

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

image.png

专栏跑道六

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

image.gif

专栏跑道七

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

image.gif

image.gif

上节回顾





4. PRIMARY KEY 约束

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

4.2 关键字primary key

4.3 特点

  • 主键约束相当于唯一约束+非空约束的组合,主键约束列不允许重复,也不允许出现空值
  • image.gif 编辑
  • 一个表最多只能有一个主键约束,建立主键约束可以在列级别创建,也可以在表级别上创建。
  • 主键约束对应着表中的一列或者多列(复合主键)
  • 如果是多列组合的复合主键约束,那么这些列都不允许为空值,并且组合的值不允许重复
  • MySQL的主键名总是PRIMARY,就算自己命名了主键约束名也没用
  • 创建主键约束时,系统默认会在所在的列或列组合上建立对应的主键索引
  • (能够根据主键查询的,就根据主键查询,效率更高)。

  • 如果删除主键约束了,主键约束对应的索引自动删除了。
  • 需要注意的一点是,不要修改主键字段的值

  • 因为主键数据记录的唯一标识

  • 如果修改了主键的值,就有可能会破坏数据的完整性。
  • 4.4 添加主键约束
  • (1)建表时指定主键约束
create table 表名称(    
    字段名  数据类型  primary key, //列级模式     
    字段名  数据类型,       
    字段名  数据类型   ); 
create table 表名称(     
    字段名  数据类型,     
    字段名  数据类型,       
    字段名  数据类型,     
[constraint 约束名] primary key(字段名)); //表级模式
  • image.gif
  • 举例:
create table temp(     
    id int primary key,        
    name varchar(20) );
  • image.gif
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)
  • image.gif
insert into temp values(1,'张三');
//成功 
insert into temp values(2,'李四');
//成功
  • image.gif
mysql> select * from temp; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | 张三 | 
|  2 | 李四 | 
+----+------+ 
2 rows in set (0.00 sec)
  • image.gif
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,'张三');
//成功
  • image.gif
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
  • image.gif
mysql> select * from temp; 
+----+------+ 
| id | name | 
+----+------+ 
|  1 | 张三 | 
|  2 | 李四 | 
|  3 | 张三 | 
|  4 | NULL | 
+----+------+ 
4 rows in set (0.00 sec)
  • image.gif
  • 举例:一个表建立两个主键约束
CREATE TABLE emp4(     
    id INT PRIMARY KEY AUTO_INCREMENT ,     
    
    NAME VARCHAR(20) );
  • image.gif
  • 列级约束
create table temp(     
    id int primary key,     
    name varchar(20) primary key ); 
ERROR 1068 (42000): Multiple(多重的) primary key defined(定义)
  • image.gif
  • 表级约束
CREATE TABLE emp5( 
id INT NOT NULL AUTO_INCREMENT, 
NAME VARCHAR(20), pwd VARCHAR(15), 
CONSTRAINT emp5_id_pk PRIMARY KEY(id) );
  • image.gif
  • (2)建表后增加主键约束
ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); 
//字段列表可以是一个字段,也可以是多个字段,如果是多个字段的话,是复合主键 ;
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd)
  • image.gif

4.5 关于符合主键

create table 表名称(     
    字段名  数据类型,     
    字段名  数据类型,       
    字段名  数据类型,     
    primary key(字段名1,字段名2)  
//表示字段1和字段2的组合是唯一的,也可以有更多个字段 );
  • image.gif
  • 学生表
create table student(     
    sid int primary key,  //学号     
    sname varchar(20)     //学生姓名 
);
  • image.gif
  • 课程表
create table course(     
    cid int primary key,  //课程编号     
    cname varchar(20)     //课程名称 
);
  • image.gif
  • 选课表
create table student_course(     
    sid int,     
    cid int,     
    score int,     
    primary key(sid,cid)  
    //复合主键 
);
  • image.gif
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)
  • image.gif
CREATE TABLE emp6( 
   id INT NOT NULL, 
   NAME VARCHAR(20), 
   pwd VARCHAR(15), 
    CONSTRAINT emp7_pk PRIMARY KEY(NAME,pwd) 
);
  • image.gif

4.6 删除主键约束

alter table 表名称 drop primary key;
  • image.gif

举例:

ALTER TABLE student DROP PRIMARY KEY; 
ALTER TABLE emp5 DROP PRIMARY KEY;
  • image.gif
  • 注意:
  • 删除主键约束,不需要指定主键名,因为一个表只有一个主键,删除主键约束后,非空还存在

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不是
整数类型
  • image.gif

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(字段名) 
);
  • image.gif
  • 举例
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)
  • image.gif
  • (2)建表后
alter table 表名称 modify 字段名 数据类型 auto_increment;
  • image.gif
  • 举例
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)
  • image.gif

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)
  • image.gif

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 
);
  • image.gif
  • 插入4个空值
INSERT INTO test1 VALUES(0),(0),(0),(0);
  • image.gif
  • 查询数据表test1中的数据
mysql> SELECT * FROM test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
+----+ 
4 rows in set (0.00 sec)
  • image.gif
  • 删除id为4的记录
DELETE FROM test1 WHERE id = 4;
  • image.gif
  • 再次插入一个空值
INSERT INTO test1 VALUES(0);
  • image.gif
  • 查询此时数据表test1中的数据
mysql> SELECT * FROM test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  5 | 
+----+ 
4 rows in set (0.00 sec)
  • image.gif
  • 虽然删除了id为4的记录,但是再次插入空值时,并没有重用被删除的4,而是分配了 5删除id为5的记录,
DELETE FROM test1 where id=5;
  • image.gif
  • 重启数据库,重新插入一个空值。
INSERT INTO test1 values(0);
  • image.gif
  • 再次查询数据表test1中的数据
mysql> SELECT * FROM test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
+----+ 
4 rows in set (0.00 sec)
  • image.gif
  • 可以看出,新插入的0值分配的是4,按照重启前的操作逻辑,此处应该分配6。出现上述结果的主 要原因是自增主键没有持久化

知识补充

  • MySQL 5.7系统中,对于自增主键的分配规则,是由InnoDB数据字典内部一个计数器来决定的,而该计数器只在内存中维护 ,并不会持久化到磁盘中
  • 所以当数据库重启时, 该计数器会被初始化
mysql> SELECT * FROM test1; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  6 | 
+----+ 
4 rows in set (0.00 sec)
  • image.gif
  • 上述结果可以看出,自增变量已经持久了。

小结

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




相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
19天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
19天前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
在项目中,为了解决Redis与Mysql的数据一致性问题,我们采用了多种策略:对于低一致性要求的数据,不做特别处理;时效性数据通过设置缓存过期时间来减少不一致风险;高一致性但时效性要求不高的数据,利用MQ异步同步确保最终一致性;而对一致性和时效性都有高要求的数据,则采用分布式事务(如Seata TCC模式)来保障。
53 14
|
19天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
61 3
|
19天前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
26 1
|
27天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。通过具体案例,读者可以了解如何准备环境、下载源码、编译安装、配置服务及登录 MySQL。编译源码安装虽然复杂,但提供了更高的定制性和灵活性,适用于需要高度定制的场景。
75 3
|
28天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。
本文介绍了在 CentOS 7 中通过编译源码安装 MySQL 数据库的详细步骤,并与使用 RPM 包安装进行了对比。内容涵盖准备工作、下载源码、编译安装、配置服务、登录设置及实践心得,帮助读者根据需求选择最适合的安装方法。
50 2
|
29天前
|
SQL NoSQL 关系型数据库
2024Mysql And Redis基础与进阶操作系列(13)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
MYSQL日志之详解如何配置查看二进制、查询及慢查询日志;备份与恢复等具体详解步骤;举例说明、注意点及常见报错问题所对应的解决方法
2024Mysql And Redis基础与进阶操作系列(13)作者——LJS[你个小黑子这都还学不会嘛?你是真爱粉嘛?真是的 ~;以后请别侮辱我家鸽鸽]
|
29天前
|
存储 SQL NoSQL
|
29天前
|
存储 SQL NoSQL
2024Mysql And Redis基础与进阶操作系列(10)作者——LJS[你个IKUN还学不会嘛?你是真爱粉嘛?真是的 ~;以后别侮辱我家鸽鸽]
Mysql And Redis基础与进阶操作系列之存储函数和MySQL 触发器等具体举例以及详解步骤;注意点及常见报错问题所对应的解决方法]
|
19天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
34 1

相关产品

  • 云数据库 Tair(兼容 Redis)
  • 云数据库 RDS MySQL 版