MySQL入门教程(16)数据约束类型与使用建议

Tanglu MySQL 2022-12-23 180 0

MySQL数据约束的作用

数据约束是对表中字段进行强制限制的一种方法,通过约束可以实现数据完整性和一致性,也是需要强制让研发\维护人员所遵守的规则。数据约束按其功能分为了主键约束、外键约束、非空约束、唯一性约束、默认约束等类型


1、主键约束——PRIMARY KEY
· 主键约束是一种特殊的唯一索引,相当于唯一约束+非空约束的组合,一张表只能有一个主键约束 

· 用主键索引进行查询仅需要一次索引查找

· 主键约束下字段的值不能重复、不能为空,并且最好永远不要去手动更新主键列的值(使用复合主键可以同时给多个字段设置一个主键约束,但是这个组合后的值也必须是唯一的)

· 自增主键的插入模式需要符合单调递增的场景,即每次插入一条新记录都是追加操作,这样就不涉及挪动其他数据行的情况,也不会触发叶子节点的分裂。

· 主键约束最好建立在跟业务不相关的字段上,比如ID字段(建议类型为BITINT UNSIGNED)。如果使用业务字段做主键,由于无法预测未来会不会因为业务需要出现业务字段重复或者重用的情况,比如使用手机号作为主键会存在号码被回收换新用户的情况、使用身份证做主键的话,不一定所有用户都愿意暴露这样的隐私信息。而有业务逻辑的字段做主键往往也不容易保证有序插入,这样写数据成本相对较高

#写法1
CREATE TABLE t1 (
id int PRIMARY KEY,
name varchar(10)
);

#写法2
CREATE TABLE t1 (
id INT,
name VARCHAR(10),
PRIMARY KEY (id)
);

INSERT INTO t1 values (1,'zhangsan')(2,'lisi')(3,'wangwu');  #正常插入三条数据
INSERT INTO t1 values (4,'zhaoliu')(2,'wangba');  #由于ID为2的数字已经存在,报错

#后期添加约束示例,需要先确保没有字段的值是重复且为NULL的
ALTER TABLE t3 ADD PRIMARY KEY (id)

#删除主键索引
ALTER TABLE t5 DROP PRIMARY KEY


对有重复值但是没主键的表,如何加主键

#方法1:重复数据没用的话先清理掉再加主键,建议先将原表复制出一张新表再做清理

#方法2:创建一个新的自增长列,然后删除旧列,推荐
alter table student add new_id int auto_increment primary key;


对有主键约束的表插入重复值的办法

# 方法1:使用ignore将重复值跳过
insert ignore into student values (1,'tanglu'),(2,''tanglu2),(3,'tanglu3') 

# 方法2:使用replacce替换重复的值
replace into student values (1,'tanglu'),(2,''tanglu2),(3,'tanglu3') 

# 方法3:on deplicate key update


2、自增约束——AUTO_INCREMENT

· 整数类型的字段(TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT)才可以添加自增约束

· 自增字段通常不用手动插入数值,如果有手动插入数据,MySQL会重置自增约束字段的自增基数,下次新增数据的时候会从手动插入的数据值开始自增

· 一张表只能有一个自增列,自增列必须是键列(主键列或唯一键列),常定义在主键索引上

· 自增字段的值默认从1开始递增,当达到自增整型类型上限值时,再次自增插入会报重复错误

· 当向包含AUTO_INCREMENT的主键列上添加0或者NULL时,数值会自动自增

· 在8.0以前,自增值是保存在内存中的,如果删除过数据后再重启数据库会发现自增值出现了回退。而如果发生主键冲突或者事务回滚,自增值则会发生不连续现象

77b87820b649692a555f19b562d5d926.png


在表结构设计时如果用自增字段做主键建议使用BIGINT,因为INT的范围最大只能达到42亿的级别,在互联网业务场景中的一些流水表、日志表,如果每天有1000W的数据量,仅420天就可以达到INT 类型的上限,到时候再进行表结构变更的代价会更大。

CREATE TABLE student(
id bigint not null primary key auto_increment
name varchar(10)
);

insert into student(name) values ('tanglu');  # id列的值默认从1开始递增

create table student(
id int not null primary key auto_increment=100  #指定自增开始值
name varchar(10) # id列默认从100开始
);


3、外键约束——FOREIGN KEY
· 外键约束类似于子表的概念,实现表和表之间的关联

