InnoDB(4.4) 约束

最新推荐文章于 2022-06-20 14:30:00 发布

GDUT_Ember

最新推荐文章于 2022-06-20 14:30:00 发布

阅读量373

点赞数

分类专栏： Innodb 文章标签： mysql

本文链接：https://blog.csdn.net/GDUT_Trim/article/details/116614482

版权

Innodb 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

数据完整性约束

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

数据的完整性是指数据库中的数据值均是正确的状态，比如说唯一不重复型、不为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约束

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语句命令来进行添加

CREATE TABLE parent(
	id INT NOT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB;

CREATE TABLE child(
	id INT,
	parent_id INT,
	FOREIGN KEY(parent_id) REFERENCES parent(id)
)ENGINE=INNODB;

在这里插入图片描述
新建外键成功

下面进行测试一下外键有什么功能

//给parent插入一条数据
INSERT INTO parent SELECT 1;
//给child也插入一条数据，并且关联上了父的id
INSERT INTO child SELECT 1,1;
//对parent数据进行删除（删除了的话，child的数据就无关联了）
DELETE FROM parent WHERE id = 1;
//对child数据进行新增（新增在parent表上无关联的数据）
INSERT INTO child SELECT 2,2;
//对child表进行删除
DELETE FROM child WHERE id = 1;

在这里插入图片描述

发现删除不了也新增不了，原因是这两个修改都会打破外键约束，如果删除了parent的数据，那么child表中存在某一条数据的外键无法匹配上parent，如果新增了一条child的数据，新增的数据的外键也是无法匹配上parent，所以都无法成功

对child表进行任意的删除是没有问题的

也就是说外键约束保持了父表与子表之间必须有所对应！！（一般来说，称被外键引用的表为父表，对应上面的栗子就是parent，引用外键的表为子表，对用上面的栗子就是创建外键结构的child）

接下来回到外键的定义

[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

外键定义时有ON DELETE和ON UPDATE，这两个表示在对父表进行DELETE和UPDATE操作时，对子表进行的操作，操作有如下的几类

CASCADE
SET NULL
NO ACTION
RESTRICT

CASCADE

CASCADE表示当父表发生DELETE或者UPDATE操作时，对子表相应外键匹配的数据也做一个DELETE或者UPDATE操作，即会保证父表与子表的同步。

SET NULL

SET NULL表示当父表发生DELETE或者UPDATE操作时，对子表相应外键匹配的数据要设为一个NULL值，这也带出了另外一个条件，就是该列要允许为NULL值，如果不为NULL就会报错

NO ACTION

NO ACTION表示当父表发生DELETE或者UPDATE操作时，就会抛出错误，不允许这类操作产生**（前提是该数据有子表与它有外键关联，否则还是可以删除成功的）**，这个是来源标准的SQL，是延时检查的（下面会说）

RESTRICT（MySQL默认）

RESTRICT和NO ACTION是一样的，也是当父表发生DELETE或者UPDATE操作时，就会抛出错误**（前提是该数据有子表与它有外键关联，否则还是可以删除成功的）**，不允许这类操作产生（如果在定义外键时候，没有定义ON DELETE和ON UPDATE的外键约束，就默认是RESTRICT），它与NO ACTION的区别就是，RESTRICT是即时检查的（下面会说）

上面4个约束都是针对父表发生情况的

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

父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别

拓展

在MySQL中，外键约束都是即时检查的，即SQL语句还没执行完成就会去进行判断（所以MySQL的NO ACTION和RESTRICT是一样的），但其他数据库，比如oracle，是支持延时检查的，也就是执行完SQL再去检查。

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

InnoDB存储引擎在外键建立时会给外键的列自动添加一个索引，可以很好地避免外键列上无索引而导致死锁问题的产生（这个不太懂，好像ORACLE设置才会这样，MySQL不知道有没有），同时，也避免了对于子表会查用全表扫描的方式去查找数据，提高了搜索效率

在这里插入图片描述

外键的优缺点

外键对于参照性约束（SET ENUM）等，拥有更好的完整性约束。

但是对于数据的导入操作，外键往往会导致在外键约束的检查上会浪费太多的时间（因为通常外键的值我们都不会去进行修改，而且会建立索引，占用一定的空间），外键会对导入的每一行数据都会进行外键检查。

但用户是可以设置在导入过程中忽视对外键的检查的（通过改变foreign_key_checks属性）

set foreign_key_checks = 0; //关闭检查
set foreign_key_checks = 1; //开启坚持

GDUT_Ember

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录