大家好,又见面了,我是全栈君,今天给大家准备了Idea注册码。
《MySQL技术内幕:InnoDB存储引擎》本书从源代码的角度深度解析了InnoDB的体系结构、实现原理、工作机制,并给出了大量实践,本着将书读薄的思想,循序渐进的记录对本书学习的读书笔记。
关系型数据库系统和文件系统的一个不同点是,关系数据库本身能保证存储数据的完整性,不需要应用程序的控制,而文件系统一般需要在程序端进行控制。当前几乎所有的关系型数据库都提供了约束(constraits)机制,该机制提供了一条强大而简易的途径来保证数据库中的数据完整性,一般来说,数据完整性有以下三种形式:
(1)实体完整性保证表中有一个主键,在InnoDB存储引擎中,用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性,用户还可以编写一个触发器来保证数据完整性、
(2)域完整性保证数据每列的值满足特定的条件。在InnoDB存储引擎中,域完整性可以通过以下途径来保证:
a. 选择适合的数据类型确保一个数据值满足条件
b. 外键(Foreign Key)约束
c. 编写触发器
d. 还可以考虑用default约束作为强制域完整性的一个方面
(3)参照完整性保证两张表之间的关系,InnoDB存储引擎提供了以下几种约束:
a. primary key
b. unique key
c. foreign key
d. default
e. not null
1. 约束的创建和查找
约束的创建有以下两种方式:
(1)表建立时就进行约束定义
(2)利用alter table命令来进行创建约束
以下几点需要关注和注意:
a. 对Unique Key(唯一索引)的约束,用户除了在创建时约定,还可以通过Create Unique Index来创建
b. 对于主键约束耳音,其默认约束名为PRIMARY,而对于Unique Key约束而言,默认约束名和列名一样,当然也可以人为的指定Unique Key的名字,Foreign Key约束似乎会有一个比较神秘的默认名称
1.1 例1:创建表u,设置一个primary key和unique key
mysql> Create table u( -> id int, -> id_card varchar(18), -> name varchar(20), -> primary key(id), -> unique key(name) -> )engine=InnoDB; Query OK, 0 rows affected (0.56 sec) mysql> select constraint_name,constraint_type -> from -> information_schema.table_constraints -> where table_schema='test' and table_name='u'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | name | UNIQUE | +-----------------+-----------------+ 2 rows in set (0.01 sec)
可以看到,约束名如上面所说,主键的约束名为PRIMARY,唯一索引的默认约束名与列名相同。
1.2 例2:alter table创建约束
mysql> alter table u -> add unique key uk_id(id_card); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select constraint_name,constraint_type -> from -> information_schema.table_constraints -> where table_schema='test' and table_name='u'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | name | UNIQUE | | uk_id | UNIQUE | +-----------------+-----------------+ 3 rows in set (0.00 sec)
1.3 例3:Foreign key的约束
mysql> create table p( -> id int, -> u_id int, -> primary key(id), -> foreign key(u_id) references p(id) -> )engine=InnoDB; Query OK, 0 rows affected (0.41 sec) mysql> select constraint_name,constraint_type -> from -> information_schema.table_constraints -> where table_schema='test' and table_name='p'; +-----------------+-----------------+ | constraint_name | constraint_type | +-----------------+-----------------+ | PRIMARY | PRIMARY KEY | | p_ibfk_1 | FOREIGN KEY | +-----------------+-----------------+ 2 rows in set (0.00 sec)
在上面的例子中,通过information_schema架构下的表table_constraints来查看当前MySql库下所有的约束信息。
2. 对错误数据的约束
在某些默认设置下,MySql数据库允许非法或不正确的数据的插入或更新,又或者可以在数据库内部将其转化为一个合法的值,如向not null的字段插入一个null值,MySql数据库会将其更改为0再进行插入,因此数据库本身没有对数据的正确性进行约束。
2.1 例1
mysql> Create table a( -> id int not null, -> data date not null -> )engine=InnoDB; Query OK, 0 rows affected (0.20 sec) mysql> insert into a select NULL, '2009-02-20'; Query OK, 1 row affected, 1 warning (0.05 sec) Records: 1 Duplicates: 0 Warnings: 1
mysql> select * from a; +----+------------+ | id | data | +----+------------+ | 0 | 2009-02-20 | +----+------------+ 1 row in set (0.00 sec)
通过设置参数set sql_mode=’strict_trans_tables’;对MySql数据库的输入值进行了约束,而且针对不同的错误提示错误内容也不同。
2.1 例2:enum与set约束
mysql> Create table a( -> id int, -> sex enum('male','female') -> )engine=InnoDB; Query OK, 0 rows affected (0.17 sec) mysql> insert into a select 1,'male'; Query OK, 1 row affected (0.06 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into a select 2,'hello'; ERROR 1265 (01000): Data truncated for column 'sex' at row 1
3. 触发器与约束
3.1 触发器的认识
触发器的作用是在执行insert,delete和update命令之前或之后自动调用sql命令或存储过程
3.1.1 触发器的创建
Create [definer = { user | current_user}] trigge trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE on tble_name FOR EACH ROW trigger_stmt
(1)最多可以为一个表建立6个触发器,即分别为insert,update,delete的before和after各定义一个
(2)只有表才支持触发器,视图不支持(临时表也不支持)
(3)如果before触发器失败,则MySQl将不执行请求的操作,此外如果before触发器或语句本身失败,MySql将不执行after触发器(如果有的话)
3.1.2 触发器的删除
DROP TRIGGER trigger_name;
注:触发器不能更新或覆盖,为了修改一个触发器,必须先删除它,然后再重新创建
3.2 触发器约束
假设有张用户消费表,每次用户购买一样物品后其金额都是减的,若这时有不坏好意的用户做了一个类似减去一个负值的操作,这样用户的钱没有减少反而不断增加
mysql> Create table usercash( -> userid int not null, -> cash int unsigned not null -> , -> Primary key(userid)) -> engine=InnoDB; Query OK, 0 rows affected (0.16 sec) mysql> insert into usercash select 1,1000; Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update usercash set cash=cash-(-20) where userid = 1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from usercash; +--------+------+ | userid | cash | +--------+------+ | 1 | 1020 | +--------+------+ 1 row in set (0.00 sec)
上面运行的SQL语句对数据库来说没有任何的问题,都可以正常运行,不会报错。但从业务逻辑上来说,这是绝对错误的。下面采用触发器来约束这个逻辑行为:
mysql> Create table usercash_error_log( -> userid int not null, -> old_cash int unsigned not null, -> new_cash int unsigned not null, -> user varchar(30), -> time datetime, -> primary key(userid) -> )engine=InnoDB; Query OK, 0 rows affected (0.23 sec) mysql> delimiter // mysql> Create trigger tgr_usercash_update before update on usercash -> for each row -> begin -> if new.cash -old.cash>0 then -> insert into usercash_error_log select old.userid, old.cash,new.cash,US ),NOW(); -> set new.cash=old.cash; -> end if; -> end// Query OK, 0 rows affected (0.05 sec) mysql> delimiter ; mysql> delete from usercash; Query OK, 1 row affected (0.05 sec) mysql> insert into usercash select 1,1000; Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> update usercash set cash=cash-(-20) where userid=1; Query OK, 0 rows affected (0.11 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from usercash; +--------+------+ | userid | cash | +--------+------+ | 1 | 1000 | +--------+------+ 1 row in set (0.00 sec) mysql> select * from usercash_error_log; +--------+----------+----------+----------------+---------------------+ | userid | old_cash | new_cash | user | time | +--------+----------+----------+----------------+---------------------+ | 1 | 1000 | 1020 | root@localhost | 2018-06-01 15:09:51 | +--------+----------+----------+----------------+---------------------+ 1 row in set (0.00 sec)
可以看出这次对于异常的数据更新通过触发器将其保存到了usercash_error_log。此外该触发器还记录了操作该SQL语句的用户和时间。
4. 外键约束
外键用来保证参照完整性,MySQL数据库的MyIsAM存储引擎本身并不支持外键,对于外键的定义只是起到一个注释的作用,而InonoDB存储引擎则完整支持外键约束。
一般来说,称被引用的表为父表,引用的表称为子表,外键定义时的on delete和on update表示在对父表进行delete和updata操作时,对子表所做的操作。可定义的子表操作有:
(1)CASCADE
表示当父表发生delete和update操作时,对相应的子表中的数据页进行delete和update操作
(2)SET FULL
表示当父表发生delete和update操作时,相应的子表中的数据被更新为NULL值,但是子表中对应的列必须允许为NULL值
(3)NO ACTION
表示父表发生delete或update操作时,抛出错误,不允许这类操作发生
(4)RESTRICT
表示父表发生delete或update操作时,抛出错误,不允许这类操作发生,如果定义外键时没有指定on delete或on update,RESTRICT就是默认的外键设置
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/120124.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...