非淡泊无以明志,非宁静无以致远。这篇文章主要讲述超硬核学习手册系列3事务视图篇——深入浅出MySQL的知识点,学习收藏必备相关的知识,希望能为你提供帮助。
1. 事务
1.1 事务的概述
1.1.1 概念引进小鱼给小猫转账,正常流程就是小鱼的账号减钱,而小猫的账号加钱对吧。可是假设,小鱼在转账过程中,小鱼的账号已经扣钱了,但是钱还没有到达小猫的账号上,此时服务器崩溃了。小猫的账号没有多出来的钱,但是小鱼的钱少了,数据出现了问题,你觉得这样的转账可以吗?当然不行的啦,所以这个时候就需要到了事务。
1.1.2 事务事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个SQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
1.1.3 事务的ACID四大特性
事务特性 | 含义 |
---|---|
原子性(Atomicity) | 事务中的各项操作,要么全部执行成功,要么全部执行失败。 |
一致性(Consistency) | 数据库总是从一个一致性状态转换为另一个一致性的状态。表示事务结束后系统状态一致。 |
隔离性(Isolation) | 表示多个事务并发访问时,事务之间是隔离的不可见的。一个事务不会影响到其他事务的运行。 |
持久性(Durability) | 表示一个事务一旦提交成功,他对数据库的数据操作是永久性的。 |
1.2.1 事务的分类
文章图片
- 我们先创建事务需要的数据库及数据:
CREATE TABLE IF NOT EXISTS account( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(20), balance DOUBLE ); INSERT INTO account(username, balance) VALUES(\'小鱼\', 1000), (\'小猫\', 1000);
文章图片
- 查看数据库的默认提交状态:
SHOW VARIABLES LIKE \'autocommit\'; -- 或者第二种: SELECT @@AUTOCOMMIT; -- 推荐
文章图片
文章图片
- 数据库默认的增删改都是默认提交状态的,我们之前做了这么多增删改也发现了吧,具体我们还是再来看看吧:
UPDATE account SET balance = 666 WHERE username=\'小鱼\'; SELECT * FROM account;
文章图片
我们执行修改语句,可以发现小鱼的账号确实少了,但是我们如果要实现转账等多功能,这种肯定不能满足我们的需求,总不能一个人的钱扣就扣了,另一个人的钱没有加上是吧。这是我就肯定不干的,哈哈。你给我,我倒是可以接受一下下的!
- 所以我们就需要把默认提交的功能给关闭,再进行转账功能的操作。就进行接下来的学习。
SQL语句 | 描述 |
---|---|
SET autocommit = 0; | 关闭默认提交 |
START TRANSACTION; | 开启事务 |
COMMIT; | 提交事务 |
ROLLBACK; | 回滚事务 |
文章图片
事务的执行可以分为两种:
- 成功:首先开启事务,编写事务中的多条SQL语句,成功就提交事务,一旦提交后,就不能再更改。
- 失败:首先开启事务,编写事务中的多条SQL语句,失败就需要回滚事务,保证数据正常。
UPDATE account SET balance = 1000 WHERE username=\'小鱼\';
UPDATE account SET balance = 1000 WHERE username=\'小猫\';
具体演示一下步骤:
- 成功:
-- 开启事务 SET autocommit = 0; START TRANSACTION; -- 编写事务SQL语句 UPDATE account SET balance = 800 WHERE username=\'小鱼\'; UPDATE account SET balance = 1200 WHERE username=\'小猫\'; -- 提交事务 COMMIT; SELECT * FROM account;
文章图片
可以看到,小鱼的账号确实少了200,而小猫的账号加了200。这样是正常的事务,提交成功。
- 失败:
-- 开启事务 SET autocommit = 0; START TRANSACTION; -- 编写事务SQL语句 UPDATE account SET balance = 500 WHERE username=\'小鱼\'; UPDATE account SET balance = 1500 WHERE username=\'小猫\'; -- 回滚事务 ROLLBACK; SELECT * FROM account;
文章图片
可以看到,小鱼想要继续转账300给小猫,但是我回滚了(这里暂时没办法失败再去回滚,只能自己设置回滚),小鱼的账号还是继续保持800,小猫的账号还是原封不动的1200。没有发生任何变化,感觉一切都没发生。保证了数据的一致性。
1.3.1 语法
- 设置回滚点:
```sql\\
savepoint 回滚点名字;
- 回到回滚点:
rollback to 回滚点名字;
-- 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 编写事务SQL语句
UPDATE account SET balance = 600 WHERE username=\'小鱼\';
UPDATE account SET balance = 1400 WHERE username=\'小猫\';
SAVEPOINT nz;
# 在此处设置回滚点
-- 再次编写事务SQL语句
UPDATE account SET balance = 400 WHERE username=\'小鱼\';
UPDATE account SET balance = 1600 WHERE username=\'小猫\';
-- 回滚到回滚点
ROLLBACK TO nz;
SELECT * FROM account;
文章图片
我们可以看到,我们进行两次修改操作,最终因为我们在修改小鱼到600的时候,设置了回滚点,在回滚的时候,只回滚到此处,而不是全部回滚。
1.4 事务的隔离级别
那并发可能会产生什么问题呢?
- 脏读:对于两个事务来说,一个事务读取到了另一个事务中已经更新了但是还没有提交的数据。
- 不可重复读:对于两个事务来说,一个事务中两次读取的数据内容不一致,即读取一个字段的数据后,但是另一个事务将其修改了,此时再次读取后数据就改变了。
- 幻读:对于两个事务来说,一个事务中多次读取,数据的数量不一样,即一个事务读取一个字段后,但是另一个事务多增加一些字段或者数据,此时再次读取后就会多出几行,出现幻觉的感觉!
首先介绍下事务的隔离级别,mysql为我们提供了四种隔离级别,让我们分别看看有什么吧
- read-uncommitted读取未提交:最低的隔离级别,读取尚未的提交的数据,也被为脏读,它可能会发生就是脏读现象和不可重复读和幻读现象。
- read-committed读已提交:可以读取并发事务中已经提交的数据,可以有效的阻止脏读,但是每次读取的值发生了改变,所以不可重复读和幻读仍有可能发生。
- repeatable-read可重复读:mysql的默认隔离级别,对同一字段的读取多次结果是一致的,可以阻止脏读和不可重复读,但是幻读仍会发生。那幻读就是本来我读取的只有一行的数据,此时再次读取可能多了一行,此时就是幻读了。
- serializable可串行化:最高的隔离级别,可以有效的解决脏读、不可重复读、幻读现象。但是效率会比较低。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
read-uncommitted读取未提交 | 可能会出现 | 可能会出现 | 可能会出现 |
read-committed读已提交 | 可以解决 | 可能会出现 | 可能会出现 |
repeatable-read可重复读 | 可以解决 | 可以解决 | 可能会出现 |
serializable可串行化 | 可以解决 | 可以解决 | 可以解决 |
进阶1:演示最低级别的read-uncommitted
- 如何查看全局事务隔离级别
show variables like \'%isolation%\'; select @@tx_isolation;
文章图片
- 设置下当前的事务隔离级别
set session transaction isolation level read uncommitted; show variables like \'%isolation%\';
文章图片
当然另一个也需要去改变,同样的操作!
- 【超硬核学习手册系列3事务视图篇——深入浅出MySQL的知识点,学习收藏必备】先对第一个窗口a进行事务处理
use data_test; set names gbk; select * from account; set autocommit = 0; START TRANSACTION; update account set balance = 400 where username=\'小鱼\';
文章图片
首先我们对窗口a开始事务,并修改了小鱼的金钱到400,此时没有提交,我们去看看窗口b进行查看得到的结果吧。
- 查看窗口b
use data_test; set names gbk; select * from account;
文章图片
可以看到我们窗口a没有提交数据,但是我们窗口b仍然可以读取窗口a未提交的数据,此时就是脏读现象。
- 那此时如果我们将窗口a的事务进行回滚,我们再去查看下窗口b的结果吧!
窗口a:
rollback;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1seKISYC-1626787875996)(https://cdn.jsdelivr.net/gh/onliyliuzepeng/ImgTest/MySQL/%E6%BC%94%E7%A4%BA%E6%9C%80%E4%BD%8E%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E6%93%8D%E4%BD%9C33.jpg)]
窗口b:
select * from account;
文章图片
我们可以看到我们窗口b相当于前后两次的结果都是不一样的,此时就是不可重复读现象。
- 那我们再进行演示下幻读现象
窗口a:
set autocommit = 0; START TRANSACTION; insert into account values(null, \'小鸟\', 1000);
文章图片
窗口b:
select * from account;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mdMPqn14-1626787875998)(https://cdn.jsdelivr.net/gh/onliyliuzepeng/ImgTest/MySQL/%E6%BC%94%E7%A4%BA%E6%9C%80%E4%BD%8E%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E6%93%8D%E4%BD%9C6.jpg)]
我们可以发现,我们在a窗口进行增加一行的操作,也是未提交的,但是在窗口b中再次读取,可以发现,为啥多出一行了,此时就会产生一种幻觉,此时就是幻读现象。
最终我们在窗口a中进行回滚操作,保证数据还原最初状态!
- 小结:
read-uncommitted读取未提交:它是最低的隔离级别,它什么并发现象都会发生。
- 设置下当前的事务隔离级别
set session transaction isolation level read committed; select @@tx_isolation;
文章图片
当然另一个也需要去改变,同样的操作!
- 进行脏读演示,看是否真的解决了脏读现象,两者都开始事务。
窗口a:
set autocommit = 0; START TRANSACTION; update account set balance = 400 where username=\'小鱼\';
文章图片
窗口b:
set autocommit = 0; START TRANSACTION; select * from account;
文章图片
可以发现我们的脏读现象真的可以避免了耶!
- 演示下不可重复读现象
以上面的状态,对窗口a进行提交事务,然后窗口b再次查看下数据
窗口a:
commit;
窗口b:
select * from account;
文章图片
我们可以发现,不可重复读的现象还是存在。
- 演示下幻读现象
窗口a:
set autocommit = 0; START TRANSACTION; insert into account values(3, \'小鸟\', 1000); commit;
文章图片
窗口b:
select * from account;
文章图片
可以发现,幻读现象还是会存在。
最后,窗口b进行rollback操作,保证下次演示的一致性。
- 总结:
read-committed读已提交:可以读取并发事务中已经提交的数据,可以有效的阻止脏读,但是不可重复读和幻读仍有可能发生。
- 设置下当前的事务隔离级别
set session transaction isolation level repeatable read; select @@tx_isolation;
文章图片
当然另一个也需要去改变,同样的操作!
脏读现象在第二级别中已经解决,这里就无须再演示了吧!有需要的客官可以尝试尝试呦!
- 演示不可重复读现象
我们先进行读取窗口b的最新数据:
文章图片
再进行窗口a修改数据:
set autocommit = 0; START TRANSACTION; update account set balance = 800 where username=\'小鱼\'; commit;
文章图片
再次对窗口b读取数据,看是否可以解决不可重复读现象:
文章图片
可以发现,前后两次的读取都是相同,代表了解决不可重复现象啦!
- 演示下幻读现象:
窗口a:
set autocommit = 0; START TRANSACTION; insert into account values(4, \'小月\', 1000); commit;
文章图片
我们在窗口b进行修改数据,对id> 2的金额修改成500
update account set balance = 400 where id > 2; select * from account;
文章图片
可以发现幻读现象还是会有,并且可能一不小心改了别人的数据呢
最后,窗口b进行rollback操作,保证下次演示的一致性。
- 总结:
repeatable-read可重复读:对同一字段的读取多次结果是一致的,可以阻止脏读和不可重复读,但是幻读仍会发生。
- 设置下当前的事务隔离级别
set session transaction isolation level serializable; select @@tx_isolation;
文章图片
当然另一个也需要去改变,同样的操作!
不可重复读现象在第三级别中已经解决,这里就无须再演示了吧!有需要的客官可以尝试尝试呦!
- 直接演示幻读现象:
我们先查看窗口b的数据:
文章图片
首先我们在窗口b中先输入修改数据语句,不按回车:
update account set balance = 400 where id > 3;
文章图片
然后对窗口a进行增加数据,不提交:
set autocommit = 0; START TRANSACTION; insert into account values(5, \'小哪\', 1000);
文章图片
此时在窗口b进行修改数据,按下回车:
文章图片
此时可以发现,无法进行修改,是因为窗口a还没提交数据,serializable相当于在别人修改数据的时候,给表上了锁,只能当前访问,其他访问不了,只能等到其他用户提交了才可以访问。
窗口a提交后,我们再试下窗口b修改数据:
文章图片
可以发现只能等到窗口a提交了才能继续访问,所以可以有效避免了幻读现象。
- 总结:
erializable可串行化:最高的隔离级别,可以有效的解决脏读、不可重复读、幻读现象。但是效率会比较低。
- read-uncommitted读取未提交:最低的隔离级别,读取尚未的提交的数据,也被为脏读,它可能会发生就是脏读现象和不可重复读和幻读现象。
- read-committed读已提交:可以读取并发事务中已经提交的数据,可以有效的阻止脏读,但是每次读取的值发生了改变,所以不可重复读和幻读仍有可能发生。
- repeatable-read可重复读:mysql的默认隔离级别,对同一字段的读取多次结果是一致的,可以阻止脏读和不可重复读,但是幻读仍会发生。那幻读就是本来我读取的只有一行的数据,此时再次读取可能多了一行,此时就是幻读了。
- serializable可串行化:最高的隔离级别,可以有效的解决脏读、不可重复读、幻读现象。但是效率会比较低。
那既然这样,不管信与不信,我们直接来验证下即可,免得说我骗你哈!
我们先看看起始的数据:
文章图片
delete:
-- 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 编写事务SQL语句
DELETE FROM account;
-- 回滚事务
ROLLBACK;
SELECT * FROM account;
文章图片
可以看到,我们数据删除后,回滚成功啦。接下来再看看truncate吧
truncate:
-- 开启事务
SET autocommit = 0;
START TRANSACTION;
-- 编写事务SQL语句
TRUNCATE account;
-- 回滚事务
ROLLBACK;
SELECT * FROM account;
文章图片
可以发现,回滚失败了。
总结:
TRUNCATE删除不能回滚,DELETE删除可以回滚。
2. 视图还记得之前面试的时候,那位面试大哥问了我一句,你了解视图吗,有用过吗?而我...
算了,过去都过去,最后我还是进入了这家公司,哈哈。这也印证,人的学习终究是有极限,人的认知终究还是得去拓展,如果一成不变,那将步步落后。
话不多说,让我们来看看视图是什么吧。
2.1 视图概念
MySQL从5.0.1版本开始提供视图功能。它其实就是一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。相当于保存的就是一条select查询语句哦。
那我们为什么要使用视图呢,那视图对于我们普通表有什么好处呢?
- 重用性:我们可以重复利用sql语句。
- 简洁性:使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件,简化了复杂的sql操作。
- 安全性:使用视图的用户只能访问他们被允许查询的结果集,保护了我们数据,提高了安全性。
2.2 创建并使用视图
创建语法:
CREATE VIEW 视图名称 AS 查询语句;
需求:我们查询下邮箱中包含a字符的员工名、部门名、和工种信息。
我们创建视图来做道题吧:
CREATE VIEW view_emp_dep_job
AS
SELECT last_name, department_name, job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id = e.job_id;
那我们创建出来的视图在哪里呢?
文章图片
那我们创建视图出来,如何去使用视图呢?
其实使用视图,就跟我们使用表查询一样的。
使用视图语法:
SELECT * FROM 视图名称;
那我们再接着刚才创建出来的视图去查看下包含a字符的。
SELECT * FROM view_emp_dep_job WHERE last_name LIKE \'%a%\';
文章图片
是不是也很easy呀!使用起来跟我们普通表没什么区别吧,但是我们简化了复杂的sql语句,并可以重用这个视图中的sql语句。
2.3 查看视图
从 MySQL 5.1 版本开始,使用 SHOW TABLES 命令的时候不仅显示表的名字,同时也会显示视图的名字,而不存在单独显示视图的 SHOW VIEWS 命令。
SHOW TABLES;
文章图片
如果你想看这个视图是如何定义出来的,当然也有命令可以帮助你!
-- 查看创建视图的sql语句
SHOW CREATE VIEW view_emp_dep_job;
文章图片
2.4 修改视图
在我们创建完视图后,那可不可以对这个视图进行修改呢?那肯定可以的啦!
语法1:
CREATE OR REPALCE VIEW 视图名称 AS 查询语句;
这条语句代表着如果存在这个视图就修改,如果没有就创建!
演示下:
CREATE OR REPLACE VIEW view_emp_dep_job
AS
SELECT last_name, department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_idSELECT * FROM view_emp_dep_job;
文章图片
可以看到,确实修改成功了!我们再看看第二个语法吧
语法2:
ALTER VIEW 视图名 AS 查询语句;
这条语句可以看出来就是修改视图啦!
演示下:
ALTER VIEW view_emp_dep_job
AS
SELECT * FROM employees;
SELECT * FROM view_emp_dep_job;
文章图片
可以看到该语句也可以修改成功!
总结:
- 语句1:
CREATE OR REPALCE VIEW 视图名称 AS 查询语句;
- 语句2:
ALTER VIEW 视图名 AS 查询语句;
语法:
DROP VIEW 视图名称;
演示下:
DROP VIEW view_emp_dep_job;
SHOW TABLES;
文章图片
可以看到,视图确实删除成功啦!
2.6 总结
视图与普通表的区别:
- 存储空间:
视图它保存的是sql逻辑,是一条查询语句。
表它保存了表结构和数据
- 应用场景:
如果复杂的sql语句需要重复利用,我们可以创建视图来重复利用,一般情况我们还是选择查询表来符号我们不断变更的需求。
文章图片
相信各位看官看到事务视图篇,是不是也学到了点东西呢。所以一起加油吧,不仅为了学习提升自我,也为了自己能面对MySQL面试而不慌张!并且面试中比较常问的面试题中都有MySQL的身影,所以MySQL的学习也是必不可少滴!
我们还是小小的总结下吧:
查看数据库的默认提交状态:SHOW VARIABLES LIKE \'autocommit\'关闭默认提交: SET autocommit = 0
开启事务: START TRANSACTION
提交事务: COMMIT
回滚事务: ROLLBACK设置回滚点:savepoint 回滚点名字
回到回滚点:rollback to 回滚点名字创建视图: CREATE VIEW 视图名称 AS 查询语句
使用视图: SELECT * FROM 视图名称
查看视图: SHOW TABLES
修改视图: ALTER VIEW 视图名 AS 查询语句
删除视图: DROP VIEW 视图名称;
当然啦,更多语句命令的一些细节,希望大家可以仔细学习!
注: 如果文章有任何错误和建议,请各位大佬尽情留言!如果这篇文章对你也有所帮助,希望可爱亲切的您给个三连关注下,非常感谢啦!
文章图片
推荐阅读
- 简单了解 MySQL 中相关的锁
- 前端 JavaScript 实现一个简易计算器
- 万字长文(SpringCloud gateway入门学习&实践)
- #导入MD文档图片#关于JS中的作用域中的沉思
- 我用 140 行代码,带你看一场流星雨?
- 优雅编程 | 7 个你应该掌握的 JavaScript 编码技巧
- 前端 · 面试 HTTP 总结—— HTTP 强缓存
- 架构师_01-架构师的道
- this的理解