MySQL中列属性(主键、唯一键和自增等)使用实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL中列属性(主键、唯一键和自增等)使用实践

数据表中,真正约束字段的是数据类型。但是数据类型的约束很单一,需要有一些额外的约束,来更加保证数据的合法性。如主键、唯一、自增等。

查看表数据结构

  • 红色表示数据类型;
  • 蓝色表示列属性;


列属性(其实也是约束)有很多,大致如下:

NOT NULL/NULL,

DEFAULT,

PRIMARY KEY,

UNIKUE KEY,

AUTO_INCREMENT,

COMMENT


【1】空属性

其有两个值:null(默认值) 和 not null (不为空) 。虽然默认的数据库基本字段为空,但是生产环境中尽可能保证字段都不为空。空数据没有意义,且无法参加运算。

select 1+'hello',null,1+null;




【2】列描述

comment:描述,没有实际含义,为列增加文字说明,会随着表创建一起储存起来。

创建表的时候设置描述

CREATE TABLE `tb_sys_user` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(32) DEFAULT NULL COMMENT '名字',
  `mobile` varchar(12) DEFAULT NULL COMMENT '手机号',
  `user_code` varchar(32) DEFAULT NULL COMMENT '用户编码',
  `password` varchar(100) DEFAULT NULL COMMENT '密码',
  `sex` varchar(5) DEFAULT NULL COMMENT '性别',
  `age` int(5) DEFAULT NULL COMMENT '年龄',
  `address` varchar(50) DEFAULT NULL COMMENT '地址',
  `field1` varchar(255) DEFAULT NULL COMMENT '保存用户头像-saveName',
  `field2` varchar(255) DEFAULT NULL COMMENT '冗余字段2',
  `field3` varchar(255) DEFAULT NULL COMMENT '冗余字段3',
  `field4` varchar(255) DEFAULT NULL COMMENT '冗余字段4',
  `field5` varchar(255) DEFAULT NULL COMMENT '冗余字段5',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_mobile` (`mobile`) USING BTREE,
  UNIQUE KEY `unique_code` (`user_code`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COMMENT='用户表'

修改列的时候添加描述

alter table equipment.tb_sys_user modify column  
name varchar(32) DEFAULT NULL comment '名字'

【3】默认值

① default

某一种数据会经常性的出现某个具体的值,可以在一开始指定好,用户可以在使用的时候选择性的使用默认值或插入新数据。

create table my_default(
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;
desc my_default;
# gender为null
insert into my_default values('tom',null);
# default,gender取默认值
insert into my_default values('lily',DEFAULT);
select * from my_default;

当不对gender列进行插入的时候,gender将会使用默认值

# 指定列,gender取默认值
insert into my_default (name) VALUE ('lir');
select * from my_default;

可见,指定列 与 values使用default 两种方式都可以使具有默认值的列,在插入数据的时候使用默认值。

【4】主键primary key

一张表只能有一个字段可以使用对应的键,用来唯一的约束该字段里面的数据,不能重复。主键本身一定不为空,且不允许重复。

① 列后面

create table my_default(
id int PRIMARY KEY,--使用关键字
name VARCHAR(20) not null unique key ,
gender varchar(4) DEFAULT '男'
)charset utf8;

② 表字段后面

在创建表的时候,在所有的字段之后使用 primary key(主键的字段 列表)来创建主键(如果有多个字段作为主键,称之为复合主键)

create table my_pri(
number char(10),
course char(10),
score TINYINT,
PRIMARY KEY(number,course)--复合主键
)charset utf8;
desc my_pri;

测试如下:

insert into my_pri VALUES('001','IT','10');--插入成功
insert into my_pri VALUES('001','IT','10');--插入失败
insert into my_pri VALUES('001','IT2','10');--插入成功

③ 创建表之后,为表追加主键

有两种方式:第一位修改表字段属性,第二直接追加。

第一种方式示例如下:

alter TABLE my_pri MODIFY number char(11) PRIMARY KEY

第二种方式示例如下:

alter table table_name add primary key(column) ;
## 增加复合主键
alter TABLE my_pri add PRIMARY KEY(number,course);
#使用关键字CONSTRAINT 且指定主键名字pk_score 
alter table my_pri add CONSTRAINT pk_score PRIMARY KEY(score)
#使用关键字CONSTRAINT 且使用默认名字 
alter table my_pri add CONSTRAINT  PRIMARY KEY(score)

drop删除主键:

#不能根据主键名字删除,因为只有一个主键
alter table my_pri drop PRIMARY KEY;

主键分类

在实际创建表的过程中,很少使用真实的业务 数据作为主键字段(业务主键,如课程号,学号。大部分时候是使用逻辑性字段(字段没有什么业务含义,值是什么都没有关系),将这种字段主键称之为逻辑主键。


【5】自动增长

① 自增长定义

auto_increment:当对应的字段,不给值、给默认值 、给null 值,会自动的被系统触发。


系统会从当前字段中已有的最大值进行+1操作,得到一个新的不同的字段,作为下一次插入数据是自增长字段的值。


若手动修改了auto_increment,且为向上修改,那么下一次值为auto_increment。自增长通常跟主键搭配。

自增长特征

① 任何一个字段要做自增长前提必须是一个索引;

② 自增长字段必须是整型数字;

③ 一个表至多有一个标识列

④ 标识列可以通过 SET auto_increment_increment=3;设置步长。

② 设置自增长属性

测试如下(创建失败),自增长字段必须是key(索引)

create table my_auto(
id int auto_increment ,
name varchar(20)
)charset utf8;

19cb750cd67c656fbb4e0a9b2219c83b.png

正确实例

create table my_auto(
id int primary key auto_increment ,
name varchar(20)
)charset utf8;

1fe61ea369174e128ceab689c0ec2f2c.png

③ 插入数据

insert into my_auto  values(null,'tom');
insert into my_auto  values('','tom2');--插入失败
insert into my_auto  values('null','tom3');--插入失败
insert into my_auto  values(DEFAULT,'tom4');

其中,第二、三将会插入失败–主键不能为空:


第一、四插入null 或者默认值,将会自动增长并插入:

如果自增长对应的字段输入了值,那么自增长失效。下一次插入数据,会获取最大值+1:

#指定id 为5
insert into my_auto  values(5,'tom5');
# tom6对应id应该为6
insert into my_auto  values(default,'tom6');
select * from my_auto;

a06f85df6d692452c5ceeed3c333c0c6.png

④ 修改自增长

自增长如果是设计到字段改变,必须先删除自增长,后增加(一张表只能有一个自增长)。修改当前自增长已存在的值,修改只能比最大值大,不能小(小不生效)。

语法如下:

alter table table_name auto_increment = 值 ;

测试向下修改:

#现在数据表已存在最大值7
alter table my_auto auto_increment=3;
show create table my_auto;
CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
--此时AUTO_INCREMENT=8 ,故向下修改无效

测试 向上修改:

#可以向上修改
alter table my_auto auto_increment=10;
show create table my_auto;
CREATE TABLE `my_auto` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

插入数据测试,此时最新数据id为10:

insert into my_auto VALUES(null,'Lucy');
select * from my_auto ;


c9cade8ce59acb10aabc0a84305342ae.png

演示到这里,有没有一个疑问:为什么自增长是从1开始?

我们知道,所有系统的表现(如字符集,校对集)都是由系统内部的变量进行控制的。

查看自增长对应的变量:show VARIABLES LIKE '%auto_increment%'

auto_increment_increment 1 --步长值
auto_increment_offset    1 --起始值

f343a43dd191f21dd1cbd7959a31ab11.png

可以修改变量实现不同的效果。但是修改是对整个数据库进行修改而不是单张表,且修改是会话级别,关闭并重新打开链接,变量恢复默认值

set auto_increment_increment = 2;
show VARIABLES LIKE '%auto_increment%';

1d861c133fc611fb25fb95cc32af9c83.png

变量修改后,在第二次插入数据时候生效

因为修改变量并不会立即更新表选项的auto_increment,当再插入数据后,会更新auto_increment ,此时检测到步长已经改变,故之后插入数据会使用新的步长值。

insert into my_auto values(null,'Lucy12');
insert into my_auto values(null,'Lucy12');
select * from my_auto;

80b5fe69451633ab0abf2978b048d679.png

⑤ 删除自增长

自增长不能通过drop消除,只能通过modify column。需要注意的是,如果自增长对应的字段为主键,那么修改 列属性的时候,不要再加primary key ; 会被系统认为又定义一个主键,将会提示"Multiple primary key defined"

--错误,会被认为又定义一个主键;
alter table my_auto modify id int primary key;
--正确,且不会丢失掉 id 的主键
alter table my_auto modify id int ;

⑥ 标识列

又称为自增长列,含义:可以不用手动的插入值,系统提供默认的序列值。

可以通过 手动插入值,设置起始值

# 设置普通字段NAME自增
CREATE TABLE tab_identity(
  id INT  ,
  NAME FLOAT UNIQUE AUTO_INCREMENT,
  seat INT 
);

【6】唯一键

一张表往往有很多字段需要唯一性,数据不能重复;但是一张表只能有一个字段为主键,那么唯一键(unique key),就可以解决表中有多个字段需要唯一性约束的问题。

唯一键默认值允许自动为空,而且可以多个不同字段为空—空字段不参与唯一性比较。

① 增加唯一键

分为创建表和创建后两种方式:

① 创建表时(又分两种):

--第一种,字段增加
create table my_unique(
name varchar(4) unique key,
number varchar(20) unique key
)charset utf8;
--第二种,字段末尾增加
create table my_unique(
name varchar(4) ,
number varchar(20) ,
unique key name(name),--指定唯一约束名字
unique key(number)--使用默认名字
)charset utf8;

a4ac2de03817ddc74f5406e341cd92d7.jpg

② 创建表后(又分两种):

#创建表,不带唯一约束
create table my_unique(
name varchar(4) ,
number varchar(20) 
)charset utf8;
#第一种,modify column
alter table my_unique modify column name varchar(5) UNIQUE KEY;
#第二种 add constraint unique key,指定唯一约束名字为num_uk
alter TABLE my_unique add CONSTRAINT num_uk UNIQUE KEY(number);

② drop删除唯一约束

#根据指定约束名字删除,若创建的时候未指定名字,默认使用列名作为唯一约束名字。
alter table my_unique drop index num_uk;


CREATE TABLE `my_unique` (
  `name` varchar(5) DEFAULT NULL,
  `number` varchar(20) DEFAULT NULL,
  UNIQUE KEY `num_uk` (`number`),--唯一约束名字为num_uk
  UNIQUE KEY `name` (`name`)--唯一约束名字默认为列名
) ENGINE=InnoDB DEFAULT CHARSET=utf8

下面演示一个有趣的现象:

--创建表
create table my_unique(
name varchar(4) not null,--注意这里 not null
number varchar(20) 
)charset utf8;
--增加唯一约束
alter table my_unique modify column name  varchar(5) not NULL UNIQUE KEY;
--查看表结构
desc my_unique;

注意key那一列,显示 PRI 不是UNI!解释如下:该表没有主键;刚好是一个不为空的唯一键,性质和主键一样。故MySQL无法判断,显示了PRI。当表中已经存在主键,则会正常显示为UNI


③ 复合唯一约束

##第一种创建方式
create table my_unique(
name varchar(4) not null,
number varchar(20) ,
unique KEY na_um_un(name,number)--字段末尾创建,使用指定名
)charset utf8;
desc my_unique;

插入数据

insert into my_unique values('tom','1001');
insert into my_unique values('tom','1001');
  • 提示数据重复–约束名字为 na_um_un(创建时候指定的名字)

--第二种创建方式
--创建空表,不带约束
create table my_unique(
name varchar(4) not null,
number varchar(20) 
)charset utf8;
--增加复合约束
alter TABLE my_unique add CONSTRAINT num_na_uk UNIQUE KEY(number,name);
--查看表结构
desc my_unique;

  • 删除指定名字复合约束
alter table my_unique drop index num_na_uk;
  • 删除默认名字复合约束
  • 若不指定名字,那么复合约束名字为第一个字段名字
alter TABLE my_unique add CONSTRAINT UNIQUE KEY(number,name);
--查看表创建语句
show create TABLE my_unique;
CREATE TABLE `my_unique` (
  `name` varchar(4) NOT NULL,
  `number` varchar(20) DEFAULT NULL,
  UNIQUE KEY `number` (`number`,`name`)--这里,名字为number
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--删除复合约束
alter table my_unique drop index number;

另外,唯一键同样是索引的类型,故唯一键的添加删除可以如下:

ALTER TABLE `weight_num_curr_detail`
DROP INDEX `unque_store_brand` ;
ADD UNIQUE INDEX `unque_store_brand` (`store_code`, `brand_code`, `year_num`) USING BTREE ;


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
安全 关系型数据库 MySQL
PHP与MySQL交互:从入门到实践
【9月更文挑战第20天】在数字时代的浪潮中,掌握PHP与MySQL的互动成为了开发动态网站和应用程序的关键。本文将通过简明的语言和实例,引导你理解PHP如何与MySQL数据库进行对话,开启你的编程之旅。我们将从连接数据库开始,逐步深入到执行查询、处理结果,以及应对常见的挑战。无论你是初学者还是希望提升技能的开发者,这篇文章都将为你提供实用的知识和技巧。让我们一起探索PHP与MySQL交互的世界,解锁数据的力量!
|
6天前
|
存储 关系型数据库 MySQL
MySQL 8.0特性-自增变量的持久化
【11月更文挑战第8天】在 MySQL 8.0 之前,自增变量(`AUTO_INCREMENT`)的行为在服务器重启后可能会发生变化,导致意外结果。MySQL 8.0 引入了自增变量的持久化特性,将其信息存储在数据字典中,确保重启后的一致性。这提高了开发和管理的稳定性,减少了主键冲突和数据不一致的风险。默认情况下,MySQL 8.0 启用了这一特性,但在升级时需注意行为变化。
|
16天前
|
NoSQL 关系型数据库 MySQL
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
|
6天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
20 3
|
7天前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
22 3
|
6天前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
13 1
|
21天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
54 9
|
17天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
30 0
|
1月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
2月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计