MySQL高级进阶：事务，变量，触发器

最新推荐文章于 2024-07-22 03:13:52 发布

无名一小卒

最新推荐文章于 2024-07-22 03:13:52 发布

阅读量775

点赞数

文章标签： MySQL进阶

本文链接：https://blog.csdn.net/h1025372645/article/details/89104725

版权

MySQL基础专栏收录该内容

10 篇文章 0 订阅

订阅专栏

事务

为什么需要事务

通过一个案例来了解为什么需要事务

现在有两个人A,B，A要向B进行银行转账，如果转账成功，则A的账号中的钱减少，B的账户中的钱增加

但是，当A操作完转账之后，B没有收到转账之前，突然断电了，那么用户A的钱减少了，B的钱却没有增加，怎么解决这个问题

答案就是A操作完转账之后，不要立刻对A的账号余额进行操作，把A的转账的操作记录下来，当B收到钱后，执行A的转账操作，同时对A与B的账户余额进行修改。

什么是事务

事务：transaction，一系列要发生的连续的操作。

事务的特点：连续的操作要么全部成功，要么全部失败。

事情安全：一种保护连续操作同时满足（实现）的一种机制。

事务安全的意义：保证数据操作的完整性。

事务操作

事务的操作分成2种：自动事务（默认的），手动事务

手动事务操作流程

1：开启事务

告诉系统以下所有的操作（写）不要直接写入到数据表，先存放到事务日志。

START TRANSACTION;

2：进行事务操作：

准备工作，建立一张操作表：

CREATE TABLE tb_user(
	id INT AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL,
	balance DOUBLE NOT NULL
)ENGINE=INNODB DEFAULT CHARSET = utf8;


INSERT INTO tb_user(NAME,balance) VALUES ('zs',1000),('ls',1000);

Engine是数据库引擎，使用事务需要支持事务的数据库引擎

进行操作：ls账户减少 zs账户增加

UPDATE tb_user SET balance = balance -100 WHERE id = 2;
UPDATE tb_user SET balance = balance +100 WHERE id = 1;

此时，没有提交事务，打开表查看数据时，两人的余额都还是1000

3：关闭事务

选择性的将日志文件中操作的结果保存到数据表（同步 commit）或者说直接清空事务日志（rollback所有的操作都放弃）

（1）提交事务：同步数据表（操作成功）：commit

（2）回滚事务：直接清除事务日志（操作失败），放弃本次操作：rollback

自动事务

在mysql中：默认的都是自动事务处理，用户操作后立刻马上同步到数据表中。

自动事务：系统通过autocommit变量控制

查看自动提交变量

SHOW VARIABLES LIKE 'autocommit';

修改自动提交

注意：自动提交设置为off之后，需要手动commit/rollback

SET autocommit = off;

可以打开两个cmd控制台，修改自动提交变量的值后，可以验证是否修改成功

事务的原理

事务开启之后，所有的操作都会保存到事务日志，

事务日志只有在得到commit命令之后，才会将数据同步到数据表中

其他任何情况都会清空（rollback，断电，断开连接）

下面用一张图来解释事务的操作过程

回滚点

在某个成功的操作之后，后续的操作有可能失败，但是不管后续的操作成功与失败，前面操作都已经成功了

如果需要回滚的话，我们只需要回滚一部分，而不是回滚所有。

回滚到哪里，我们需要设置一个位置，这个位置称为回滚点。

设置回滚点，并回滚到该点

#回滚点
START TRANSACTION;

#事务处理1 zs加钱
UPDATE tb_user SET balance = balance + 100 WHERE id = 1;
#设置回滚点
SAVEPOINT sp1;

SELECT * FROM tb_user;

#事务处理2 ls减钱
UPDATE tb_user SET balance = balance - 100 WHERE id = 2;

SELECT * FROM tb_user;

#回滚到sp1
ROLLBACK TO sp1;

通过select查询语句，可以清楚的看到每一步的执行结果

事务特性（面试题）

事务有4大特性（ACID）

A：Atomic 原子性，事务的整个操作是一个整体，不可分割，要么全部成功，要么全部失败。

C：Consistency 一致性，事务操作的前后，数据表中的数据没有变化

I： Isolation隔离性，事务操作之间互相隔离，互不影响

D：Durability 持久性，数据一旦提交，永久改变数据表的数据

锁机制

锁机制：innodb默认是行锁，但是如果在事务操作的过程中，没有使用到索引（主键是索引）

那么系统会自动搜索数据，自动升级为表锁

行锁：只有当前行被锁住，别的用户不能操作

表锁：整张表表锁住，别的用户不能操作。

因为这次的修改没有使用主键，所以整张表都被锁住了

当我在cmd中修改表的数据时，就会等待，等待时间过了，另一面还没有提交事务，就会出现以下错误

变量

变量分成2种：系统变量和自定义变量

系统变量

系统已经定义好的变量：大部分的时候用户不需要使用系统变量

系统变量是用来控制服务器的表现

查看系统变量

SHOW VARIABLES;

查看具体的变量值

SELECT @@autocommit,@@VERSION

修改系统变量

修改系统变量分为2种方式：会话级别和全局级别

会话级

临时修改，当前客户端当次有效

Set 变量名=值

Set @@变量名=值

SET auto_increment_offset = 100;

全局级别

一次修改，永久生效（对所有的客户端都生效）

Set global 变量名 = 值

SET GLOBAL auto_increment_offset = 1;

注意：如果其他客户端当前已经连上服务器，那么当次修改无效，退出重新登录才生效

自定义变量

定义变量

系统为了区分系统变量，规定用户自定义的变量必须使用一个@符号

set @变量名=值

SET @NAME = 'zs';

查看变量

SELECT @NAME;

变量赋值

在mysql中“=”会默认当做比较符（很多地方），mysql为了区分比较和赋值的概念，

重新定义了一个新的赋值符号： :=

SET @age := 19;

Mysql 允许从数据表中获取数据，然后赋值给变量：2种方式

方案一：边赋值，边查看结果

Select @变量名:=字段名 from 数据源

SELECT
	@NAME := NAME
FROM 
	tb_user;

方案二：只赋值不看结果

要求很严格：数据记录最多只允许获取一条:mysql不支持数组

Select 字段列表 from 表名 into 变量列表

SELECT 
	NAME,id
FROM
	tb_user
WHERE 
	id=1
INTO
	@NAME,@age;

注意

所有的自定义变量都是会话级别的：当前客户端当次连接有效

所有自定义变量不区分数据库。

触发器

什么是触发器

触发器：trigger

事先为某张表绑定代码，当表中的某些内容发生改变的时候（增删改）

系统会自动触发代码的执行

触发器：

事件类型：增删改，3种类型（insert,delete,update）

触发时间：前后：before和after

触发对象：表中的每一行（记录）

一张表中：最多能有6个触发器

创建触发器

在MySQL高级结构中，没有大括号，都是使用对应的字符符号代替，下面是语法

#基本语法

#临时修改语句结束符

delimiter 自定义符号：后续代码看到该符号，表示结束

create trigger 触发器名称  触发时间 时间类型 on 表名 for each row

begin   #代表左大括号：开始

         ....里面就是触发器内容，每行 内容都必须使用结束符“;”

end    #代表右大括号：结束

自定义符号

#语句结束符 恢复

delimiter ;

下面是代码实现，先准备操作数据

CREATE TABLE my_goods(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
price DECIMAL(10,2) DEFAULT 1,
inv INT COMMENT '库存数量'
)CHARSET utf8;

INSERT INTO my_goods VALUES(NULL,'iPhone6s',5288,100),(NULL,'s6','6088',100);

SELECT * FROM my_goods;

CREATE TABLE my_order(
id INT PRIMARY KEY AUTO_INCREMENT,
g_id INT NOT NULL COMMENT '商品ID',
g_number INT COMMENT '商品数量'
)CHARSET utf8;
SHOW CREATE TABLE  my_order;

SELECT * FROM my_order;

建立触发器：订单生成一个，商品库存减少

DELIMITER $$

CREATE TRIGGER after_order AFTER INSERT ON my_order FOR EACH ROW
BEGIN
	UPDATE my_goods SET inv = inv - 1 WHERE id = 1;
END
$$

DELIMITER ;

查看建立的触发器

SHOW CREATE TRIGGER after_order;

查看所有触发器

SHOW TRIGGERS ;

所有的触发器都会保存到系统表中：

Information_schema.triggers

触发器使用

触发器不需要手动调用，而是在某种情况下会自动触发。（订单表插入记录之后）

INSERT INTO my_order VALUES(NULL,1,2);

此时你会发现一个问题，无论你购买的数量写多少，货物表中的库存只会减少一个，这是因为在触发器中将数量写死了，下面将使用触发器的记录来解决这个问题；

修改触发器（删除）

触发器不能修改，只能先删除，后新增

Drop trigger 触发器的名称

DROP TRIGGER after_order;

SELECT 
	*
FROM
	information_schema.TRIGGERS;

这时就查不到触发器了

触发器记录

不管触发器是否触发了，只要当某种操作准备执行，

系统会将当前要操作的记录的当前状态和操作之后的状态分别记录下来，以供触发器使用

其中操作之前的状态保存old中，操作之后的状态保存到new中

old:代表的就是旧记录。插入操作old是没有的

new：代表的是新记录。删除操作new是没有的

old和new本身代表的就是记录本身：任何一条记录除了数据，还有字段名称

使用方式：

old.字段名称

new.字段名称

DELIMITER $$

CREATE TRIGGER after_order AFTER INSERT ON my_order FOR EACH ROW
BEGIN
	UPDATE my_goods SET inv = inv - new.g_number WHERE id = new.g_id;
END
$$

DELIMITER ;

INSERT INTO my_order VALUES(NULL,1,20);

SELECT 
	* 
FROM 
	my_goods;

SELECT 
	* 
FROM 
	my_order;

这样就可以解决上面出现的问题，订单表中加入相应的数据，货物表中也会对相应的数据进行准确的修改

注意：

（1）如果触发器内部只有一条SQL语句需要执行，可以省略大括号（begin和end）

（2）触发器可以很好的协调表内部的数据处理顺序和关系，但是从业务开发的角度出发

触发器会增加数据库维护的难度，所以减少使用触发器。

（3）通过业务去解决问题。企业中触发器用的很少。

无名一小卒

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL高级进阶：事务，变量，触发器

事务为什么需要事务通过一个案例来了解为什么需要事务现在有两个人A,B，A要向B进行银行转账，如果转账成功，则A的账号中的钱减少，B的账户中的钱增加但是，当A操作完转账之后，B没有收到转账之前，突然断电了，那么用户A的钱减少了，B的钱却没有增加，怎么解决这个问题答案就是A操作完转账之后，不要立刻对A的账号余额进行操作，把A的转账的操作记录下来，当B收到钱后，执行A的转账操作，同时...
复制链接

扫一扫