快捷搜索:  汽车  科技

mysql约束的几种类型(MySQL中六大约束你知道几个)

mysql约束的几种类型(MySQL中六大约束你知道几个)InnoDB对聚簇索引处理如下:既然InnoDB对数据的存储必须依赖于主键,那么对于没有创建主键的表,该怎么办?这个算是我们最常使用的 我们在创建表的时候一般会选用ID做主键约束 主键约束用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号,并且这个身份证号唯一,也就是我们的ID 全部都是不能重复 也就衍生出很多 主键的生成策略 UUID 雪花算法等等问: 如果我们在创建的时候没有去主动设置一个主键约束怎么办?MySQL数据表使用InnoDB作为存储引擎的时候,数据结构就是使用B 树,而数据本身存储在主键索引上,也就是通常所说的聚簇索引,也就是每个表都需要有个聚簇索引树,但是,在建表的时候却发现可以不用指定主键,那么MySQL对于没有指定主键的表示如何处理的呢?回答:

前言

问: 约束存在的意义是什么?

答:为防止错误的数据被插入到数据表,MySQL中定义了一些维护数据库完整性的规则;这些规则常称为表的约束 是对数据准确性的一种提前控制

例如:人员的姓名不能为空,人的年龄只能在0~150岁之间。约束可以对数据库中的数据进行保护。
约束可以在建表的时候直接声明,也可以为已建好的表添加约束。

mysql约束的几种类型(MySQL中六大约束你知道几个)(1)

正文

我们简单说一下约束的几种类型和使用方法

mysql约束的几种类型(MySQL中六大约束你知道几个)(2)

主键约束主键约束理念

这个算是我们最常使用的 我们在创建表的时候一般会选用ID做主键约束 主键约束用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号,并且这个身份证号唯一,也就是我们的ID 全部都是不能重复 也就衍生出很多 主键的生成策略 UUID 雪花算法等等

提出问题

问: 如果我们在创建的时候没有去主动设置一个主键约束怎么办?MySQL数据表使用InnoDB作为存储引擎的时候,数据结构就是使用B 树,而数据本身存储在主键索引上,也就是通常所说的聚簇索引,也就是每个表都需要有个聚簇索引树,但是,在建表的时候却发现可以不用指定主键,那么MySQL对于没有指定主键的表示如何处理的呢?

回答:

既然InnoDB对数据的存储必须依赖于主键,那么对于没有创建主键的表,该怎么办?

InnoDB对聚簇索引处理如下:

  • 如果定义了主键,那么InnoDB会使用主键作为聚簇索引
  • 如果没有定义主键,那么会使用第一非空的唯一索引(NOT NULL and UNIQUE INDEX)作为聚簇索引
  • 如果既没有主键也找不到合适的非空索引,InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id,而且InnoDB 维护了一个全局的 dictsys.row_id,所以未定义主键的表都共享该row_id,每次插入一条数据,都把全局row_id当成主键id,然后全局row_id加1

很明显,缺少主键的表,InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引,查询的时候都是全表扫描,小数据量没问题,大数据量就会出现性能问题。

但是,问题真的只是查询影响吗?不是的,对于生成的ROW_ID,其自增的实现来源于一个全局的序列,而所以有ROW_ID的表共享该序列,这也意味着插入的时候生成需要共享一个序列,那么高并发插入的时候为了保持唯一性就避免不了锁的竞争,进而影响性能。

即便我们没有去主动创建一个主键约束 MySQL 数据库也会创建帮我们去创建一个

小总结
  • 如果定义了主键,那么InnoDB会使用主键作为聚簇索引
  • 如果没有定义主键,那么会使用第一非空的唯一索引(NOT NULL and UNIQUE INDEX)作为聚簇索引
  • 如果既没有主键也找不到合适的非空索引,那么InnoDB会自动生成一个不可见的名为row_id的列名为GEN_CLUST_INDEX的聚簇索引,该列是一个6字节的自增数值,随着插入而自增--补充:该全局row_id在代码实现上使用的是bigint unsigned类型,但实际上只给row_id留了6字节,这种设计就会存在一个问题:如果全局row_id一直涨,一直涨,直到2的48幂次-1时,这个时候再 1,row_id的低48位都为0,结果在插入新一行数据时,拿到的row_id就为0,存在主键冲突的可能性

自动生成的主键约束会有什么问题?

  • 使用不了主键索引,查询会进行全表扫描
  • 影响数据插入性能,插入数据需要生成ROW_ID,而生成的ROW_ID是全局共享的(InnoDB 维护了一个全局的 dictsys.row_id,所有未定义主键的表都共享该row_id),并发会导致锁竞争,影响性能
创建主键约束方式

1、通过创建表的时候创建