· 一张表可以有多个外键约束,外键约束需要在子表中定义,指明外键字段以及外键字段所引用的主表中的主键字段

·  外键约束分了多种工作方式(在创建时指明)

  Cascade:在父表上进行数据更新时会同步更新子表相匹配的记录

  Set null:在父表上进行数据更新时会将子表相匹配的记录修改为null

  No action:如果子表中有相关记录则不允许父表进行更新

· MySQL 系统会根据外键约束的定义,监控对主表中数据的删除操作。如果发现要删除的主表记录正在被从表中某条记录的外键字段所引用,MySQL 就会提示错误,从而确保了关联数据不会缺失

· 由于外键约束需要消耗额外的资源,在高并发场景下可能会因为外键约束导致性能受损明显,所以在实际开发过程中外键使用得并不多。通常是在应用层面去完成这些附加逻辑实现同样的功能,确保数据的一致性

CREATE TABLE t2 (
fid INT,
phone VARCHAR(16),
address VARCHAR(50),
constraint fk_t1 FOREIEN KEY(fid) REFERENCES t1(id)  #t2表的fid列为t1表id列的外键,外键名为fk_t1,其值来自t1表id列
#constraint fk_t1 FOREIEN KEY(fid) REFERENCES t1(id) ON UPDATE CASCADE ON DELETE SET NULL  #把修改操作设置为级联修改,删错设置为set null
);

#删除外键约束
ALTER TABLE t2 DROP FOREIGN KEY fk_t1


4、非空约束——NOT NULL
该约束可以让指定列中的值不能为空值(NULL),建议
在非必要的前提下所有字段都有该约束的存在,然后为约束字段设置一个默认值来确保字段不存在NULL值。因为索引在遇到NULL时会需要额外的空间来作为保存判断标志位。在进行比较时IS NOT NULL这样的语句是不会走到索引、在进行计算时遇到NULL值返回永远为空,会使查询结果出错(但是有一些字段可能必须存在NULL以表示它的本来含义,比如一些数值字段,如果设置了默认值会让平均值的计算出现差错,设置为NULL则不会参与计算)

#建表时创建约束示例
CREATE TABLE t3 (
id INT NOT NULL,
name VARCHAR(10) NOT NOLL
);

#后期添加约束示例,需要先确保没有字段的值为NULL
ALTER TABLE t3 MODIFY email VARCHAR(20) NOT NULL


5、唯一性约束——UNIQUE KEY
· 用于约束列中的值不能重复,比如身份证号、手机号、用户ID等信息一般是不可重复的

· 该约束允许字段值为空

· 相比主键约束,一张表可以有多个唯一约束,唯一约束修改起来也更方便

· 添加唯一约束的字段也会自动创建唯一索引,唯一索引的名称和唯一约束名相同;多个字段可以建立复合唯一约束,如果没有自定义复合索引的名称,则使用第一个字段作为索引名

· 删除唯一约束只能通过删除唯一索引的方式来实现

#建表时创建约束示例
CREATE TABLE t4(
id INT,
certid INT UNIQUE    #certid这列的值不能重复
email VARCHAR(20) UNIQUE    #email列的值不能重复
);

#后期添加约束示例,需要先确保没有字段的值是重复的
ALTER TABLE t3 MODIFY email VARCHAR(20) UNIQUE

#删除唯一约束
ALTER TABLE t3 DROP INDEX email


6、检查约束——CHECK

8.0开始支持使用,限制某个字段的值必须符合一定预期

CREATE TABLE test5(
id INT,
last_name VARCHAR(10),
salary DECIMAL(10,2) CHECK(salary > 2000)
)


7、默认值约束——DEFAULT

用于设置约束对应列中的值的默认值,除非默认值就是空值,否则不能插入空值

create table t5(
id int primary key,
name varchar(10) not null,
sex enum('man','woman','unkown') not null default 'unkown'  
); 

insert into t5 values (1,'tanglu');  #不填写第三个字段的内容,则采用default的值
insert into t5 values (1,'tanglu',default,);  #也可以直接写为default


8、注释——COMMENT

用户对字段或者表进行说明,便于维护

create table student (
id int not null primary key comment '学号',
name varchar(10) not null comment '姓名'
);


9、复合主键约束(mysql服务的用户授权就采用了这种方法,同时判断多个字段的值来决定最终的结果是否重复)

create table t2 (
id int,
certid int
name varchar(10)
primary key(id,certid)  #同时给2列设置主键约束,只有当2列数据都重复的时候才会报错
);

评论