05 MySQL--字段约束、事务、视图

1. CONSTRAINT 约束

创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。比如大家上网注册用户时常见的：用户名不能为空。对不起，用户名已存在。等提示信息。
约束包括：

• 非空约束：not null
• 检查约束：check
• 唯一性约束：unique
• 主键约束：primary key
• 外键约束：foreign key

1.1 NOT NULL 非空约束

语法格式：

create table t_stu(
  no int,
  name varchar(255) not null,
  age int
);

name字段不能为空。插入数据时如果没有给name指定值，则报错。

1.2 CHECK 检查约束

create table t_stu(
  no int,
  name varchar(255),
  age int,
  check(age > 18)
);

1.3 UNIQUE 唯一性约束

1.3.1 列级唯一约束

#语法格式：
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255) unique
);

1.3.2 表级唯一约束：

使用表级约束可以为多个字段添加联合唯一约束。

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name,email)
);

1.4 FOREIGN KEY 外键

• 外键是一种对应关系，一个外键对应一个参照字段 + 一个被参照字段。不像主键就是字段本身，因此外键需要另外命名。
• 外键的键名中建议包含一个 fk 字段以一目了然这是个外键，如 t_school_schno_fk 。
• 添加了外键约束的字段中的数据必须来自其他表的字段。
• 假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。例如学生表中引用的学校号 schno，在学校表中，这个字段的所有值都是唯一的
• 外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。
• a 表如果引用 b 表中的数据，就把 b 表叫做父表，把 a 表叫做子表。
  1. 创建表时，先创建父表，再创建子表。
  2. 插入数据时，先插入父表，在插入子表。
  3. 删除数据时，先删除子表，再删除父表。
  4. 删除表时，先删除子表，再删除父表。

create table t_school( 
  schno int primary key, 
  sname varchar(255) 
); 
create table t_student( 
  stuno int primary key, 
  name varchar(255), 
  age int, 
  schno int, 
  #创建一个外键
  constraint t_school_sno_fk foreign key(schno) references t_school(schno) 
);

1.4.1 级联操作

ＣＡＳＣＡＤＥ关键字，可以令父表发生变化时，子表相应做出变化

一般我们的选择是，父表删除时选择级联置空 SET NULL；附表更新时选择级联更新 CASCADE。

1. 级联删除

创建子表时，外键可以添加：on delete cascade，这样在删除父表数据时，子表会级联删除。谨慎使用。

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade 
);

2. 级联更新

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade 
);

3. 级联置空

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null 
);

1.5 PRIMARY KEY 主键

1.6

• 创建约束时也可以给约束起名字，将来可以通过约束的名字来删除约束：

create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  constraint t_stu_name_email_unique unique(name,email)
);

• 所有的约束都存储在一个系统表当中：table_constraints。这个系统表在这个数据库当中：information_schema

2. TRANSACTION 事务

事务只针对DML语句有效：因为只有这三个语句是改变表中数据的。

1. insert
2. delete
3. update

2.1 事务四大特性：ACID

• 原子性（Atomicity）：是指事务包含的所有操作要么全部成功，要么同时失败。
• 一致性（Consistency）： 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。例如张三和李四的钱加起来是5000，中间不管进行过多少次的转账操作(update)，总量5000是不会变的。这就是事务的一致性。
• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰。例如当多个⽤户并发操作同⼀张表时，多个事务之间必须互不影响。
• 持久性（Durability）：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

2.2 事务的隔离级别

• 严重性从高到低排序：脏读 > 不可重复读 > 幻读
• MS 默认的隔离级别：可重复读（REPEATABLE READ）。大部分数据库的默认隔离级别都是第二、三档。

2.2.1 数据读取问题

• 脏读

指的 A 事务读取了 B 事务尚未提交的数据（即脏数据 Dirty Data）。在此情况下，如果 B 事务回滚了或者修改了这些数据，那么 A 事务读取这些脏数据就是不准确的。常发生于转账与取款操作中。

• 不可重复读

A 事务执行过程中，要多次读取同一个数据行，但在自身没有修改该数据行的情况下，前后读到的数据不一致。这是由于其他事务修改了该数据。

• 幻读

指在事务执行过程中，前后两次相同的查询条件得到的结果集（数据总量）不一致，可能会变多或变少。

• 不可重复读和幻读的区别？

(1) 不可重复读是读取了其他事务更改的数据，针对update操作

解决：使用行级锁，锁定该行，事务 A 所有对该行的读取操作完成后才释放锁，这个时候才允许其他事务更改改行。

(2) 幻读是读取了其他事务新增的数据，针对insert和delete操作

解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

2.2.2 隔离级别

• READ UNCOMMITTED（读未提交）

该隔离级别表示一个事务可以读取另一个事务修改但还没有提交的数据。该级别不能防止任何数据读取问题，是一种理论层面的，在实际的数据库产品中，没有从这个级别起步的。

• READ_COMMITTED (读提交)

该隔离级别表示一个事务只能读取另一个事务已经提交的数据。该级别可以防止脏读，这也是大多数情况下的推荐值。

