MySQL-InnoDB表

索引组织表

在InnoDB中，表是按照主键顺序组织存放的，被称为索引组织表。每张表都有主键，如果没有显性指定，会自动按照如下规则选定

• 首先判断是否有非空的唯一索引(unique not null),有则为主键

• 否则自动创建一个6字节大小的指针

可以使用_rowid来查看单列的主键值，多列共同形成的主键就看不了。

---

InnoDB逻辑存储结构

表空间：存放表的所有数据

段：分为数据段，索引段，回滚段等。数据段为B+树的叶子节点，索引段为非叶子节点。

区：区由连续的页组成，每个区的大小为1MB,InnoDB每次会申请4-5个区来保证数据的连续性。默认下，每个页为16KB,一个区有64个连续的页。

页：页是InnoDB磁盘管理的最小单位，通过innodb_page_size可以修改页的大小。常见的页类型有数据页(B-tree Node),undo页，系统页，事务数据页，插入缓存位图页，插入缓存空闲列表页，未压缩的二进制大对象页，压缩的二进制大对象页

---

约束

数据完整性

实体完整性，保证表中有一个主键。用户可以定义Primary Key和Unique Key来保证实体的完整性。用户可以通过编写触发器保证数据完整性。

域完整性，1.选择合适的数据类型;2.外键约束;3.编写触发器;4.考虑用Default约束强制域完整性

参照完整性，Primary Key,Unique Key,Foreign Key,Default,Not Null

//查看约束
select constraint_name, constraint_type
from information_schema.TABLE_CONSTRAINTS
where table_schema='sakila' and table_name='store'\G;

/*
*************************** 1. row ***************************
CONSTRAINT_NAME: PRIMARY
CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_NAME: idx_unique_manager
CONSTRAINT_TYPE: UNIQUE
*************************** 3. row ***************************
CONSTRAINT_NAME: fk_store_address
CONSTRAINT_TYPE: FOREIGN KEY
*************************** 4. row ***************************
CONSTRAINT_NAME: fk_store_staff
CONSTRAINT_TYPE: FOREIGN KEY
*/

//增加约束
alter table store
add unique key uk_id_card(id_card);

//进一步了解外键
select * 
from information_schema.REFERENTIAL_CONSTRAINTS 
where constraint_schema='sakila' 
and table_name='store'\G;

/*
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: sakila
          CONSTRAINT_NAME: fk_store_address
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: sakila
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: CASCADE
              DELETE_RULE: RESTRICT
               TABLE_NAME: store
    REFERENCED_TABLE_NAME: address
*************************** 2. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: sakila
          CONSTRAINT_NAME: fk_store_staff
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: sakila
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: CASCADE
              DELETE_RULE: RESTRICT
               TABLE_NAME: store
    REFERENCED_TABLE_NAME: staff

*/

触发器

CREATE [DEFINER = { user | CURRENT_USER}]
TRIGGER trigger_name BEFORE|AFTER
INSERT|UPDATE|DELETE
ON table_name FOR EACH ROW stmt;

//用户有一张购物卡
create table card(
    id int not null primary key auto_increment,
    cash int unsigned not null);

//金额有1000
insert into card values(1,1000);

//一位大佬，每次买东西，金额都能增加
//但这显然是不可以的
update card set cash = cash - (-20) where id = 1;

//写个触发器来约束这种行为
mysql> create table card_err(
    -> id int not null,
    -> old_cash int unsigned not null,
    -> new_cash int unsigned not null,
    -> user varchar(30),
    -> time DATETIME);

mysql> create trigger tgr_card_update
    -> before update on card
    -> for each row
    -> begin
    -> if new.cash-old.cash > 0 then
    -> insert into card_err 
    -> select old.id,old.cash,new.cash,USER(),NOW();
    -> set new.cash = old.cash;
    -> end if;
    -> end;
    -> ??

//重复UPDATE，钱不会改变了，日志如下
+----+----------+----------+----------------+---------------------+
| id | old_cash | new_cash | user           | time                |
+----+----------+----------+----------------+---------------------+
|  1 |     1020 |     1040 | root@localhost | 2017-09-30 14:49:16 |
|  1 |     1020 |     1040 | root@localhost | 2017-09-30 14:49:31 |
+----+----------+----------+----------------+---------------------+

外键约束

[CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
REFERENCES table_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

//reference_option有
RESTRICT | CASCADE | SET NULL | NO ACTION

• CASCADE: 当父表发生DELETE和UPDATE时，对子表中的对应数据也DELETE和UPDATE

• SET NULL: 当父表发生DELETE和UPDATE时，对子表中的对应数据变成NULL

• NO ACTION: 当父表发生DELETE和UPDATE时，抛出错误，表示不允许

• RESTRICT: 当父表发生DELETE和UPDATE时，抛出错误，表示不允许

---

视图

视图只是一个虚表，用于简化复杂的查询，并有时隐藏一些信息，起安全层的作用。

CREATE
[OR REPLACE]
[ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(col_list)]
AS select_stmt
[WITH [CASCADED | LOCAL] CHECK OPTION]

视图分为可更新视图和查询视图，WITH CHECK OPTION针对于可更新视图。

mysql> create table t(
    -> id int);

mysql> create view v1
    -> as
    -> select * from t where id < 10;

mysql> insert into v1 select 5;

mysql> insert into v1 select 10;

mysql> insert into v1 select 15;

mysql> select * from v1;
+------+
| id   |
+------+
|    5 |
+------+

mysql> select * from t;
+------+
| id   |
+------+
|    5 |
|   15 |
|   10 |
+------+

//加上WITH CHECK OPTION
mysql> alter view v1
    -> as
    -> select * from t where id < 10
    -> with check option;

mysql> insert into v1 select 15;
ERROR 1369 (HY000): CHECK OPTION failed 'test.v1'

查看基表和视图

//基表
mysql> select * from information_schema.TABLES
    -> where table_type='BASE TABLE'
    -> and table_schema=database();
/*
  TABLE_CATALOG: def
   TABLE_SCHEMA: test
     TABLE_NAME: t
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Dynamic
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 5461
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2017-09-30 15:15:46
    UPDATE_TIME: 2017-09-30 15:16:37
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: 
*/

mysql> select * from information_schema.VIEWS
    -> where table_schema=database();
/*
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: test
          TABLE_NAME: v1
     VIEW_DEFINITION: select `test`.`t`.`id` AS `id` from `test`.`t` where (`test`.`t`.`id` < 10)
        CHECK_OPTION: CASCADED
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*/

由于MySQL不支持物化视图，只能通过触发器对假视图和基表之间建立关系。