继续上一篇我们继续学习InnoDB的表的相关知识,本章主要内容:InnoDB表约束,视图,分区表。

约束

首先我们搞清楚约束和索引的区别,用户创建了一个唯一索引,意味着用户创建了唯一约束。但是约束和索引的概念是不同的,约束更多是逻辑上的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,还有物理上的存储。

数据完整性

在InnoDB存储引擎表中,域完整性可以通过如下几个途径来保证:

  • 选择合适的数据类型确保一个数据值满足特定的条件
  • 外键(Foreign Key)约束
  • 编写触发器(在执行SQL前后执行的特定行为动作,个人简单理解为Spring中的AOP切面的编程)
  • 还可以考虑用DEFAULT约束作为强制域完整性的一个方面。

对于InnoDB而言,提供出去的集中约束为:

  • Primary Key
  • Unique Key
  • Foreign Key
  • Default
  • NOT NULL

对错误数据的约束

在某些默认值下,mysql允许非法或者不正确的数据插入或更新。比如NOT NULL约束下你传一个NULL值进来,Mysql会将NULL转换成0再进行插入。比如时间插入,插入‘2020-02-30’,数据库也不会对日期是否合法进行验证。因此数据库本身没有对数据的正确性进行验证。可以通过设置sql_mode用来严格审查输入的参数。sql如下:

SET sql_mode = 'STRICT_TRANS_TABLES'

ENUM和SET约束

MySQL数据不支持传统的Check约束,但是可以通过ENUM和SET类型解决这部分需求。具体使用方式查看 

触发器与约束

实现比较复杂的约束的时候ENUM和SET有点乏力,我们还是使用触发器进行约束比较好。

触发器:作用是在执行INSERT、DELETE、以及UPDATE命令之前或者之后自动调用的SQL命令或者存储过程。

创建触发器:

CREATE
[definer = {user | current_user}]
TRIGGER trigger_name before |after insert | delete | update on table_name for each row trigger_stmt

我们最多可以为一个表创建6个触发器,即分别为insert、update、delete的before和after各定义一个触发器。当前的mysql只支持for each row的触发器。

外键约束

外键约束来保证完整性,InnoDB支持外键约束,定义如下:

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (table_col_name, ......)
REFERENCES tab_name (index_col_name,......)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

一般来说,称引用的表为父表,引用的表成为子表。外键定义时的 ON DELETE和 ON UPDATE 表示在对父表进行相关操作的时候,对子表进行的操作,可定义的子表操作为:

  • CASCADE : 同父表一样进行删除更新操作
  • SET NULL : 子表对应的数据更新为NULL
  • NO ACTION :如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
  • RESTRICT : 表示不允许这样的事情发生,并抛出错误(同NO ACTION相似)

视图

Mysql数据库中,视图是一个命名的虚表,它有一个SQL查询语句定义,可以用作表使用,但是不占用物理内存。Mysql不支持物化视图。

分区表

分区功能并不是在引擎层面完成的,因此不是只有InnoDB才有分区功能。Mysql的数据库分区支持水平分区,并且Mysql的分区是数据和索引一起分区。不支持全局分区(数据存放在各个分区,索引在一个对象里面)。

分区的类型:

  • RANGE分区:行数据基本属于一个给定连续区间的列值被放入分区,Mysql5.5开始支持RANGE COLUMNS的分区。
  • LIST分区:和RANGE分区类似,只是LIST分区面向的离散的值,Mysql5.5开始支持LIST COLUMNS的分区。
  • HASH分区:根据用户自定义的表达式的返回值进行分区,返回值不能为复数。
  • KEY分区:根据Mysql数据库提供的哈希函数来进行分区。

无论创建什么分区,如果表中存在主键或者唯一索引,那么每个分区对应的列也必须是主键或者唯一索引。