外键约束(foregin key)

业务背景:请设计数据库表,来描述班级和学生的信息

  1. 第一种方案:班级和学生存储在一张表中
mysql> select * from students;
+--------+----------+-------+
| number | name     | class |
+--------+----------+-------+
|      1 | student1 |     1 |
|      2 | student2 |     1 |
|      3 | student3 |     1 |
|      4 | student4 |     2 |
|      5 | student5 |     2 |
|      6 | student6 |     2 |
+--------+----------+-------+
6 rows in set (0.00 sec)

这张表的缺点是:数据冗余,空间浪费,例如都是class1,但后面class字段却都要写

  1. 第二种方案:班级一张表,学生一张表

班级表:班级代号为1的是1班,班级代号为2的是2

mysql> select * from students_class;
+----------+--------------+
| class_id | class_number |
+----------+--------------+
|        1 |            1 |
|        2 |            2 |
+----------+--------------+
2 rows in set (0.00 sec)

学生表:

mysql> select * from students;
+--------+----------+----------+
| number | name     | class_id |
+--------+----------+----------+
|      1 | student1 |        1 |
|      2 | student2 |        1 |
|      3 | student3 |        1 |
|      4 | student4 |        2 |
|      5 | student5 |        2 |
|      6 | student6 |        2 |
+--------+----------+----------+
6 rows in set (0.00 sec)

这样就通过class_id这样直接就可以查到是几班的了

但是当class_id字段没有任何约束的时候,可能会导致数据无效,可能会出现一个3或者4等其他数字,从而在students_class无法关联起来,所以为了保证class_id字段中值具有有效性,就需要在class_id字段中添加外键约束,那么class_id就是外键字段了,其中的每一个值就是外键值
注:students是子表,students_class是父表

  • 删除表的顺序

    • 先删除子表,再删除父表
  • 创建表的顺序

    • 先创建父表,再创建子表
  • 删除数据的顺序

    • 先删除子表,再删除父表
  • 插入数据的顺序

    • 先插入父表,再插入子表

例:

创建students_class

mysql> create table students_class(class_number int primary key,class_name varchar(255));
Query OK, 0 rows affected (0.02 sec)

创建students

mysql> create table students(number int primary key auto_increment,name varchar(255),classnumber int,foreign key(classnumber) references students_class(class_number));
Query OK, 0 rows affected (0.01 sec)

students_class插入数据

mysql> insert into students_class(class_number,class_name) values(100,'Java class');
Query OK, 1 row affected (0.01 sec)

mysql> insert into students_class(class_number,class_name) values(101,'HTML class');
Query OK, 1 row affected (0.00 sec)

students插入数据

mysql> insert into students(name,classnumber) values('YQHP-YuKi',100),('LowPingDog',100),('LiSang',100),('XP',101),('Richard',101),('HH',101);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

查看下students_class数据

mysql> select * from students_class;
+--------------+------------+
| class_number | class_name |
+--------------+------------+
|          100 | Java class |
|          101 | HTML class |
+--------------+------------+
2 rows in set (0.00 sec)

查看下students数据

mysql> select * from students;
+--------+------------+-------------+
| number | name       | classnumber |
+--------+------------+-------------+
|      1 | YQHP-YuKi  |         100 |
|      2 | LowPingDog |         100 |
|      3 | LiSang     |         100 |
|      4 | XP         |         101 |
|      5 | Richard    |         101 |
|      6 | HH         |         101 |
+--------+------------+-------------+
6 rows in set (0.00 sec)

当我试图在students中插入一个不是students_classclass_number的值时,就会出现报错

mysql> insert into students(name,classnumber) values('DuDu',45);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`testmysql`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`classnumber`) REFERENCES `students_class` (`class_number`))

外键值为NULL,其实也是可以的

mysql> insert into students(name) values('Sarry');
Query OK, 1 row affected (0.00 sec)

注:子表中的外键引用的父表中的某个字段,不一定必须是主键,但必须具有unique约束

存储引擎

存储引擎是Mysql中特有的一个术语,其他数据库中没有(Oracle中有,但是不叫这个名字),实际上存储引擎是一个表存贮/组织数据的方式,不同的存储引擎,表存储数据的方式不同

添加/指定存储引擎

例:

mysql> show create table students;

