随笔 - 65  文章 - 0 评论 - 0 阅读 - 28752
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

数据库4-函数，存储过程，触发器，视图，子查询和自关联查询联合查询，事务的基本使用和四大特性ACID

一 函数

MySQL常用函数非常多， 常用的函数分类则包括数值型、字符串型、日期时间、聚合、加密、控制流程等函数。当然， MySQL中也支持自定义函数。

最后要注意：要少用或不用函数，虽然mysql提供了非常多函数，但是使用了函数以后，肯定损耗性能的，MySQL提供给我们的使用的核心作用并非替代编程语言，而是存储数据。

一 数值型函数

函数描述
ABS 求绝对值
SQRT 求二次方根
MOD 求余数
CEIL 返回不小于参数的最小整数，即向上取整
FLOOR 向下取整，返回值转化为一个BIGINT
RAND 生成一个0~1之间的随机数，传入整数参数时，用来产生可重复使用的随机数
ROUND 对所传参数进行四舍五入
FORMAT(value,n)

返回对数字value进行格式化后的结果数据。n表示四舍五入后保留到小数点后n位。

例：

select format(RAND()*100,0);

二 字符串型函数

函数描述示例
CHAR_LENGTH 返回字符串的字符长度

select name, CHAR_LENGTH(description) from student;

CONCAT(s1,s2,…)

 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个

查询结果格式定义

1 普通字段合并格式定义

select name, concat(province, city, area, address) as addr from address;

select name, CONCAT_WS('-',province, city, area, address) as addr from address;

2 分组字段合并格式定义

select class, group_concat(name) from student group by class;

/* 302  王天龙,王天歌,祝华生 xxx xxx,xxx,xxx */

-- concat_ws与group_concat组合使用

select any_value(name), group_concat(concat_ws('-',id,name) order by id) from student group by class;

# 报错字段未分组可使用any_value

CONCAT_WS(x, s1,s2,......,sn)

 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

 能将相同的行组合起来 

LOWER

 将字符串中的字母转换为小写

select  lower('HEELO'), upper('hello');

UPPER

 将字符串中的字母转换为大写

TRIM

 删除字符串左右两侧的空格

 

select TRIM(' 12345 '), length(TRIM(' 12345 '));

REVERSE(s)

 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

 select REVERSE('hello');

三 日期时间函数

函数描述格式示例
CURRENT_DATE 返回当前系统的日期值 2021-02-26 select CURRENT_DATE();
CURRENT_TIME 返回当前系统的时间值 15:00:42 select CURRENT_TIME();
NOW 返回当前系统的日期和时间值 2021-02-26 15:00:42 select now();
UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 1614322842 select UNIX_TIMESTAMP();
FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 2021-02-26 15:00:42

select from_unixtime(1654693868);
MONTH 获取指定日期中的月份 2 select MONTH(now());
MONTHNAME 获取指定日期中的月份英文名称 February select MONTHNAME(now());
DAYNAME 获取指定曰期对应的星期几的英文名称 Friday select DAYNAME(now());
DATE_ADD 向日期添加指定的时间间隔 DATE_ADD('2021-02-26', interval 1 day); select DATE_ADD('2022-06-08', interval 1 day); # 2022-06-09
select DATE_SUB('2022-06-08', interval 1 day); # 2022-06-07
DATE_SUB 向日期减去指定的时间间隔 DATE_SUB('2021-02-26', interval 1 week );
DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 DATEDIFF('2021-02-26', '2022-01-26');

select DATEDIFF('2022-06-08', '2022-01-08'); # 151
TIMEFDIFF 获取两个时间之间间隔，返回参数 1 减去参数 2 的值 select timediff(current_time(), '06:30:00');

select timediff(current_time(), '06:30:00'); # 14:49:48
 

四 聚合函数


函数		描述
MAX 查询指定列的最大值
MIN 查询指定列的最小值
COUNT 统计查询结果的行数
SUM 求和，返回指定列的总和
AVG 求平均值，返回指定列数据的平均值

五 加密函数

针对项目中的用户密码往往我们都是直接在编程语言中进行加密处理了，不会使用mysql的加密函数，但是针对mysql系统内部实际上也要对数据库管理员进行密码加密，此时我们就会使用加密函数。

