1 约束
1.1 约束分类
- NOT NULL：非空，用于保证该字段的值不能为空。比如姓名、学号等。
- DEFAULT：默认，用于保证该字段有默认值，比如性别。
- PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等。
- UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号。
- CHECK：检查约束，但在MySQL中不支持。比如年龄、性别。
- FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。
1.2 约束操作
约束按照添加的位置可以分为两类，分别是列级约束和表级约束：
- 列级约束：对一个数据列建立的约束，称为列级约束。列级约束既可以在列定义时声明，也可以在列定以后声明。六大约束语法上都支持，但外键约束没有效果。
- 表级约束：对多个数据列建立的约束，称为表级约束，但表级约束只能在列定义后声明。除了非空约束和默认约束，其他的都支持。
1.2.1 创建表时添加约束
添加列级约束
CREATE TABLE stuinfo(
id INT PRIMARY KEY, # 主键
stuName VARCHAR(20) NOT NULL UNIQUE, # 非空
gender CHAR(1) CHECK(gender='男' OR gender ='女'), # 检查
seat INT UNIQUE, # 唯一
age INT DEFAULT 18, # 默认约束
majorId INT REFERENCES major(id) # 外键
);
CREATE TABLE major(
id INT PRIMARY KEY,
majorName VARCHAR(20)
);
添加表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT,
CONSTRAINT pk PRIMARY KEY(id),#主键
CONSTRAINT uq UNIQUE(seat),#唯一键
CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
综合写法：
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
1.2.2 修改表时添加约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
# 1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
# 2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
# 3.添加主键
# 列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
# 表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
# 列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
# 表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
# 5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
1.2.3 删除约束
# 1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
# 2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;
# 3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;
# 4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;
# 5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
1.3 标识列
1.3.1 定义
标识列通常也被叫做自增列，它的作用是在插入数据时可以不用手动地插入值，系统会提供默认的序列值。自增列并不是索引，但必须与索引配合使用！
- 标识列必须和主键搭配吗？ 不一定，但要求是一个索引
- 一个表可以有几个标识列？ 至多一个！
- 标识列的类型只能是数值型！
- 可以通过手动插入值，设置起始值，并且可以通过SET auto_increment_increment=3;设置步长！
1.3.2 主键生成策略
标识列通常用在主键上，即自增主键，用于保证主键的唯一性。主键生成策略通常要求：
- 全局唯一性：必须保证生成的主键ID是全局性唯一
- 有序性：生成的主键ID需要按照某种规则有序，便于数据库的写入和排序操作
- 可用性：需要保证高并发下的可用性。除了对主键ID自身的要求，还有ID生成系统的可用性
- 安全性：不能暴露系统和业务的信息
方案一：MySQL自增主键
- 优点：主键索引为自增id的话，插入数据只要放在该页的最尾端就可以，不用刻意维护。如果主键索引不满足单调递增性的话，那么数据就可能从页的中间插入，从而导致频繁地出现页分裂现象。
- 缺点：
- 安全性不高：对外暴露的接口容易猜测到用户的信息。
- 性能差：自增主键的性能较差，需要在数据库服务器端生成。同时，数据库水平拆分会产生数据一致性问题。
方案二：分布式ID生成器，最典型的应用就是使用Redis生成主键ID。针对于使用数据库自增ID的缺点，可以使用Redis来生成ID。Redis的单线程特性，同时其性能极佳，主要通过使用Redis的原子操作来实现。
- 优点：性能快，解决分布式环境产生的数据一致性问题。
- 缺点：仍然存在安全性不高的问题，此外引入新的组件Redis，如果Redis出现单点故障问题，则会影响序号服务的可用性，因此需要保证Redis的高可用。
方案三：UUID算法
- 优点：全局唯一、生成速度比较快、简单易用。
- 缺点：
- 空间消耗大：UUID长度是16字节，按照4-2-2-2-6的顺序进行分隔。
- 数据无序性：导致数据库插入性能差。
- 不安全性：基于MAC地址生成的UUID版本会泄露MAC地址。
方案四：雪花算法，其生成结果是一个64位的字符串
- 第0位是符号位（标识正负），始终为 0，没有实际意义。
- 第1~41位表示时间戳，单位是毫秒，可以支撑2 ^41毫秒（约69年）
- 第42~52位表示工作机器Id，一般来说，前5位表示机房ID，后5位表示机器ID（实际项目中可以根据实际情况调整）。
- 第53~64位表示序列号，也就是说单台机器每毫秒最多可以生成4096个ID。
优点：
- 生成速度快
- ID有序递增
- 灵活，可以对雪花算法进行简单的改造，比如加入业务ID等。