| Table    | Create Table                                                   
| students | CREATE TABLE `students` (
  `number` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `classnumber` int DEFAULT NULL,
  PRIMARY KEY (`number`),
  KEY `classnumber` (`classnumber`),
  CONSTRAINT `students_ibfk_1` FOREIGN KEY (`classnumber`) REFERENCES `students_class` (`class_number`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

1 row in set (0.00 sec)

注:在建表的时候可以在最后面小括号)的右边使用ENGINE来指定存储引擎,CHARSET来指定这张表的字符编码方式

例:

mysql> create table t_test(id int primary key,name varchar(255))engine=InnoDB default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)

查看一下

mysql> show create table t_test;

| t_test | CREATE TABLE `t_test` (
  `id` int NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Mysql支持的存储引擎

使用show engines \G就可以查看到当前Mysql支持哪些存储引擎

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

ERROR:
No query specified

Mysql支持9个存储引擎,版本不同,支持情况不同

Mysql常用的存储引擎
  • MyISAM存储引擎

    • 它管理的表具有以下特征:

      • 使用三个文件表示每个表
      • 格式文件:存储表结构的定义mytable.frm
      • 数据文件:存储表行的内容mytable.MYD
      • 索引文件:存储表上索引mytable.MYI
    • 注:对于一张表来说,只要是有主键,或者加有unique约束的字段上会自动创建索引
    • MyISAM存储引擎优点:可被转换为压缩,只读表来节省空间
  • InnoDB存储引擎

    • 这是Mysql默认的存储引擎,同时也是一个重量级的存储引擎
    • InnoDB支持事务,支持数据库崩溃后自动恢复机制
    • InnoDB存储引擎最主要的特点:非常安全
    • 它管理的表具有下列主要特征:

      • 每个InnoDB表在数据库目录中以.frm格式文件表示
      • InnoDB表空间tablespace被用于存储表的内容
      • 提供一组用来记录事务性活动的日志文件
      • COMMIT(提交),SAVEPOINTROLLBACK(回滚)支持事务处理
      • 提供全ACID兼容
      • Mysql服务器奔溃后提供自动恢复
      • 多版本MVCC和行级锁定
      • 支持外键以及引用的完整性,包括级联删除和更新
    • InnoDB最大的特点就是支持事务:以保证数据的安全,效率不是很高,并且也不能压缩,不能转换为只读.不能很好的节省存储空间
  • MyISAM存储引擎

    • 使用MyISAM存储引擎的表,其数据存储在内存中,且行的长度固定,这两个特点使得MyISAM存储引擎非常快
    • MyISAM存储引擎管理的表具有以下列特征:

      • 在数据库目录内,每个表均以.frm格式的文件表示
      • 表数据及索引被存储在内存中
      • 表级锁机制
      • 不能包含TEXTBLOB字段
    • MyISAM存储引擎以前被称为HEAP引擎
    • MyISAM引擎优点:查询效率是最高的,不需要和硬盘交互
    • MyISAM引擎缺点:不安全,关机断电之后数据就消失了,因为数据和索引都是存储在内存当中的
事务概述

一个事务其实就是一个完整的业务逻辑,是一个最小的工作单元,不可再分.而完整业务逻辑就如同银行转账,例如A账户向B账户中转账10000元,首先要从A账户的钱中减去10000元,再在B账户的钱中加上10000元.以上的操作就是一个最小的工作单元,要么同时成功,要么同时失败,不可再分

事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行

只有DML语句才会有事务

只有insert,delete,update三个DML语句才与事务有关,因为只有这三个语句是对数据库中数据进行增,删,改,一旦涉及到数据的增,删,改,那么就一定要考虑数据安全问题

事务实现原理

因为事务是InnoDB存储引擎独有的,所以InnoDB提供一组用来记录事务性活动的日志文件

在事务的执行过程中,每一条DML的操作都会记录到事务性活动的日志文件中,在事务的执行过程中,我们可以提交事务,也可以回滚事务

  • 提交事务

    • 清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中
    • 提交事务标志着事务的结束,并且是一种全部成功的结束
  • 回滚事务

    • 将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
    • 回滚事务标志着事务的结束,并且是一种全部失败的结束
如何提交事务与回滚事务

提交事务命令:commit

回滚事务命令:rollback(回滚永远只能回滚到上一次的提交点)

Mysql默认情况是自动提交事务,即每执行一条DML语句,则提交一次,所有我们直接使用rollback进行回滚是无效的,必须得事先关闭自动提交

例:

开启事务transaction

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

插入数据

mysql> insert into students(name,classnumber) values('YQHP-YuKi',100),('LowPingDog',100),('LiSang',100),('XP',101),('Richard',101),('HH',101);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

查看一下

mysql> select * from students;
+--------+------------+-------------+
| number | name       | classnumber |
+--------+------------+-------------+
|      9 | YQHP-YuKi  |         100 |
|     10 | LowPingDog |         100 |
|     11 | LiSang     |         100 |
|     12 | XP         |         101 |
|     13 | Richard    |         101 |
|     14 | HH         |         101 |
+--------+------------+-------------+
6 rows in set (0.00 sec)

进行回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

再查看一下,发现插入的数据没了

mysql> select * from students;
Empty set (0.00 sec)

其实自动提交是不符合我们的开发习惯的,因为一个业务通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全,必须要求同时成功之后再提交,所以不能执行一条就提交一条

事务特性
  • 原子性(Atomicity)

    • 说明事务是最小的工作单元,不可再分
    • 一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节,事务在执行过程中发生错误,会被回滚到事务开始前的状态,就像这个事务从来没有执行过一样
  • 一致性(Consistency)

    • 所有事物要求在同一个事务当中,所有操作必须同时成功,或者同时失败,以保证数据的一致性
    • 在事务开始之前和事务结束之后,数据库的完整性没有被破坏,这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度,串联性以及后续数据库可以自发性地完成预定工作
  • 隔离性(Isolation)

    • 事务A和事务B之间具有一定的隔离
    • 数据库允许多个并发事务同时对其数据进行读写和修改的功能,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致,事务隔离分为不同级别,包括读未提交Read uncommitted,读提交read committed,可重复读repeatable read,串行化Serializable
  • 持久性(Durability)

    • 事务最终结束的一个保障,事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘上
    • 事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失
4种隔离级别
  • 读未提交Read uncommitted(最低的隔离级别)

    • 读未提交就是事务A可以读取到事务B未提交的数据,这种隔离级别存在的问题就是脏读现象(Dirty Read),例如:我转给朋友100元,但不小心输错了,输成了1000元,虽然我没有点转账键,但我朋友却发现我转了1000给他,我发现了问题立马改回成了100元,回滚并提交了正确的100给他,想要解决脏读现象提高隔离级别即可
    • 所以这种隔离级别一般都是理论上的,大多数的数据库的隔离级别都是二档起步
  • 读提交read committed

    • 读已提交就是事务A只能读取到事务B提交之后的数据,这种隔离级别解决了脏读问题,但其实它也存在问题的,就是不可重复读取数据
    • 不可读取重复数据就是在事务开启之后,第一次读取到的数据是3条,当前事务还没有结束,但有可能在第二次重新读取是,读取到的数据就是4条了,这就是读提交,若有事务对数据进行更新,读操作事务就要等待这个更新操作事务提交后才能读取数据,若想解决不可重复读取问题,可重复读即可解决
    • 这种隔离级别是比较真实的数据,每次读到的数据是绝对真实的
    • Oracle数据库默认的隔离级别
  • 可重复读repeatable read

    • 可重复读就是事务A开启之后,不管是多久,每一次在事务A中读取到的数据都是一致的,即便事务B已经将数据给修改了,并且提交了,事务A读取到的数据还是没有发生变化的
    • 可重复读解决了不可重复读的问题,但也产生了一个问题,那就是幻读
时间点事务A事务B
1开启事务
2 开启事务
3查询数据YQHP-YuKi,不存在
4 插入数据YQHP-YuKi,成功
5 提交事务
6查询数据YQHP-YuKi,失败
7插入数据YQHP-YuKi,不成功
    • 事务A到最后即查不到数据有插入不成功,这个YQHP-YuKi就像幻象一样,无缘无故的存在着
    • Mysql中默认的事务隔离级别就是可重复读
    • 串行化Serializable(最高的隔离级别)

      • 在该级别下,事务串行化顺序执行,可以避免脏读,不可重复度和幻读,但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用
    • 大多数数据库默认的事务隔离界别是读提交Read committed,比如Sql Server,Oracle,Mysql的默认隔离级别是可重复读Repeatable read
    Last modification:March 10, 2021
    If you think my article is useful to you, please feel free to appreciate