函数描述示例
AES_ENCRYPT(str,key) 用密钥key对字符串str利用AES加密算法加密后的结果， 是一个二进制字符串，以BLOB类型存储

-- 使用AES高级加密算法基于参数2秘钥对参数1数据进行加密，得到二进制数据（BOLB）
select AES_ENCRYPT('123456', 'hwkwkwk2');
AES_DECRYPT(str,key) 用密钥key对字符串str利用AES加密算法解密后的结果

-- 使用AES高级加密基于参数2秘钥对参数1进行解密，得到原数据
select AES_DECRYPT(AES_ENCRYPT('123456', 'hwkwkwk2'), 'hwkwkwk2');
DECODE(str,key) 使用key作为密钥解密加密字符串str  
ENCODE(str,key) 使用key作为密钥加密字符串str  
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt加密字符串str  
MD5() 计算字符串str的MD5校验和 select md5('hello'), sha1('hello'), sha('hello');   
SHA1() 计算字符串str的安全散列算法(SHA)校验和
PASSWORD(str) 对字符串str进行加密，低版本MySQL默认使用password对数据库系统账号进行密码加密

六 系统信息函数

 

函数描述
VERSION() 返回当前MySQL的版本号
CONNECTION_ID() 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA() 返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”

七 流程控制函数

 

 

函数描述示例
if(expr,v1,v2) 判断，流程控制
复制代码
-- 示例1
SELECT
CASE WEEKDAY(NOW())
   WHEN 0 THEN '星期一'
   WHEN 1 THEN '星期二'
   WHEN 2 THEN '星期三'
   WHEN 3 THEN '星期四'
   WHEN 4 THEN '星期五'
   WHEN 5 THEN '星期六'
   ELSE '星期天'
END as text;

-- 示例2
SELECT
CASE
   WHEN WEEKDAY(NOW())=0 THEN '星期一'
   WHEN WEEKDAY(NOW())=1 THEN '星期二'
   WHEN WEEKDAY(NOW())=2 THEN '星期三'
   WHEN WEEKDAY(NOW())=3 THEN '星期四'
   WHEN WEEKDAY(NOW())=4 THEN '星期五'
   WHEN WEEKDAY(NOW())=5 THEN '星期六'
   ELSE '星期天'
END as text;
复制代码
ifnull(v1,v2) 判断是否为空
case搜索语句

-- 用法1
CASE <表达式>
WHEN <值1> THEN <操作1>
WHEN <值2> THEN <操作2>
...
ELSE <操作>
END CASE;

-- 用法2：
CASE
WHEN <条件1> THEN <命令1>
WHEN <条件2> THEN <命令2>
...
ELSE <其他命令>
END CASE;

八 自定义函数

MySQL中支持自定义函数，但是这个功能默认是关闭的。

复制代码
-- 查看是否开启自定义函数功能
show variables like '%func%';

-- 临时开启自定义函数功能
SET GLOBAL log_bin_trust_function_creators = 1;

-- 创建函数
DELIMITER $$  -- DELIMITER $$  定义语句结束符。MySQL默认的结束符是分号，但是函数体中可能用到分号。为了避免冲突，需要另外定义结束符。
set global log_bin_trust_function_creators=1$$  -- 开启自定义函数的权限
DROP FUNCTION IF EXISTS 自定义函数名$$  -- 如果自定义函数名已经存在了，就删除掉。
CREATE FUNCTION 自定义函数名([参数列表]) RETURNS 返回结果的数据类型
BEGIN -- 函数体放在BEGIN 与 END之间
    SQL语句;
    RETURN 返回结果的值;
END $$  -- 函数结束
set global log_bin_trust_function_creators=0$$  -- 关闭自定义函数的权限
DELIMITER ; -- 自定义函数结束以后，恢复原来的语句结束符

-- 删除函数
DROP FUNCTION [ IF EXISTS ] 自定义函数名;
复制代码

示例

复制代码
DELIMITER $$
set global log_bin_trust_function_creators=1$$

DROP FUNCTION IF EXISTS get_student_total_by_class$$

