“这是我参与8月更文挑战的第2天，活动详情查看：8月更文挑战”
点击查看上一篇 「新生代农民工」之前端工程师必会的数据库-MySQL(上)
第五章 MySQL中的视图
视图(view)是一种虚拟存在的表, 是一个逻辑表, 本身不包含数据.
他是作为一个select语句保存在字典中.
我们称用来创建视图的表叫做「 基表 」.
使用视图也有很多优点, 比如我们只关心过程, 而不用去关注表; 我们还可以通过视图对某行某列进行权限管理.
同时同我们修改原表的时候, 不会对视图产生影响.
为什么在实际web应用中我们很少用到view
我们在web 开发中, 一般都会去使用ORM, 这样其实我们就不用每个数据库都去写一个视图查询.
每当我们去创建视图的时候都会去执行查询「基表」的操作, 这样就会出现很多额外的开销. 所以可以尽量少使用.
以前有写过很复杂的存储过程, 结果被公司的DBA一顿diao.
但是不用不代表我们不用, 「有剑不用」和「没有剑」那是两码事.
一、创建视图
我们创建视图的语法和创建表的很像, 只是我们需要定义「基表」.
-- 基于students表创建视图
create view v_students (id, name, age, sex) as select id,name,age,sex from students with check option;
desc v_students;
-- 得到
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
-- 查询视图数据
select * from v_students;
-- 得到
+----+--------+------+------+
| id | name | age | sex |
+----+--------+------+------+
| 1 | 超哥 | 20 | 男 |
| 2 | 孟洋 | 10 | 男 |
| 3 | 梦佳 | 18 | 女 |
| 4 | 爱德 | 18 | 男 |
| 5 | 旭神 | 88 | 女 |
+----+--------+------+------+
我们可以看到, 其实视图和我们的普通表没有什么区别;
创建视图的时候, 数据类型也是根据「基表」来的.
使用show tables;语句, 可以看到v_students视图;
注意:
as 关键字不能省略.
with check option 表示视图在更新时要在其权限范围内. 一般都需要加上, 保证安全性.
视图不止基于单表创建, 还可以多表, 只要select语句返回记录即可以.
视图的列数量与select后的列数量保持一致. 视图创建完成之后, 我们可以把它当作一张普通表一样使用. 包括他的增删改查.
二、更新视图
虽然说很多语法和普通表一致, 不过也有不同的.
-- 创建或更新视图
create or replace view v_students(id, name, city) as select id, name, city from students with check option;
-- 得到
+----+--------+-----------+
| id | name | city |
+----+--------+-----------+
| 1 | 超哥 | 杭州 |
| 2 | 孟洋 | null |
| 3 | 梦佳 | 山沟沟 |
| 4 | 爱德 | 郑州 |
| 5 | 旭神 | 阿拉罕 |
+----+--------+-----------+
-- 同样的alter可以修改吗?
alter view v_students (name, age, city) as select name, age, city from students with check option;
-- 同样是可以的
+--------+------+-----------+
| name | age | city |
+--------+------+-----------+
| 超哥 | 20 | 杭州 |
| 孟洋 | 10 | null |
| 梦佳 | 18 | 山沟沟 |
| 爱德 | 18 | 郑州 |
| 旭神 | 88 | 阿拉罕 |
+--------+------+-----------+
当「基表」字段发生改变的时候, 我们就需要更新视图来保持数据一致.
所以开销大这也是情有可原.
可以通过replace或者alter跟新视图.
通过update更新数据
-- 更新梦佳的城市
update v_students set city="杭州" where name="梦佳";
--得到
+--------+------+-----------+
| name | age | city |
+--------+------+-----------+
| 超哥 | 20 | 杭州 |
| 孟洋 | 10 | null |
| 梦佳 | 18 | 杭州 |
| 爱德 | 18 | 郑州 |
| 旭神 | 88 | 阿拉罕 |
+--------+------+-----------+
这个时候我们再去看一下基表students;
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 20 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 杭州 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
明显的看到, 我们的students表也被更新了.
是不是很熟悉, 和我们的数组很像, 浅拷贝的时候也会改变到原数据.
注意:
视图本身是没有数据, 我们对其进行的DML操作都会更新到基表中.
有很多种情况我们的视图不能做DML操作. 包括select语句含有distinct、group by、union、分组函数等.
为什么有些视图不能做DML操作
可以明显的看到, 不能DML操作的select语句中已经不是单一的表了, 这个时候我们去更新, 数据库就不知道更新哪一个数据了.
-- 更新视图
create or replace view v_students as select name,(select grade from grades where id=1) as v_grade from students with check option;
-- 得到
+--------+-----------+
| name | v_grade |
+--------+-----------+
| 超哥 | 一年级 |
| 孟洋 | 一年级 |
| 梦佳 | 一年级 |
| 爱德 | 一年级 |
| 旭神 | 一年级 |
+--------+-----------+
-- 更新数据
update v_students set v_grade="二年级" where name="超哥";
-- 报错, 不能更新数据
ERROR 1348 (HY000): Column 'v_grade' is not updatable
其他的不能使用DML操作的可能情况这里就不列举了.
三、视图嵌套
那我们可以基于视图继续创建视图吗?
答案是可以的.
create view v_stu_2 as select name from v_students with check option;
-- 得到
+--------+
| name |
+--------+
| 超哥 |
| 孟洋 |
| 梦佳 |
| 爱德 |
| 旭神 |
+--------+
四、视图权限控制
我们在定义视图的时候, 可以定义视图的创建者, 以及对权限的控制.
➠ definer选项
指定视图的创建者, 没有则默认
create or replace definer="_xdq" view v_students(id, name, city) as select id, name, city from students with check option;
-- 查看视图信息
show create view v_students \G;
-- 得到
View: v_students
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`_xdq`@`%` SQL SECURITY DEFINER VIEW `v_students` (`id`,`name`,`city`) AS select `students`.`id` AS `id`,`students`.`name` AS `name`,`students`.`city` AS `city` from `students` WITH CASCADED CHECK OPTION
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set, 1 warning (0.00 sec)
可以看到我们的创建者_xdq
➠ 权限控制
-- 更新用户信息
update v_students set city="山沟沟" where name="梦佳";
-- 这样就报错了
ERROR 1449 (HY000): The user specified as a definer ('_xdq'@'%') does not exist
上面我们指定了视图的创建者之后, 我们的主机身份就没权限修改数据了.
这时候我们就用到另一个关键字了.
-- 使用sql security invoker加权限
create or replace definer="_xdq" sql security invoker view v_students(id, name, city) as select id, name, city from students with check option;
-- 这个时候我们再去修改数据
update v_students set city="山沟沟" where name="梦佳";
-- 得到
+----+--------+-----------+
| id | name | city |
+----+--------+-----------+
| 1 | 超哥 | 杭州 |
| 2 | 孟洋 | null |
| 3 | 梦佳 | 山沟沟 |
| 4 | 爱德 | 郑州 |
| 5 | 旭神 | 阿拉罕 |
+----+--------+-----------+
注意:
sql security invoker 表示操作该视图的用户需要对基表有操作权限.
sql security definer 表示创建该视图的用户需要对基表有操作权限.
到这里, 我们视图就基本讲完了, 还有很多都需要实际操作具体分析了.
第六章 存储过程
存储过程就是一段程序脚本, 使用 SQL 语言进行编程.
存储过程会在创建后会进行预编译, 然后存储在数据库中, 使用的时候可以指定调用某一个存储过程.
使用存储过程有什么好处呢?
可以将很多比较复杂的数据操作封装成一个脚本, 后端直接调用存储过程, 而不必再在代码中写繁杂的 SQL;
可以统一接口, 确保数据安全.
存储过程作为封装好的一段脚本, 具有很好的复用性.
存储过程可以指定权限操作.
存储过程使用有什么缺点呢?
首先不利于维护, 后端代码如果依托于存储过程, 就会很繁杂.
存储过程虽然是很短的脚本, 但是调试它也并非易事.
复杂的业务逻辑配合复杂的存储过程, 那下一个迭代你需求的人会变得十分困难.
一把鼻涕一把泪
以前公司使用.net 架构, 每一个项目都看起来十分臃肿, 调用存储过程更是多而复杂, 基本每个接口都有依托存储过程, 导致 sql server 数据库十分庞大, 对于当时我这个新人十分不友好, 一段时间过后, 我存储过程写的飞起, 基本能用的地方都用 存储过程去写, 写上瘾了. 毕竟在.net 中直接掉用就能拿到想要的数据.
不过我们在 MySQL 中还是少用为好.
不必给数据库太大的压力.
不管用还是不用, 我们还是需要学会的.
一、创建存储过程
我们先来看个例子, 封装一个简单的存储过程update_age, 实现对students表年龄的修改.
-- 创建存储过程
delimiter //
create procedure update_age(in p_age int, in p_name varchar(20))
begin
update students set age=p_age where name=p_name;
end //
注意:
因为我们一直在用命令行书写, 所以先使用 delimiter //将结束标志的分号改为//
这个时候我们看下创建好的存储过程:
-- 查看
show create procedure update_age//
-- 得到下面结果, 有很多信息我直接省略了.
...
`update_age`(in p_age int, in p_name varchar(20))
begin
update students set age=p_age where name=p_name;
end
...
注意:
即使存储过程没有参数, 也必须在后面加上小括号().
不要把参数名写的和列名一样, 不然只会被当作列名处理.
如何调用存储过程?
使用 call 关键字调用.
-- 将超哥的年龄设置为111
call update_age(111,"超哥")//
-- 查看数据表
select * from students;
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 111 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
我们可以看到超哥的 age 已经变成 111 了.
而我们创建的存储过程是可以多次使用的, 可以随便设置任何人的年龄.
而不用每次都去写update方法了.
在创建存储过程时需要注意什么?
使用create procedure关键字创建, 可以传入参数, 同样也可以具有输出.
存储过程以begin end开始结束, 可以嵌套, 相当于我们代码中的大括号{ }.
存储过程中可以使用很多的 sql 语法, 包括 DML、DDL、以及 if、case、while 等流程控制语句.
二、存储过程中的参数
存储过程支持参数, 我们可以把每一段存储过程看成是一个代码块.
输入参数
我们上面例子中的in就是入参.
这个参数是局部变量, 和其他语言中一致.
输出参数
使用out关键字定义输出参数, 输出参数也一样放在存储过程中的括号里.
-- 创建存储过程, 输出p2, p1的基础上+1
create procedure out_test (in p1 int, out p2 int)
begin
-- 先打印p2
select p2;
set p2 = p1 + 1;
select p2;
end //
定义好了存储过程, 我们下面来调用他, 先声明两个变量p_in和p_out作为入参和出参.
-- 声明入参p_in
set @p_in=2//
-- 声明出参p_out
set @p_out=1//
-- 调用存储过程
call out_test(@p_in,@p_out)//
-- 得到
+------+
| p2 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
+------+
| p2 |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
-- 打印p_out
select @p_out//
-- 得到
+--------+
| @p_out |
+--------+
| 3 |
+--------+
我们可以看到, 我们声明的变量p_out被改变了.
注意:
我们传入 out 参数的时候, 这个 out 参数是对调用者输出参数, 所以我们 select 打印的时候是 null.
输入输出参数
关键字inout, inout参数具备in和out的特性, 可以输入和输出. 会改变外界变量, 这里就不演示了.
三、使用 sql 编程
我们的存储过程就是一个代码块, 使用 sql 语言进行编程, 编程思维类其他语言.
定义一个变量
使用declare声明变量, 同时声明数据类型.
-- 声明变量
declare a int default 0;
给变量赋值
使用set关键字, 在上面的例子中我们就使用了.
条件语句
条件语句有if then else和case;
和我们其他语言中的if else和switch case类似.
-- if then else
declare a int default 0;
if a=0 then set a=2 end if;
if a=0 then set a=2 else set a=3 end if;
-- case
case a
when 0 then set a = 1
when 1 then set a = 2
when 2 then set a = 3
end case;
注意:
每条语句之后一定要使用对应的 end 语句结束.
循环语句
使用while do ... end while来进行语句循环.
使用repeat ... until end repeat来进行循环.
使用loop ... until end loop来进行循环.
-- 使用while do
while a = 0 do
set a = a + 1;
end while;
-- 使用repeat
repeat
set a = 1;
until a =0
end repeat;
-- 使用loop
loop_lable: loop
set a = a + 1;
if a > 2 then
leave loop_label
end loop;
注意:
在 sql 中没有 set a++ 这样的自增.
while 在语句执行前进行条件检查.
repeat 是在执行操作之后执行, 是不是很熟悉, 与我们的 while 和 do while 很相似.
loop 需要配合使用 leave 来离开循环, 它是没有循环条件的.
我们简单来使用一下上面的知识点
我们来做个简单的分页, 查询 students 表中年龄小于 80 的人并统计人数.
-- 创建存储过程
-- pageIndex 第几页
-- pageSize 每页几条
-- totalCount 输出 总条数
-- pageNum 总页数
create procedure sc_age(
in params varchar(100),
in _table text,
in _where varchar(100),
in _order varchar(100),
in pageIndex int,
in pageSize int,
out totalCount int,
out pageNum int
)
begin
-- 设置变量开始行
set @startRow = pageSize * (pageIndex - 1);
set @pageSize = pageSize;
set @rowIndex = 0;
-- concat是字符串链接函数
set @strSql = concat(
'select sql_calc_found_rows ',
params,
' from ',
_table,
concat(' where ', _where),
concat(' order by ', _order),
' limit ',
@startRow,
',',
@pageSize
);
-- 预处理
prepare strsql from @strSql;
-- 执行预处理语句
execute strsql;
-- 执行完记得删除
deallocate prepare strsql;
set totalCount = found_rows();
-- 判断总的页数
-- div 整除, 取整 \
-- % 取余
if (totalCount <= @pageSize) then
set pageNum = 1;
else if (totalCount % @pageSize > 0) then
set pageNum = totalCount div @pageSize + 1;
else
set pageNum = totalCount div @pageSize;
end if;
end if;
end//
-- 调用存储过程
set @totalCoun=0//
set @pageCount=0//
call sc_age('name, age, sex, city', 'students', 'age < 80','id asc', 1,2,@totalCount,@pageCount)//
-- 可以得到
+--------+------+------+-----------+
| name | age | sex | city |
+--------+------+------+-----------+
| 孟洋 | 10 | 男 | null |
| 梦佳 | 18 | 女 | 山沟沟 |
+--------+------+------+-----------+
-- 总的记录数
select @totalCount
-- 得到
+-------------+
| @totalCount |
+-------------+
| 3 |
+-------------+
-- 总的页数
select @pageCount//
-- 得到
+------------+
| @pageCount |
+------------+
| 2 |
+------------+
注意:
上面的例子中我们使用了预处理命令, 因为我们的存储过程封装中, select语句是没有写死的,这种情况下我们就需要使用预处理语句了.
预处理语句会将sql中的值用占位符代替, 可以理解为js中的模版字符串.
存储过程的编写中, 一定要注意语法, 不要遗漏.
存储过程在实际开发中越来越少用了, 基本都是在代码中处理, 为了减轻数据库压力, 所以我们可以学, 但是尽量少用.
第七章 MySql中的事务
「事务」是数据库中最基本的工作单元, 保证了一个业务的完整性, 它是一个操作序列, 这些操作要么执行, 要么不执行.
一、如何控制事务
我们可以设置是否自动提交来控制事务.
没有提交的事务具有「回滚」的功能.
我们先来查看一下MySQL中的自动提交状态.
select @@autocommit;
-- 得到
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
显而易见, 自动提交功能默认开启.
当我们去执行一个sql语句的时候, 效果会被立即执行, 且不能回滚.
继续我们上面的例子, 使用students表.
select * from students;
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 111 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
-- 接着我们修改一条数据
update students set age=100 where name="超哥";
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 100 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
-- 接着我们来回滚一下
rollback;
-- 结果没有改变
使用rollback来回滚更新语句, 可以看到, 并没有回滚成功.
接下来, 我们来将autocommit的状态设置为0;
set autocommit=0;
-- 得到
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
然后我们接着来执行上面的操作:
update students set age=200 where name="超哥";
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 200 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
-- 接着回滚
rollback;
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 100 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
很显然, 我们的数据表记录回到了更新语句之前.
那我们可以手动提交事务吗?
答案是肯定的!
可以使用commit语句来手动提交.
update students set age=100 where name="超哥";
commit;
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 100 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
-- 回滚
rollback;
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 100 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
数据记录并没有回滚到我们之前的状态.
除了设置autocommit=0的方法, 还有其他方法可以手动开启事务:
- begin;
- start transaction;
先把之前设置的autocommit改为1.
begin;
update students set age=200 where name="超哥";
rollback;
-- 得到
+----+--------+------+-----------+------+----------+
| id | name | sex | city | age | grade_id |
+----+--------+------+-----------+------+----------+
| 1 | 超哥 | 男 | 杭州 | 100 | 2 |
| 2 | 孟洋 | 男 | null | 10 | 1 |
| 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 |
| 4 | 爱德 | 男 | 郑州 | 18 | 2 |
| 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 |
+----+--------+------+-----------+------+----------+
同样的, start transaction也是一样的使用方法.
注意:
事务给我们提供一个回滚的方法rollback;
可以使用autocommit=0 、begin或者start transaction来手动开启事务;
事务开启之后, 一旦commit了, 就不可以回滚了.
二、事务的四大特征(ACID)
事务具有四大特性:
- 原子性 A
- 一致性 C
- 隔离性 I
- 持久性 D
原子性
事务是最小的单元, 不可以再分割. 如果一个操作失败了,需要将这一个事务中的所有操作恢复到事务执行之前.
一致性
同一事务中sql语句必须保证同时成功或者同时失败.
隔离性
事务具有几个隔离级别:
- 1、read uncommitted; 读未提交
- 2、read committed; 读已提交
- 3、repeatable read; 可重复读 (MySQL默认隔离级别)
- 4、serializable; 串行化
1、read uncommitted✅
如果有事务a和事务b, a事务对数据进行操作, 事务a没有被提交, 但是b可以看见a操作的结果.
举个例子🌰:
首先创建一张表user.
create table if not exists user(
id int,
name varchar(20),
money int(200)
);
-- 插入一条数据
insert into user values(1,"超哥",100);
insert into user values(2,"梦佳",2000);
-- 得到
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
+------+--------+-------+
如何查看数据库的隔离级别?
使用select查看@@global.transaction_isolation.
select @@global.transaction_isolation;
-- 得到
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
接着我们来修改隔离级别为read uncommitted;
set global transaction isolation level read uncommitted;
-- select @@global.transaction_isolation得到
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED |
+--------------------------------+
很显然, 隔离级别被修改成READ-UNCOMMITTED了.
在read uncommitted隔离级别下会出现什么问题呢?
接着继续我们的例子:
梦佳钱💰比较多, 就想给超哥转200块钱.
begin;
update user set money=money-200 where name="梦佳";
update user set money=money+200 where name="超哥";
-- select
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 300 |
| 2 | 梦佳 | 1800 |
+------+--------+-------+
-- 可以看到转账成功
我们打开两个终端, 表示「超哥」和「梦佳」两个用户.
梦佳给超哥转了200快, 梦佳查看转账是否成功:
select * from user;
-- 得到
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 300 |
| 2 | 梦佳 | 1800 |
+------+--------+-------+
梦佳看到转账成功了. 然后超哥也要查看转账是否成功了.
select * from user;
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 300 |
| 2 | 梦佳 | 1800 |
+------+--------+-------+
超哥看到钱也到账了. 这个时候, 梦佳家比较坏了, 她回滚了一下;
rollback;
然后超哥去消费的时候, 就会发现钱不够了.
-- 没有转账成功
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
+------+--------+-------+
如果两个用户都在进行数据操作, 如果事务a开启之后, 它的数据可以被其他数据读取到.但是没有提交, 可以被回滚.回滚之后的数据也会被读到.
这种情况就叫做「脏读」.
「脏读」就是一个事务读到了另外一个事务没有提交的事务.
注意
实际操作中不允许脏读出现, 记得commit.
2、read committed✅
首先, 修改隔离级别为read committed.
set global transaction isolation level read committed;
-- 得到
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED |
+--------------------------------+
在read committed隔离级别下会出现什么问题呢?
接着我们上面的例子.
用户a计算user表中的平均值.
select avg(money) from user;
-- 得到
+------------+
| avg(money) |
+------------+
| 1050.0000 |
+------------+
然后, 用户b往user表中插入一条数据.
insert into user values(1,"孟洋",1000);
-- 得到
+------+--------+-------+
| id | name | money |
+------+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
| 1 | 孟洋 | 1000 |
+------+--------+-------+
这个时候, 用户a重新计算了下平均值.
select avg(money) from user;
-- 得到
+------------+
| avg(money) |
+------------+
| 1033.3333 |
+------------+
显而易见, user表中的平均值被改变了.
读取同一个表, 发现前后数据不一致.
这种情况就叫做「不可重复读」.
3、repeatable read✅
首先修改隔离级别repeatable read.
set global transaction isolation level repeatable read;
-- 得到
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
在repeatable read隔离级别下会出现什么问题呢?
接着上面的例子, 我们来看下会出现什么情况.
首先, 用户a往user表中插入一条数据.
begin;
insert into user values(4,"爱德",10);
-- 得到
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
| 3 | 孟洋 | 1000 |
| 4 | 爱德 | 10 |
+----+--------+-------+
接着用户b查询一下user表:
-- 查询user
select * from user;
-- 得到
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
| 3 | 孟洋 | 1000 |
+----+--------+-------+
可以看到, 并没有查询到准确的数据.
那我们用户a去进行事务提交:
-- 提交
commit;
-- 用户a查询得到
select * from user;
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
| 3 | 孟洋 | 1000 |
| 4 | 爱德 | 10 |
这时候用户b接着去查询:
-- 查询user
select * from user;
-- 得到
+----+--------+-------+
| id | name | money |
+----+--------+-------+
| 1 | 超哥 | 100 |
| 2 | 梦佳 | 2000 |
| 3 | 孟洋 | 1000 |
+----+--------+-------+
还是没有查询到id=4的数据;
但是我们这时候用户b往user表中插入数据:
-- 插入一样的数据
insert into user values(4,"爱德",10);
-- 得到
ERROR 1062 (23000): Duplicate entry '4' for key 'user.PRIMARY'
很显然, 数据插入失败.
两个事务同时操作一张表, 事务a提交的数据, 也不能被事务b读到.
这种现象就叫做「幻读」.
注意:
关闭终端(用户b), 重新启动查询则幻读现象消失.
4、serializable✅
设置隔离级别为串行化.
set global transaction isolation level serializable;
同样的, 我们打开两个终端, 代表用户a和用户b; 接着两个用户同时开启一个事务;
-- 用户a
begin;
insert into user values(5,"狗蛋",100);
--用户b
begin
insert into user values(6,"艾斯",1800);
可以看到先插入的用户a成功了, 但是用户b一直卡住了.
过一段时间之后, 会报一个错误, 操作超时了.
-- 操作超时
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
接下来, 用户a提交事务, 用户b再去操作插入操作, 可以操作成功了.
当一个表被一个事务操作的时候, 其他的事务里面的写操作是不能被执行的.
这种现象就是「串行化」.
注意:
如果另一个事务b还没有超时, 这时候第一个事务a被提交了, 这个事务b会被执行.
隔离级别越高, 性能越差.
read uncommitted > read committed > repeatable read > serializable
mySql默认隔离级别是repeatable read.
MySQl的事务基本就讲完了, 还有更多的用到再查了.
终章
MySQL数据库就先讲到这里了. 文章讲到了很多内容, 包括:
- MySQL的数据类型
- SQL编程
- 约束
- 视图
- 存储过程
- 事务
- ...
同时为了更好的写文章, 我也列举了很多的例子.
都是一些比较不错和常用的例子, 大佬可略过.
不会数据库或者曾经大学学过忘掉的同学都可以学习, 简单易上手. 跟着敲一遍, 基本都可以学会了.
MySQL的知识还有很多, 这里就不再继续了, 有兴趣的同学可以下去自行学习.
文章体量过大, 难免有不当之处, 敬请指出, 万分感谢!
文章太长, 我们下一篇见.
文章粗浅, 望诸位不吝您的评论和点赞~
注: 本文系作者呕心沥血之作, 转载须声明
