mysql约束、视图、分区表总结

innodb数据完整性

实体完整性：保证表中有一个主键

域完整性：保证数据每列的值满足特定的条件（可以通过以下几种途径保证：1.选择合适的数据类型确保一个数据值满足特定条件；2.外键约束；3.编写触发器；4.可以用delete约束作为强制域完整性的一个方面）

参照完整性：保证两张表之间的关系（可以通过外键和编写触发器强制执行）

innodb存储引擎提供的约束:primary key、unique key、foreign key、default、not null

约束的创建和查找的方式：1.表建立时进行约束定义；2.利用alter table命令进行创建约束

触发器

触发器的作用：在执行insert、delete、update命令之前或之后自动调用sql命令或存储过程

只有具备super权限的用户才能创建触发器

一张表最多可以创建6个触发器即分别为insert、update、delete的before和after各定义一个

当前mysql数据库只支持for each row的出发方式即按每行记录进行触发。

外键约束

外键用来保证参照完整性，innodb支持外键约束

用户可以在create table时添加外键，也可以通过alter table添加

外键的定义：

[CONSTRAINT symbol] FOREIGN KEY [id] (从表的字段1)

REFERENCES tbl_name (主表的字段2)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION} 

说明：被引用的表为父表，引用的表为子表

on delete和on update表示在对父表进行delete和update操作时对子表所进行的操作，可定义的子表操作有：

cascade：当父表进行delete和update时，子表也进行delete和update

set null：当父表进行delete和update时，子表相应的数据更新为null

no action：当父表进行delete和update时，抛出错误不允许进行这样的操作

restrict：当父表进行delete和update时，抛出错误不允许进行这样的操作。为默认设置

mysql外键约束的检查时即时检查，no action和restrict的功能是相同的

建立外键时，若该列没有索引，innodb会自动对该列加一个索引。

在对数据进行导入操作时，外键会导致在外键约束的检查上花费大量时间(mysql为即时处理，所以在导入每行都会进行外键检查)，可以通过忽视外键检查来解决 set foreign_key_checks=0

视图

视图是一个命名的虚表，视图中的数据没有实际的物理存储

分区表

分区的过程：将一个表或索引分解为多个更小、更可管理的部分

mysql支持的分区类型为水平分区，mysql分区是局部分区索引，一个分区中既存放数据又存放了索引。

mysql支持的几种分区类型(数据必须是整型，不是可通过函数(year(),to_days(),)进行转换)：

range分区：行数据基于属于一个给定连续区间的列值被放入分区(主要用于日期列的分区)

list分区：类似range分区，但面向的是离散的值

hash分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负值

key分区：根据mysql提供的哈希函数来进行分区

注意：如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分；如果建表时没有指定主键或唯一索引，可以指定任何一个列为分区列

可以通过information_schema下的partitions表来查看每个分区的具体信息；(TABLE_ROWS表示每个分区中记录的数量；PARTITION_METHOD表示分区类型)

columns分区类似range和list分区，可以直接使用非整型的数据进行分区，且可以对多个列的值进行分区

columns支持的数据类型(整型类型，日期类型，字符串类型)

子分区(复合分区)

在分区的基础上再进行分区，

建立子分区注意问题：

1.每个子分区的数量必须相同

2.要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的字分区

3.每个subpartition子句必须包括子分区的一个名字

4.子分区的名字必须唯一

分区中的NULL值

mysql允许对NULL值做分区，视NULL值为小于任何一个非NULL的值和order by处理NULL值类似

range分区下，将NULL值放入最左边的分区中

在list分区下，必须显式指出哪个分区中放入NULL值，否则会报错

hash和key分区下，任何分区函数都会将NULL值的记录返回为0

对于OLAP的应用，分区可以提高查询的性能

在表和分区间交换数据

通过alter table exchange partition语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换。

条件：

    要交换的表需和分区表有着同样的表结构，但是表不能含有分区

    在非分区表中的数据必须在交换的分区定义内

    被交换的表中不能含有外键，或其他的表含有对该表的外键引用

    用户除了需要alter、insert、create权限外，还需要drop权限

    使用该语句时，不会触发交换表和被交换表上的触发器

    auto_increment列将被重置

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结