• REPEATABLE_READ (可重复读)

该隔离级别表示一个事务在整个过程中可以多次执行某个查询语句，并且每次返回的数据都相同。即使在多次查询之间有新增的数据满足该查询，这些新增的记录也会被忽略。该级别可以防止脏读和不可重复读。

• SERIALIZABLE (串行化)

所有的事务串行逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。
在该隔离级别下，MS 的 InnoDB 引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

• MVCC（多版本并发控制）

mysql中，默认的事务隔离级别是可重复读（repeatable-read），为了解决不可重复读，innodb采用了MVCC（多版本并发控制）来解决这一问题。 MVCC是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个递增的版本号,用来和查询到的每行记录的版本号进行比较。 MYSQL MVCC

2.2.3 查看与设置隔离级别

• 查看当前会话的隔离级别：select @@transaction_isolation;
• 查看全局的隔离级别：select @@gobal.transaction_isolation;

设置事务隔离级别：

• 会话级：set session transaction isolation level read committed;
• 全局级：set global transaction isolation level read committed;

2.3 可重复读隔离级别的具体实现

理论上隔离级别为可重复读时，只能杜绝脏读、不可重复读两个问题
但 MS 实现的可重复读，在很大程度上也避免了幻读问题（并不能完全解决）。

解决方案包括两种：

• 针对快照读（普通 SELECT 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题。
• 针对当前读（执行任何 DML 语句都会执行一次当前读），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

2.3.1 快照读是如何解决幻读的

普通的select语句都是采用的快照读。

什么是快照读？
顾名思义：事务隔离级别是可重复读的前提下，在整个事务的处理过程中，第一次执行 SELECT 语句时，会保存一个快照（第一次读取时固定下来的数据），今后只要执行相同的 SELECT 语句，都是读取的快照。

底层实现原理是：
由 MVCC（多版本并发控制）实现。
实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。

演示：

事务A	事务B
mysql> use powernode
	mysql> use powernode
mysql> start transaction;
	mysql> start transaction;
mysql> select * from a; //快照读
	mysql> insert into a values(5);
	mysql> commit;
mysql> select * from a; //快照读

2.3.2 当前读是如何解决幻读的

当前读，顾名思义：每一次都读取最新的数据。

当前读包括：update、delete、insert、select…for update，即所有 DML 语句。这个很好理解，因为增删改的时候都要基于最新的数据进行增删改。

而select…for update原理是：对查询范围内的数据进行加锁，不允许其它事务对这个范围内的数据进行增删改。也就是说这个select语句范围内的数据是不允许并发的，只能排队执行，从而避免幻读问题。

select…for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作。

假如有这样的数据：

SQL语句是这样写的：

select * from a where id between 2 and 4 for update;

那么id在[2-4]区间的所有记录行被锁定，不能插入3是通过间隙锁来搞定的。不能修改或删除2和4是通过记录锁来搞定的。

2.3.3 出现幻读的两种情况

在同一个事务处理过程中，如果前后两次都采用快照读，或者都采用当前读，则不会出现幻读问题。

如果第一次使用快照读，后面使用了当前读，则会出现幻读问题。

第一种产生幻读的场景

在 A 事务中第一次查询使用快照读，B 事务插入数据。然后在 A 事务中第二次查询使用当前读。则会产生幻读现象。

第二种产生幻读的场景

在事务 A 中第一次查询使用快照读，在事务 B 中插入一条数据，然后在事务 A 中更新事务 B 插入的那条记录，最后在事务A中再次使用快照读。则会发生幻读现象。
|

总结可重复读的幻读问题

要避免特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

---

3. VIEW 视图

3.1 视图的概念

视图是由数据库中的一个表或多个表导出的虚拟表，其内容由查询定义，其作用是方便用户对数据的操作。

同真实的表一样，视图包含一系列带有名称的列和行数据。

数据库中存储的是：表的定义 + 符合表定义的数据 + 视图的定义。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据放置于视图中。

视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
同时，对视图的更新，也会影响到原来表的数据。

视图是存储在数据库中的查询的SQL语句，

它主要出于两种原因：

1. 视图可以隐藏表的字段名。不论是为了安全。例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等；抑或是使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到指定的的数据列。这意味着你可以在这个视图上使用SELECT *，而你看到的将是你在视图定义里给出的那些数据列。
2. 便于修改。如果一段很复杂的 SQL 语句在程序的多个地方被使用，如果此时想修改，就要多个地方同时修改，并且别忘了这段语句很复杂。如果我们提前将这段语句创建为视图，那么在修改时只要修改视图即可。类似于程序设计中的抽象过程。

3.2 视图的基本操作

1. 只能将select语句创建为视图。
2. 创建视图

#如果已经存在则 replace ，类似于 create table if not exists，用来保底确保操作无误
create or replace view v_emp as select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

3. 修改视图

alter view v_emp as select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno;

4. 删除视图
   1. drop view if exists v_emp;
5. 对视图增删改（DML：insert delete update）可以影响到原表数据。