InnoDB(4(1)，java 技术架构知识

• 对错误数据的约束
  

• ENUM和SET约束
  

• 触发器与约束
  

• 外键约束
  

• CASCADE
  

• SET NULL
  

• NO ACTION
  

• RESTRICT（MySQL默认）
  

• Set default(MySQL自带的，但InnoDB不能识别)
  

• InnoDB在添加外键时做了什么？
  

• 外键的优缺点
  

数据完整性约束

首先，我们先来认识一下什么是数据的完整性

数据的完整性是指数据库中的数据值均是正确的状态，比如说唯一不重复型、不为 NULL 型。

关系型数据库系统和文件系统的一个不同点是，关系型数据库本身就可以保证存储数据结构的完整性，不需要应用程序的控制，而文件系统却需要在程序段进行控制（需要程序段去看哪些数据违法，然后进行剔除）。几乎所有的关系型数据库都提供了约束机制，该机制提供了一条强大而且简易的途径去保证数据库中数据的完整性

在 InnoDB 存储引擎表中，域完整性（即保证数据每列的值满足特定条件）可以通过以下几种途径来保证

• 选择合适的数据类型确保该列的数据值满足特定类型条件

• 外键约束（Foreign 约束）

• 编写触发器

• DEFAULT 约束

在 InnoDB 存储引擎表中，参照完整性（即保证两张表之间的关系），可以通过以下途径来保证

• 外键约束

对于 InnoDB 存储引擎而言，提供了以下几种约束

• Primary key 主键约束

• Unique key 唯一约束

• Foreign Key 外键约束

• Default

• NOT NULL 非空约束

约束的创建和查找

约束的创建可以采用以下两种方式进行

• 表建立时就进行约束定义

• 利用 ALTER TABLE 命令去进行修改表，增加创建约束

举个栗子

CREATE TABLE t1(

id INT,

name VARCHAR(20),

id_card CHAR(18),

PRIMARY KEY(id),

UNIQUE KEY(name)

)ENGINE=INNODB;

//看一下表的索引情况

SELECT constraint_name,constraint_type FROM information_schema.TABLE_CONSTRAINTS

WHERE table_schema = 'db_book' AND table_name = 't1';

可以看到，里面有一个 Primary key 约束和一个 Unique 约束

下面使用 ALTER TABLE 的方式进行添加约束

//给其增加一个唯一约束，格式为：add unique key 约束名(约束列)

ALTER TABLE t1 ADD UNIQUE KEY uk_id_card(id_card);

接下来看下使用 Foreign Key 的约束

CREATE TABLE p(

id INT,

u_id INT,

PRIMARY KEY(id),

FOREIGN KEY(u_id) REFERENCES p (id)

)ENGINE=INNODB;

//同样使用 SQL 去看一下这个约束

SELECT constraint_name,constraint_type FROM information_schema.TABLE_CONSTRAINTS

WHERE table_schema = 'db_book' AND table_name = 'p';

约束和索引的区别

约束和索引建立的方式是一样的，其实建立一个约束也就是建立一个索引

但要分清楚的是约束和索引的概念，约束是一个逻辑的概念，是用来保证数据的完整性，而索引则是一个数据结构，是用来存储数据页或其他数据的。

对错误数据的约束

在某些默认设置下，MySQL 数据库会允许非法的或者不正确的数据进行插入和更新，又或者会在数据库内部进行一个转换，转换成合法的数据，比如向 NOT NULL 的字段插入一个 NULL 值，在某些情况下，MySQL 数据库会将其更改为 0 然后再进行插入，又或者往 DATE 类型的类中，插入了 2021-02-30 这样的非法数据。这证明了数据库本身没有对数据的正确性进行约束。

要让 MySQL 对添加的数据进行合法性约束，需要改变 SQL_MODE(在 MySQL 系列中提到过)，改为严格模式（STRICT_TRANS_TABLES）,这样 MySQL 就会对输入值的合法性进行约束，而且针对不同的错误，也会提示不同的内容。

ENUM 和 SET 约束

《Android学习笔记总结+最新移动架构视频+大厂安卓面试真题+项目实战源码讲义》
浏览器打开：qq.cn.hn/FTe 免费领取

MySQL 数据库并不支持传统的 CHECK 约束（CHECK 约束是用来检验数据是否为特定的值（不是指 Null），比如 male 或者 female，0 或者 1），但是可以通过 ENUM 和 SET 类型来进行解决。

CREATE TABLE a(

id INT,

sex ENUM('male','female')

)ENGINE=INNODB;

INSERT INTO a SELECT 1,"malemale";

CREATE TABLE b(

id INT,

sex SET('male','female')

)ENGINE=INNODB;

INSERT INTO b SELECT 1,"malemale";

可以看到两条 SQL 在控制台报都错了，对非法的输入值进行了约束，但是只限于对离散数值的约束，即不可以规定一系列连续范围的值，比如 0~10 这样，所以说 ENUM 和 SET 还是无能用力，这时用户就需要使用到触发器来实现对于值域的约束

触发器与约束

我们可以通过触发器去实现对值域的约束

首先，我们要先了解一下什么是触发器

触发器的作用就是在执行了 INSERT、DELETE 和 UPDATE 命令之前或之后自动调用 SQL 命令或存储过程

下面是创建触发器的命令

create

[definer = {user|CURRENT_USER}]

TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE

ON tbl_name FOR EACH ROW trigger_stmt

一个表最多可以有 6 个触发器，即分别为 INSERT、UPDATE 和 DELETE 这三个 DDL 语句，每个 DDL 语句又可以绑定是 BEFORE 还是 AFTER（所以是 3 * 2 = 6）。

• Before 和 After 是代表触发器发生的时间，表示是在每行操作的之前发生还是之后发生

• MySQL 数据库只支持 FOR EACH ROW 的触发方式，即按每行记录进行触发（即对行记录发生变化就会触发）

• INSERT、UPDATE 和 DELETE 是代表触发器发生的条件，即新增、修改和删除行记录会进行触发

通过触发器，就可以去实现一些 MySQL 数据库本身并不支持的一些特性，比如高级复制，审计（就是指监督数据）等特性

举个栗子

比如有一张用户消费表，每次用户购买物品之后，其金额应该是减少的，若此时有人恶意攻击，做了一个减去一个复制的操作，这样用户的钱在购买了商品之后反而增加了

//创建一张消费表

CREATE TABLE usercash(

userid INT NOT NULL,

cash INT UNSIGNED NOT NULL

)ENGINE=INNODB;

//初始化某个用户金额

INSERT INTO usercash SELECT 1,1000;

//恶意攻击的用户进行减去负值操作

UPDATE usercash SET cash = cash-(-20) WHERE userid = 1;

上面的 SQL 对于数据库来说是没有任何问题的，都是可以正常运行的，但是这从业务的逻辑上来说，这是绝对错误的，因为消费就意味着要去减去一个正值而不是负值，我们可以通过使用触发器来约束这个逻辑行为**(触发器是可以通过 new 和 old 对象去获取修改后和修改前的数据)**

//创建一个报错表

CREATE TABLE usercash_err_log(

userid INT NOT NULL, //记录出错的用户

old_cash INT UNSIGNED NOT NULL, //原来的金额

new_cash INT UNSIGNED NOT NULL, //修改后的金额

user VARCHAR(30),//谁插入这条报错数据的（一般为数据库用户）

TIME DATETIME //记录时间

)ENGINE=INNODB;

//创建存储过程

DELIMITER $$

//只要执行了 UPDATE 操作就会触发，并且在数据更新后进行触发

CREATE TRIGGER tgr_usercash_update BEFORE UPDATE ON usercash

//触发方式是，每一行都会触发

FOR EACH ROW

//开始触发器处理的逻辑

BEGIN

//判断修改后的金额，是不是大于修改钱的金额

IF new.cash-old.cash > 0 THEN

//如果是，就表明发生错误，要往报错表里插入信息

INSERT INTO usercash_err_log

SELECT old.userid,old.cash,new.cash,USER(),NOW();

//将修改后的金额改回修改钱的金额（即还原数据,通过 set 关键字）

SET new.cash = old.cash;

END IF;

END;

$$

//下面进行非法测试

INSERT INTO usercash SELECT 2,1000;

UPDATE usercash SET cash = cash - (-20) WHERE userid = 2;

可以看到报错表里面的信息

金额表里面也是没有发生修改

外键约束

外键是用来保证参照完整性的，MySQL 的 MyISAM 存储引擎本身并不支持外键，对于外键的定义只是起到了一个注释的作用，但 InnoDB 的存储引擎则完整支持外键约束。

InnoDB 可以使用下面的语句去定义一个外键

[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

其实，用户可以在创建表的时候就添加外键，也可以在添加后使用 ALTER 语句命令来进行添加