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

本文涉及的产品
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介: 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根据重做日志中的信息来初始化计数器的内存值




相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
5月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
2月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
157 10
|
5月前
|
Java 关系型数据库 MySQL
在Linux操作系统上设置JDK、Tomcat、MySQL以及J2EE后端接口的部署步骤
让我们总结一下,给你的Linux操作系统装备上最强的军队,需要先后装备好JDK的弓箭,布置好Tomcat的阵地,再把MySQL的物资原料准备好,最后部署好J2EE攻城车,那就准备好进军吧,你的Linux军团,无人可挡!
126 18
|
6月前
|
关系型数据库 MySQL Linux
CentOS 7系统下详细安装MySQL 5.7的步骤:包括密码配置、字符集配置、远程连接配置
以上就是在CentOS 7系统下安装MySQL 5.7的详细步骤。希望这个指南能帮助你顺利完成安装。
1470 26
|
7月前
|
Ubuntu 关系型数据库 MySQL
容器技术实践:在Ubuntu上使用Docker安装MySQL的步骤。
通过以上的操作,你已经步入了Docker和MySQL的世界,享受了容器技术给你带来的便利。这个旅程中你可能会遇到各种挑战,但是只要你沿着我们划定的路线行进,你就一定可以达到目的地。这就是Ubuntu、Docker和MySQL的灵魂所在,它们为你开辟了一条通往新探索的道路,带你亲身感受到了技术的力量。欢迎在Ubuntu的广阔大海中探索,用Docker技术引领你的航行,随时准备感受新技术带来的震撼和乐趣。
295 16
|
7月前
|
人工智能 关系型数据库 MySQL
解决MySQL自增id用尽的问题
本文介绍了解决文章点击记录表(`article_click_record`)数据量激增问题的方案。由于用户量大,每天新增约400万条记录,导致表id接近溢出(2,100,000,000),且占用空间超320G。解决方案包括:1) 新建`article_click_record_new`表,将id类型改为BIGINT以避免溢出;2) 过渡阶段同时写入新旧表,待旧表id溢出后切换至新表;3) 定时清理过期数据或转移旧表内容。实现方式涉及修改相关接口和服务逻辑,确保业务平稳过渡。
165 5
|
6月前
|
存储 Oracle 关系型数据库
MySQL 8.4 配置SSL组复制(八个步骤)
MySQL 8.4 配置SSL组复制(八个步骤)
336 0
|
7月前
|
缓存 NoSQL 关系型数据库
Redis和Mysql如何保证数据⼀致?
1. 先更新Mysql,再更新Redis,如果更新Redis失败,可能仍然不⼀致 2. 先删除Redis缓存数据,再更新Mysql,再次查询的时候在将数据添加到缓存中 这种⽅案能解决1 ⽅案的问题,但是在⾼并发下性能较低,⽽且仍然会出现数据不⼀致的问题,⽐如线程1删除了 Redis缓存数据,正在更新Mysql,此时另外⼀个查询再查询,那么就会把Mysql中⽼数据⼜查到 Redis中 1. 使用MQ异步同步, 保证数据的最终一致性 我们项目中会根据业务情况 , 使用不同的方案来解决Redis和Mysql的一致性问题 : 1. 对于一些一致性要求不高的场景 , 不做处理例如 : 用户行为数据 ,
|
7月前
|
SQL 关系型数据库 MySQL
【YashanDB 知识库】YashanDB 支持 MySQL 多表更新语句的解决方法
【YashanDB 知识库】YashanDB 支持 MySQL 多表更新语句的解决方法
|
7月前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】YashanDB 支持MySQL多表更新语句的解决方法
本文介绍了在YashanDB中处理MySQL多表更新语句的方法。由于YashanDB默认不支持多表更新(报错YAS-04344),可通过启用兼容性参数`SQL_PLUGIN = 'MYSQL'`解决,或改写SQL语句实现相同功能。文章通过具体示例说明了多种改写方法,包括根据共同列更新单个或多个字段、添加过滤条件以及基于多个共同列的更新场景。涉及的表结构和数据展示了实际操作过程,帮助用户顺利迁移和使用YashanDB。

相关产品

  • 云数据库 Tair(兼容 Redis)
  • 云数据库 RDS MySQL 版
  • 推荐镜像

    更多