create function get_student_total_by_class(class_num int) returns int
BEGIN
    declare total int default 0; -- 声明一个变量total，类型为int，默认值为0
    select count(id) from student where class=class_num into total;  -- 把查询结果赋值(into)给total变量
    return total;  -- 返回total作为函数结果
END $$


set global log_bin_trust_function_creators=0$$
DELIMITER ;

select get_student_total_by_class(305);
select get_student_total_by_class(301);
示例
复制代码

二 存储过程

存储过程中往往会与函数类似，一般处理复杂的业务功能，而且往往内部都会采用事务批量执行SQL语句

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程可以没有。

创建存储过程 
CREATE PROCEDURE 存储过程名称 (in/out/inout [proc_parameter[,...]])
begin
    -- 存储过程的函数体->SQL语句
end ;
-- in 表示外界传递进来的参数，叫入参
-- out 表示由内部处理后返回给外界的数据，叫出参[相当于返回值]
-- inout 表示外界传递进来的参数，并经过处理后可以在外界调用结果的进出参
调用存储过程 
call 存储过程名称();
查看存储过程

因为存储过程是基于数据库保存的，所以查询存储过程必须指定数据库

-- 查询指定数据库中的所有的存储过程或函数
select routine_type, routine_name  from information_schema.routines where routine_schema='students';

-- 查询存储过程的状态信息和创建信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure  procedure_name;
 删除存储过程 
DROP PROCEDURE  [IF EXISTS] procedure_name;
 

 

示例：

复制代码
delimiter $$
drop procedure if exists procedure_name$$
create procedure procedure_name(in n int)
begin
    declare total int default 0;
    declare num int default 0;
    SET num = 1; -- 赋值语句，如果值的结果是一个SQL语句的结果，则需要使用into
    SELECT num;  -- 打印，因为MySQL中没有提供print函数，所以我们调试就需要使用select
    while num<=n do
        set total = total + num;
        set num = num + 1;
    end while;
    select total;
end $$

delimiter ;

call procedure_name(10);
示例1
复制代码

 

复制代码
delimiter $$
drop procedure if exists procedure_name$$
create procedure procedure_name(in age int , inout content varchar(100))
-- in 表示外界传递进来的参数，叫入参
-- out 表示由内部处理后返回给外界的数据，叫出参[相当于返回值]
-- inout 表示外界传递进来的参数，并经过处理后可以在外界调用结果的进出参
begin
  if age < 18 then
    set content='未成年人';
  elseif age < 40 then
    set content ='青年';
  else
    set content ='中老年';
  end if;

end$$

delimiter ;

call procedure_name(50, @content); -- 出参必须使用@开头声明变量
select @content;
示例2
复制代码

三 触发器

触发器（trigger）就是在预设条件满足以后，自动执行的SQL语句的数据库特性，一般在编程开发中，也可以称之为钩子（Hook），中间件（Middleware）。

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中预定义的SQL语句集合。触发器的这种特性可以协助应用程序在数据库端确保数据的完整性 ，常用于日志记录， 数据校验等操作。

创建触发器

复制代码
create trigger 触发器名称

before/after insert/update/delete

on tbl_name

[ for each row ] -- 行级触发器，注意：mysql中只支持行级触发器，不支持语句触发器。
begin
-- 一条或SQL语句;
end;
复制代码

触发器类型


触发器类型		触发器中new 和 old的使用
INSERT 类型触发器 new 对象表示将要或者已经新增的数据
UPDATE 类型触发器 old 对象表示修改之前的数据 , new 对象表示将要或已经修改后的数据
DELETE 类型触发器 old 对象表示将要或者已经删除的数据

触发器操作

以一张职员表与日志表为例，通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除

准备数据

创建一张职员表与日志表：

复制代码
create table emp(
    id int auto_increment primary key,
    name varchar(50),
    age tinyint,
    salary decimal(8,2)
);

create table logs(
  id int(11) not null auto_increment primary key,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
);
复制代码
创建 insert 类型触发器

完成插入数据时的日志记录

复制代码
-- 创建添加数据的触发器
DELIMITER $$
create trigger logs_insert_trigger
after insert
on emp
for each row
begin
    insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id, concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end $$

