《MySQL技术内幕：InnoDB存储引擎》读书笔记四-表（2）

四、表（2）

1、约束

InnoDB提供了以下几种约束：

l  Primary Key

l  Unique Key

l  Foreign Key

l  Default

l  NOT NULL

2、约束和索引的关系

*当你创建了一个唯一索引，就创建了一个唯一约束

* 约束是一个逻辑的概念，用来保证数据的完整性

* 而索引是一个数据结构，有逻辑上的概念，在数据库中更是一个物理存储的方式

 

3、ENUM与SET约束

4、触发器和约束

触发器作用：在INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或者存储过程。

创建命令：

         CREATE

         [DEFINER = { user | CURRENT_USER }]

         TRIGGER trigger_name  DEFORE|AFTER  INSERT|UPDATE|DELETE

         ON tbl_name FOR EACH ROW trigger_stmt

 

5、外键

创建命令：

         [ CONSTRAINT [ symbol ] ] FOREIGN KEY

         [ index_name ] (index_col_name, … )

         REFERENCES tbl_name (index_col_name, …)

         [ ON DELETE reference_option ]

         [ ON UPDATE reference option ]

         reference_option:

         RESTRICT  | CASCADE | SET NULL | NOT ACTION

其中CASCADE: 当父表发生DELETE或UPDATE操作时，相应的子表中的数据也被DELETE或UPDATE

SET NULL: 当父表发生DELETE或UPDATE操作时，相应的子表中的数据被更新为NULL值。当然，子表中相对应的列必须允许NULL值

NO ACTION：当。。。，抛出错误，不允许这种操作发生

RESTRICT：当。。。，抛出错误，不允许这种操作发生。若定义外键没有指定ON DELETE或ON UPDATE，这就是默认操作

测试：

首先建立一张表，并向里面插入几条数据：

①：测试CASCADE

建立子表，设置ON DELECT CASCADE和ON UPDATE CASCADE约束

这时向表child插入几条数据

1）插入父表中不存在的id，出现如下错误

2）插入父表存在的id。

3）这时，将父表中的id为1的id更新为6：

查看子表发现parent_id从1变成了6：

4）删除父表中id为3的行，同时可以看到子表中parent_id为3的行也被删除。

 

6、视图

作用：被用做一个抽象装置，特别是对于一些应用程序，程序本身不需要关心基表的结构，只需要按照视图定义来获取数据或者更新数据，因此，视图同时在一定程度上起到安全层的作用。

 

创建视图：

CREATE

[ OR REPLACE ]

[ ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE} ]

[ DEFINER = { user| CURRENT_USER } ]

[ SQL SECURITY { DEFINER | INVOKER } ]

VIEW view_name [ (column_list) ]

AS select_statement

[ WITH [CASCADED | LOCAL] CHECK OPTION]

 

其中WITH CHECK OPTION表示：对于可更新的视图，更新的值是否需要检查。

 

①：想要查看当前数据库下的基表：

SELECT * FROM information_schema.TABLES where table_type='BASE TABLE' and table_schema=database();

②：想要查看视图的一些元数据，则可以访问information_schema表下的VIEWS表：

SELECT * FROM information_schema.VIEWS where table_schema=database();

6、分区表

水平分区：指同一表中不同行的记录分配到不同的物理文件中

垂直分区：将同一表中不同的列分配到不同的物理文件中

 

MySQL不支持垂直分区，只支持水平分区。MySQL数据库中的分区是局部分区索引，一个分区中既存放了数据由存放了索引。

通过命令SHOW PLUGINS \G可查看是否启用分区功能。

 

分区主要用于高可用性，利于数据库管理。

MySQL数据库支持以下几种分区：

RANGE分区：行数据基于属于一个给定连续区间的列值放入分区；

LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值；

HASH分区：根据用户自定义的表达式的返回值来进行分区；

KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

 

分区限制要求：

--不论创建何种类型的分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分；

-- 唯一索引可以允许是NULL值，并且分区列只要是唯一索引的一个组成部分；

-- 当建表时没有指定主键，唯一索引时，可以指定任何一个列为分区列。

 

 

① RANGE分区：

创建RANGE分区：

启用分区后，表不再是一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成。

当插入分区范围外的数据时，出现错误：

对于上图这种情况，可添加一个MAXVALUE分区。

 

l  可以使用select * from information_schema.PARTITIONS where table_schema=database()查看每个分区具体信息。

l  对于RANGE分区的查询，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()和UNIX_TIMESTAMP()这类函数进行优化选择，对自定义的表达式形式会进行全表搜索。

 

② LIST分区

和RANGE分区类似，只是分区列的值是离散的，而非连续的。

l  如果插入的值不在分区定义中，同样会报错

l  在使用INSERT插入多个行数据时的过程中遇到分区未定义的值时，由于InnoDB支持事务，所以会把这多条插入看作一次事务，不会有任何数据插入，MyISAM则不同，在错误之前的数据都插入

③ HASH分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是相同的。

使用时需要在CREATE TABLE语句上添加一个“PARTITION BY HASH (expr)”，其中expr是一个返回一个整数的表达式。此外还要在后面添加一个“PARTITIONS num”，其中num为分区数目。

 

如下图以YEAR分区，共分成4个分区，则在保存记录时，分区使用如下表达式：

YEAR(year) MOD 4

④ KEY分区

与HASH分区相似，不同的是，HASH分区使用用户自定义的函数进行分区，而KEY分区使用MySQL数据库提供的函数进行分区。

⑤ COLUMNS分区

前面介绍的HASH、LIST、RANGE和KEY这四种分区中，分区条件必须是整数，若不是整数，则需化成整数，如YEAR(), TO_DAYS()等。MySQL5.5开始支持COLUMNS分区，分区根据直接比较可得，不需要转化为整数。其次，RANGE COLUMNS可对多个列的值进行分区。

COLUMNS支持以下类型：

         1）整数类型：INT、SMALLINT、TINYINT、BIGINT。

         2）日期类型：DATE、DATETIME

         3）字符串类型：CHAR、VARCHAR、BINARY和VARBINARY

子分区：

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

需要注意的问题：

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

l  如果在一个分区表上的任何分区上使用SUBPARTITION来明确定义任何子分区，那么就必须定义所有的子分区。

l  每个SUBPARTITION句必须包括子分区的一个名称

l  在每个分区内，子分区的名称必须是唯一的。

 

转载于:https://my.oschina.net/u/1015154/blog/803396