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

本文涉及的产品
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
云数据库 Tair(兼容Redis),内存型 2GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 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
相关文章
|
7天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
7天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
26 3
|
7天前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
14 1
|
17天前
|
存储 SQL NoSQL
|
17天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(8)作者——LJS[含MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;注意点及常见报错问题所对应的解决方法]
MySQL 创建、修改、跟新、重命名、删除视图等具体详步骤;举例说明注意点及常见报错问题所对应的解决方法
|
17天前
|
SQL NoSQL 关系型数据库
|
9天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
23 4
|
7天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法,包括安装并连接MySQL服务器、选择数据库、执行SQL语句(如插入、更新、删除和查询),以及将结果集返回到数组。通过具体示例代码,详细介绍了每一步的操作流程,帮助读者快速入门PHP与MySQL的交互。
22 1
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
61 3
Mysql(4)—数据库索引
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
82 1

相关产品

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