DELIMITER ;

-- 测试
insert into emp values (null, '小明', 18, 20000);
复制代码
创建 update 类型触发器

完成更新数据时的日志记录

复制代码
-- 创建更新类型的触发器
DELIMITER $$
create trigger logs_update_trigger
after update
on emp
for each row
begin
  insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end $$

DELIMITER ;

-- 测试
update emp set salary=salary+500 where id=1;
复制代码
创建delete 类型的触发器

完成删除数据时的日志记录

复制代码
DELIMITER $$
create trigger logs_delete_trigger
after delete
on emp
for each row
begin
  insert into logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end $$

DELIMITER ;

-- 测试
delete from emp where id = 1;
复制代码
删除触发器 
drop trigger [schema_name.]trigger_name;  -- 如果没有指定 schema_name，默认为当前数据库 。
查看触发器 
show triggers;

四 视图

视图（View）就是从一个或多个表中导出来的表，是一种只保存SQL语句的虚拟存在的表。

视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。 视图中的数据是依赖于真实表中的数据。一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变。视图常用于一些权限要求比较多的系统项目中。

视图的优点：

  1. 定制用户数据，聚焦特定的数据

  2. 简化数据操作

  3. 提高数据的安全性

  4. 共享所需数据

基本操作格式

复制代码
-- 创建视图
create view 视图表名  as SQL语句;

-- 查看视图
desc 视图表名
show tables
show create table/view 视图表名

-- 视图的数据的读写操作与普通表一致。
select * from 视图表名

update 视图表名 set 字段1=字段1的值, 字段2=字段2的值... where 条件表达式;
-- 修改视图的数据，建议直接改视图对应的那个数据表中的数据，而不是直接改视图，因为视图中如果包含以下4种情况，是不能修改视图中的数据
-- 1. 视图中包含SUM()、COUNT()、MAX()和MIN()等函数；
-- 2. 视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVING等关键字；
-- 3. 视图对应的表存在没有默认值的列，而且该列没有包含在视图里；
-- 4. 包含子查询的视图;

-- 修改视图中代表的SQL语句
ALTER VIEW 视图表名  AS SQL语句;

-- 删除视图
DROP VIEW 视图表名 [,视图表名]；
复制代码

示例

复制代码
drop table employee;
create table employee (
    id int auto_increment primary key,
    name char(20),
    department varchar(50),
    money decimal(8,2)
);


-- 提供给财务部门的人
select * from employee;

-- 创建1个视图
create view staff as select id,name,department from employee; # 确保员工看不到工资

-- 提供给其他部门的人
select * from staff;
示例
复制代码

五 子查询

子查询是将一个查询语句嵌套在另一个查询语句中，那么被嵌入到内部的语句就是子语句，而外部包含的就是主语句。

内层的子查询语句的查询结果，可以为外层查询语句提供查询条件或数据源。

常见格式

主语句 from (子语句) as 表别名;
主语句 where (子语句);

示例

复制代码
-- 查询大于平均年龄的学生信息
select name,age from student where age > (select avg(age) from student);

-- 查询指定学生（吴杰）在班的所有同学信息
select * from student where class = (select class from student where name = '吴杰') and name != '吴杰';

-- 子查询作为数据源
select id,name from (select * from student order by id desc limit 5) as t;
示例
复制代码

六 自关联查询

自关联也叫自连接，是单表同时存在主键与外键情况下的单表自连接查询操作。

例如：员工与领导，省份与城市与地区，一级菜单与二级菜单与三级菜单，权限，推荐人/分销，等数据，都是常见的需要运用自关联操作的场景。

idnameparent_id
1 河南省  
2 广东省  
3 郑州市 1
4 开封市 1
5 广州市 2
6 新郑市 3
7 龙亭区 4
8 鼓楼区 4
9 通许县 4

处理这种自关联设计的表结构，可以在自己的脑海中对当前表进行拆分。如

province省份表

idname
1 河南省
2 广东省

city 城市表

idnameparent_id
3 郑州市 1
4 开封市 1
5 广州市 2

area 地区表

province省份表

idname
1 河南省
2 广东省

city 城市表

idnameparent_id
3 郑州市 1
4 开封市 1
5 广州市 2
6 深圳市 2

area 地区表

idnameparent_id
8 新郑市 3
10 龙亭区 4
11 鼓楼区 4
12 通许县 4

示例

复制代码
-- 河南省的城市
select id from area where parent_id = (select id from area where name = '河南省');

-- 河南省的所有城市的地区
select * from area where parent_id in (select id from area where parent_id = (select id from area where name = '河南省'));

-- 查询龙亭区属于哪一个省份
select name from area where id = (select parent_id from area where id = (select parent_id from area where name = '龙亭区'));
示例
复制代码

七 联合查询

联合查询（union query），也叫合并查询，主要用于把多个查询结果合并成一个结果集返回，支持单表或多表操作，有两个关键字：

  • union，合并查询结果，并对重复结果进行去重。

  • union all，合并查询结果，但不会对重复结果进行去重。

示例

复制代码
-- union的基本使用
-- 查询男生中平均成绩最好的3个学生 与 女生中成绩最好的5个学生。
(select any_value(name), avg(achievement) as score from student s
left join achievement a on s.id = a.sid
where s.sex = 1
group by a.sid
order by score desc limit 3)  --  因为limit在SQL语句中属于语句的结束关键字，所以在联合查询中需要加上（）
union
(select any_value(name), avg(achievement) as score from student s
left join achievement a on s.id = a.sid
where s.sex = 2
group by a.sid
order by score desc limit 5);

-- union all的基本使用
-- 查询男生数量最少与女生数量最少的班级5个班级
(select class, count(id) as c from student where sex = 1 group by class order by c limit 5)
union all
(select class, count(id) as c from student where sex = 2 group by class order by c limit 5);
示例
复制代码

八 事务（针对DML，查询有隔离性）

事务，是以功能或业务作为逻辑单位，把一条或多条SQL语句组成一个不可分割的操作序列来执行的数据库机制。事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等要求数据一致性或完整性比较高的场景。

MySQL 中只有使用了 Innodb 存储引擎才支持事务操作。它可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行成功，要么全部执行失败，当事务中的某条SQL语句执行失败或产生错误，整个事务内部的所有SQL语句都将会回滚，所有受到影响的数据将返回到事务开始前的状态。

一 基本使用

准备数据

drop table if exists users;
create table users(
id int auto_increment primary key,
name varchar(50),
money decimal(8,2)
);

insert into users values(1, '小明',1000);
insert into users values(2,'小红',1000);
insert into users values(3,'小白',1000);
事务提交

begin; -- 使用begin关键语句表示手动开启事务，以下所有的SQL语句，就会被MySQL视作一个不可分割的整体
update users set money=money-200 where name = '小明';
update users set money=money+200 where name = '小红';
commit; -- 使用commit关键语句表示手动提交事务
事务回滚

begin; -- 使用begin关键语句表示手动开启事务，以下所有的SQL语句，就会被MySQL视作一个不可分割的整体
update users set money=money-200 where name = '小明';
update users set money=money+200 where name = '小红';
rollback; -- 使用rollback关键语句表示手动回滚事务，事务中所执行的所有的SQL语句都相当于没有执行一样。
-- 当然，什么时候选择提交事务，什么时候选择回滚事务呢？这个就需要结合我们将来所编写的python功能代码进行判断了。
多点回滚

begin;
update users set money=money-200 where name='小明';
select * from users;
SAVEPOINT s1; -- POINT就是事务的节点，savepoint可以给当前位置打个标记，将来如果不希望完全回滚事务，则可以选择回滚到某一个事务节点。p1是自定义的事务节点名称
update users set money=money+100 where name='小红';
select * from users;
SAVEPOINT p2;
update users set money=money+100 where name='小白';
select * from users;
SAVEPOINT p3;


ROLLBACK TO s1;
select * from users; -- 注意，这里仅仅是事务内部的回滚，并没有退出事务操作的，此时还在事务内部。
rollback; -- 或执行commit; 此时才是真正的退出事务。

二 事务控制

上面对于事务的处理都是手动操作的，MySQL中还支持使用AUTOCOMMIT来控制是否自动提交事务。mysql中默认是开启开启自动提交事务的(AUTOCOMMIT=1)，也就是在打开客户端连接时，会默认把每一条SQL语句当成一个独立的事务进行处理。

-- 查看MySQL中的AUTOCOMMIT值，MySQL中默认是开启自动提交事务的。这种情况下，MySQL会把每个sql语句当成一个事务，然后自动的commit提交事务。
SHOW VARIABLES LIKE '%AUTOCOMMIT%';

-- 关闭自动提交事务，MySQL会把当前会话连接的所有DML操作当成一个会话级别的事务进行管理，直到输入rollback或commit，当前事务才算结束。
-- 而结束该会话事务前，新的MySQL连接中是无法读取到任何该会话的操作结果的。
SET AUTOCOMMIT=0;
-- 开启自动提交事务
SET AUTOCOMMIT=1;

三 四大特性ACID

原子性（Atomicity）

事务内的所有DML语句，作为一个不可分割的整体，要么都成功，要么都失败，由redo log（重做日志）来实现.

原子性在数据库中的体现就是事务回滚，回滚能够撤销所有已经执行的sql语句，InnoDB实现回滚靠的是回滚日志undo log。在mysql中，每次执行DML语句都会先往 undo log 写入一条反向的SQL并且持久化，当系统崩溃时，扫描没有 commit 的事务对应的 undo log，按照undo log中不同类型SQL语句执行回滚操作。

  • insert 类型：undo log 记录了 id ，根据 id 写入反向的delete语句

  • delete 类型：undo log 记录了id对应的删除的数据，写入反向的insert语句

  • update 类型：undo log 记录了修改前的数据，写入反向的update语句
一致性（Consistency）

事务执行前后的数据完整性是一致的，由undo log（回滚日志）来实现。

一致性是指事务在从一个一致性状态（事务开始前）切换到另一个一致性状态（事务结束后），不管事务是提交还是回滚，数据具有完整性约束的。例如上面的转账，在事务开始前，小红与小明两人的总金额是2000，在事务结束后不管事务提交或回滚，小明与小红的总金额还是2000的。

InnoDB中事务的一致性是由undo log（回滚日志）来实现。
隔离性（Isolation）

事务执行过程中对其他事务可以设置不同的隔离级别，隔离性由锁来实现不同的隔离级别

隔离性是指并发过程中不同的客户端事务是隔离的，并发执行的各个事务之间不能互相干扰，在没有隔离性约束下，并发事务就可能出现脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）的问题，为了解决这些问题，就有了“隔离级别”的概念。 

innoDB存储引擎 遵循了数据库事务的四种隔离级别：

1 读未提交（RAED UNCOMMITED，RU）：基于行级锁（但使用查询语句不会加锁），允许事务中的查询SQL语句读取其他事务中没提交的数据，这会导致出现脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）等问题的出现。这种最低的隔离级别，我们不会使用到它。

2 读已提交（RAED COMMITED，RC）：基于行级锁，只允许事务中的查询SQL语句读取其他事务中已经提交的数据，可以避免脏读，但是不可重复读、幻读等问题还是会出现。db2或oracle等数据库的默认隔离级别，不可重复读、幻读可以通过代码程序来判断避免的。

3 可重复读（REPEATABLE READ，RR）：基于行级锁，确保事务中多次读取同一范围的数据会返回第一次查询结果的快照，不会返回不同的数据行，但是可能出现幻读。MySQL的默认数据隔离级别就是RR级别，开发中经常把MySQL的隔离级别降低为RC级别

4 串行化（SERIALIZABLE）：基于表级锁，将全部的查询语句加上共享锁，让事务基于串行化的方式一个个执行，解决了幻读的问题，但是影响了并发性。分布式事务（XA）中默认就是串行化

 

查询数据库的事务隔离级别有两种，全局事务（global ）与会话事务（session）。

全局事务级别是针对整个数据库中所有的客户端连接。

会话事务级别是针对当前客户端连接，会话事务隔离级别设置比全局事务隔离级别权重大。

复制代码
-- 查询全局事务隔离级别
show global variables like '%transaction_isolation%';   -- mysql8.0版本之前，配置项名为：tx_isolation
SELECT @@global.transaction_isolation;  -- 等价于上一句

-- 查询会话事务隔离级别
show session variables like '%transaction_isolation%';
SELECT @@session.transaction_isolation;  -- 等价于上一句
SELECT @@transaction_isolation;  -- 等价于上一句

-- 临时设置数据库的隔离级别
-- 设置全局事务隔离级别【注意，修改了事务隔离级别，并不会影响到当前已打开的客户端连接会话的隔离级别】
SET GLOBAL TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
-- 设置会话事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

-- 永久设置数据库的隔离级别
# 打开配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = READ-COMMITTED

# 保存配置文件，并重启MySQL，最好pycharm也重启下。
service mysql restart
全局事务（global ）与会话事务（session）设置
复制代码

 
持久性（Durability）

事务一旦提交，其结果就是永久性更改的，由redo log（重做日志）来实现

 

对于一个已经提交的事务，在事务提交后即使系统崩溃或宕机了，这个事务对数据库中所做的更改也不能丢失。Mysql为了提高性能使用了 BufferPool （缓存池），事务会先修改或读取 BufferPool 中的数据，如果内存中不存在当前要操作数据，会从硬盘读取到内存的 BufferPool 中。Mysql 在 更新记录写入 BufferPool 之前会把记录先写到 redolog （重做日志），当事务提交时会先将 redolog 通过刷盘机制持久化到硬盘，如果出现宕机，mysql重启后将 redolog 中的事务重放执行。

InnoDB中基于事务实现，提供了2个事务日志，redo log(重做日志) 和 undo log（回滚日志），其中redo log 分2部分，其中逻辑日志（redo log buffer，重做日志缓冲）与 物理日志（保存在硬盘中，ib_logfile0、ib_logfile1），而undo log则属于逻辑日志，保存缓冲区的表空间。

复制代码
-- 查看关于引擎的全局变量
SHOW VARIABLES like "%innodb%";
-- innodb_log_buffer_size ： 日志缓存区的内存大小是16M
-- innodb_log_files_in_group ：redo log日志组的数量，2则表示2个redolog交替使用
-- innodb_log_group_home_dir ：redo log日志组的存储目录，./表示在mysql的data目录下
-- innodb_flush_log_at_trx_commit：刷盘策略，默认1，值可以是0，1，2
--                                 0. MySQL的每次事务提交时不进行刷盘操作，由mysql自己的主县城每隔1秒进行刷盘
--                                 1. MySQL的每次事务提交时都会同步刷盘一次数据
--                                 2. MySQL的每次事务提交时只会把redolog日志缓存区数据写入文件系统缓存，而不会通过硬盘中，由操作系统决定什么时候同步到物理日志
事务日志
复制代码

 

 附：

1 什么是脏读？什么是幻读？什么是不可重复读？

脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。脏读，强调的是第二个事务读到的不够新。

例如：张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，
事务B正在读取张三的工资，读取到张三的工资为8000。
随后，
事务A发生异常，而回滚了事务。张三的工资又回滚为5000。
最后，
事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的。不可重复读的重点是修改，同一事务，两次读取到的数据不一样。

例如：

在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，
事务B把张三的工资改为8000，并提交了事务。
随后，
在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

幻读：指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。 同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象 发生了幻觉一样。幻读的重点在于新增或者删除，同样的条件 , 第 1 次和第 2 次读出来的记录数不一样

例如：

目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。
此时，
事务B插入一条工资也为5000的记录。
这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

 



 

posted on   大明花花  阅读(77)  评论(0)  编辑  收藏  举报
相关博文：
·  python面试题汇总
·  数据库5-索引分类和管理，外键约束，python操作mysql的数据库驱动模块pymysql和数据库ORM模块SQLAlchemy
·  脏读，不可重复读，幻读是如何发生的？
·  数据库事务的四种隔离级别
·  数据库事务的四种隔离级别知识
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
Copyright © 2025 大明花花
Powered by .NET 9.0 on Kubernetes Powered by: 博客园
点击右上角即可分享
微信分享提示
AI IDE Trae