CREATE TABLE test ( //这个id 就是主键约束 id int PRIMARY key )

2、通过语法去创建

alter table 表名 modify 列名 数据类型 primary key; 创建主键约束的限制

  • 主键约束相当于唯一性约束 非空约束 主键约束不允许重复 也不允许出现空值
  • 一个表最多只能有一个主键约束 建立主键约束可以在列级别创建 也可以在表级别创建
非空约束

上面那说到 主键约束 就等于 唯一性约束 非空约束 非空约束也就是 这个值不能为空 如果出现空值就会出现报错

1、作用:限定某个字段/某列的值不允许为空

2、关键值:no null

3、特点:

  • 默认所有有的类型得知都可以是null 包括int,float等数据类型
  • 非空约束只能出现表对象的列上,只能某个列单独限定非空,不能组合非空
  • 一个表可以有很多列都分别限定了非空
  • 空字符串 “” 不等于null 0也不等于null 4、添加约束:

创建数据表时:

    CREATE TABLE test(name varchar(20) not null);

创建数据表后:

alter table 表名称 modify 字段名 数据类型 not null;(模板)

例子:alter table test modify name varchar(20) not null;

默认值约束特点
  • 一个表可以有很多的默认值约束
  • 默认值约束只能针对某一个字段来说
  • 默认值约束意味着,该字段如果没有手动赋值,会按默认值处理
如何在建表时指定默认值约束?

create table 表名称( 字段名1 数据类型 primary key 字段名2 数据类型 【unique key】【not null】 default 默认值 字段名3 数据类型 default 默认值, 。。。。 );

例如:

create table student( sid int primary key sname varchar(20) not null gender char default '男' ); insert into student values(1 '变成派大星'); ERROR 1136 (21S01): Column count doesn't match value count at row 1 ' 列数与值数量 不匹配 insert into student values(1 '变成派大星' default); insert into student(sid sname) values(2 '海绵宝宝'); 建表后如何指定某个字段有默认值呢?

alter table [数据库名.]表名称 modify 字段名 数据类型 default 默认值; create table student( sid int primary key sname varchar(20) not null gender char ); alter table student modify gender char default '男';

提醒:如果某个字段既要非空,又要默认值,那么alter 语句后面两个都要写。

create table student( sid int primary key sname varchar(20) not null gender char not null );

增加gender还有默认值

alter table student modify gender char default '男' not null; 如何取消某个字段的默认值约束

alter table [数据库名]表名称 modify 字段名 数据类型 [not nul]; #不写默认值约束 insert into t_stu values (1 '变成派大星' '未知'); update t_stu set gender = default where sid = 1; 唯一性约束

唯一约束用来保证一列(或一组列)中的数据是唯一的。类似与主键,但是有以下区别:

  • 表可包含多个唯一约束,但每个表只允许一个主键。
  • 唯一约束列可包含 NULL 值。
  • 唯一约束列可修改或更新。
  • 唯一约束列的值可重复使用。
  • 唯一约束不能用来定义外键。
创建唯一约束

1、通过创建表的时候创建

CREATE TABLE test ( id int PRIMARY key auto_increment // 创建唯一约束 name VARCHAR(20) UNIQUE )

2、通过命令创建唯一约束

ALTER TABLE 表名 ADD CONSTRAINT 索引名称 UNIQUE (列名); -- 在已创建好的表上加唯一约束,指定约束名为name ALTER TABLE student ADD CONSTRAINT um UNIQUE (name); -- 在已创建好的表上加唯一约束,不指定约束名 ALTER TABLE student ADD UNIQUE (name); 创建多个唯一索引 ALTER TABLE student ADD CONSTRAINT un UNIQUE (name age );

3、删除唯一约束

查询索引

SHOW INDEX FROM 表名;

删除索引

-- 格式一: DROP INDEX 约束名 ON 表名; -- 例如: DROP INDEX name ON student; -- 格式二: ALTER TABLE 表名 DROP INDEX 约束名; -- 例如: ALTER TABLE student DROP INDEX name; 外键索引

1、特点 如果数据表含有从属关系,比如部门和员工, 教室和桌椅,班级和同学就可以使用一对多的制表关系 一的一方定义为主表, 多的一方定义为从表 在多的一方添加一列,作为外键约束 ,引用少的一方的主键 如果主表中的主键被应用,则该条数据无法被删除 如果从表中的外键找不到相同值的主键,则无法被插入

创建分类表

CREATE TABLE category ( cid VARCHAR(32) PRIMARY KEY cname VARCHAR(100)#分类名称 ); # 商品表 # 外键约束的添加格式 # 在创建时在最下方添加外键约束:constraint(约束) foreign key(外键字段) reference(参考) 主表(主键) CREATE TABLE products ( pid VARCHAR(32) PRIMARY KEY name VARCHAR(40) price DOUBLE category_id VARCHAR(32) CONSTRAINT FOREIGN KEY (category_id) REFERENCES category1 (cid) # 添加约束 ); 注意事项

  • 从表中添加的外键的值 一定要在主表中的id里存在 否则就会报错
  • 如果想要添加从表数据 先添加主表相对应的id的内容
  • 为了保证数据的完整性 如果当前主表中的数据已经被从表引用 则主表中的该数据无法被删除

不过现在外键索引用的比较少还是看个人使用

总结

1、主键约束

主键是表中能唯一识别表中每条信息的字段。要求:唯一、非空。一个表中只能有1个主键。

2、外键约束

与主键结合使用,确保数据的一致性。

3、唯一约束

一个表中可以有多个字段设置唯一约束,允许存在空值。

4、检查约束

用来检查数据表中,字段值是否有效,减少无效数据的输入。

5、非空约束

设置了非空约束的字段,不允许出现空值。

6、默认值约束

设置了默认值约束的字段,在该字段无数据输入时,会自动添加一个默认值。通常和非空约束一起使用。

主键约束 PRIMARY KEY

不论是单字段主键还是多字段联合主键都需要满足:

  • 主键能唯一标识表中每一行数据,且一个表中只能有1个主键的唯一性;
  • 主键不能有NULL值;
  • 联合主键中字段名不能重复出现。
设置主键的语法格式

1、 CREATE TABLE 语句中

单字段主键在定义字段时,创建约束

CERATE TABLE <表名> (<字段名> <数据类型> PRIMARY KEY [默认值],...)

2、命令创建

CERATE TABLE <表名> (<字段名> <数据类型> ,...,[CONSTRAINT <约束名>] PRIMARY KEY [字段名])

3、 多字段联合主键:

CERATE TABLE <表名> (<字段名> <数据类型> ,...,PRIMARY KEY [字段1,字段2,… 字段n])

4、 ALTER TABLE 语句中修改约束:

ALTER TABLE <表名> ADD PRIMARY KEY(<字段名>);

5、 删除约束:

ALTER TABLE <表名> DROP PRIMARY KEY; 外键约束 FOREIGN KEY

外键是与主键一起使用的,用来约束两个表的数据一致性和完整性。主键所在表为主表,外键所在表为从表。

1、外键需遵守的规则

  • 一个表可以有多个外键,允许有空值;
  • 主表已存在于数据库中,或为正创建的表。主表必须已指定主键;
  • 外键中字段数目、对应的字段数据类型必须与主键中字段数目、对应的字段数据类型相同。
设置外键的语法格式:

1、CREATE TABLE 语句中,

CERATE TABLE <表名> (<字段名> <数据类型> ,...,[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…] REFERENCES <主表名> 主键列1 [,主键列2,…]) 复制代码

2、 命令创建

2.1、修改外键约束:

ALTER TABLE <表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

2.2、 删除外键约束:

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>; 复制代码 唯一约束 UNIQUE

设置唯一约束的语法格式:

1、 CREATE TABLE 语句中,

CERATE TABLE <表名> (<字段名> <数据类型> UNIQUE,...);

2、ALTER TABLE 语句中,

2.1 修改唯一约束:

ALTER TABLE <表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

2.2 删除唯一约束:

ALTER TABLE <表名> DROP INDEX <唯一约束名>; 检查约束 CHECK

设置检查约束的语法格式:

1、 CREATE TABLE 语句中,

CERATE TABLE <表名> (<字段名> <数据类型> ,...,CHECK <检查约束>); <检查约束>,可以是简单的表达式,也可以是子查询

2、ALTER TABLE 语句中,

2.1 修改检查约束:

ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK(<检查约束>);

2.2 删除检查约束:

ALTER TABLE <表名> DROP CONSTRAINT <检查约束名>; 默认值约束 DEFAULT

设置默认值的语法格式:

1、CREATE TABLE 语句中,

CERATE TABLE <表名> (<字段名> <数据类型> DEFAULT <默认值>,...);

2、ALTER TABLE 语句中,

2.1 修改默认值:

ALTER TABLE <表名> CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

2.2 删除默认值:

ALTER TABLE <表名> CHANGE COLUMN <字段名> <字段名> <数据类型> DEFAULT NULL; 非空约束 NOT NULL

设置非空约束的语法格式:

1、CREATE TABLE 语句中,

CERATE TABLE <表名> (<字段名> <数据类型> NOT NULL,...);

2、ALTER TABLE 语句中,

2.1 修改非空约束:

ALTER TABLE <表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;

2.2 删除非空约束:

ALTER TABLE <表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;

猜您喜欢: