MySQL入门 | day08 | 触发器、事件、事务

一、触发器

触发器是一种特殊类型的存储过程，不由用户直接调用。

创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。

触发器可以查询其他表，而且可以包含复杂的 SQL 语句。

它们主要用于强制服从复杂的业务规则或要求。 例如，您可以根据客户当前的帐户状态，控制是否允许插入新订单。

触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系

原文链接：https://blog.csdn.net/yefufeng/article/details/119982107

触发器的作用

1.1-1.3 增强数据一致性

1.4 审计， 记录对数据库的修改

1.1创建触发器

我们在payments中每加入一条数据，那么invoices在payment_total中的数据就会更新

DELIMITER $$

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
		FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id; 
END $$

DELIMITER ;

    NEW关键字表示得到的新的数值

    此外还有OLD关键字 在更新或删除行时使用 

    --  -- -- 触发器中我们可以修改任何数据 ，除了触发器所在表，否则会无限循环

在payments中插入数据

INSERT INTO payments
VALUES (DEFAULT , 5, 3, '2019-01-01', 10,1)

 则invoices中数据更新完成

1.2 查看触发器 

SHOW TRIGGERS

SHOW TRIGGERS LIKE 'payments%'

1.3 删除触发器

DROP TRIGGER IF EXISTS 触发器名称

1.4 使用触发器进行审计

 整体代码：

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_insert;

CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
		FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id; 
    
    INSERT INTO payments_audit
    VALUES(NEW.client_id, NEW.date, NEW.amount, 'INSERT', NOW());
    
END $$

DELIMITER ;

用来审计部分的代码

 INSERT INTO payments_audit
    VALUES(NEW.client_id, NEW.date, NEW.amount, 'INSERT', NOW());

二、事件

事件是根据计划执行的任务或是一堆SQL代码，可以执行一次，也可以按照某种规律执行。

通过事件，我们可以自动化数据库的维护任务，比如删除已经过期数据，汇总数据生成报告。

设计事件之前，首先打开MySQL事件调度器（是一个后台程序）他时刻都在寻找需要执行的事件

SHOW VARIABLES ;

SHOW VARIABLES LIKE 'event%';

 如果是off，用set语句打开

SET GLOBAL event_scheduler = ON

 关掉同理，ON改成OFF即可

创建事件

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE 
	-- AT '2019-05-01'
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

查看事件

SHOW EVENTS;

删除事件

DROP EVENT IF EXISTS 事件名

更改事件 

 ALTER EVENT (和创建相同)

DELIMITER $$

ALTER EVENT yearly_delete_stale_audit_rows
ON SCHEDULE 
	-- AT '2019-05-01'
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

 还可以用ALTER EVENT来启用或者禁止一个事件

ALTER EVENT yearly_delete_stale_audit_rows DISABLE -- 禁用

ALTER EVENT yearly_delete_stale_audit_rows ENABLE -- 启用

三、事务 

1.定义

事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于物理当中的原子（一种不可分割的最小单位）。

往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。

就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。所有受到影响的数据将返回到事务开始之前的状态，但是如果单元中的所有sql语句都执行成功的话，那么该事务也就被顺利执行。

原文链接：https://blog.csdn.net/qq_56880706/article/details/122653735

2.事务的属性

        1.原子性   事务中的操作要么都执行成功 要么都失败

        2.一致性   通过使用事务 数据库始终保持一致的状态

        3.隔离性   事务的执行时不会与其他事务相互干扰

        4.持久性   一旦事务被提交 事物产生的更改是永久的

3.创建事务

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES(1, '2019-01-01', 1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1 , 1, 1);

COMMIT;

某些情况下使用rollback，这样会退事务并撤销所有更改

START TRANSACTION;

语句1

语句2

ROLLBACK;

自动提交的系统管控（MySQL会把不出现错误的语句就会自动提交）

SHOW VARIABLES LIKE 'autocommit';

4.并发

第一步，建立两个数据库连接

使用同样代码

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

 第一个连接依次执行但不提交       在依次执行第二个连接中代码

必须等第一个事务提交之后，第二个事务才可以顺利执行。

并发可能出现的问题：

数据丢失 Lost Updates

当两个事务尝试更新相同的数据并且没有上锁是，就会发生。

在这种情况下，较晚提交的事务会覆盖较早事务做的更改，较早事务做的更改数据就会丢失

脏读 Dirty Reads

事务读取了一个尚未被提交的数据  ； 可以设置隔离级别

不可重复读 Non-repeating Reads

对于两个事务，1读取了一个字段，然后2更新了该字段之后，1在读取同一个字段，值就不同了

幻读 Phantom Reads

对于两个事务，1在A表中读取了一个字段，然后2又在A表中插入了一些新的数据时，1再读取该表时，就会发现神不知鬼不觉的多出几行了…

5.事务的隔离级别

越低的隔离级别越容易并发  由下向上等级越来越低 MySQL默认的隔离级别时Repeatable Read。

在不需要精确的一致性的批量报告或者数据怎么更新的情况下可以使用读未提交和读已提交。

设置隔离级别

查看当前隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';

设置新的隔离级别

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

为当前会话或连接所有以后的事务设定隔离级别(SESSION关键字)

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 为所有新事务设定全局隔离级别（GLOBAL关键字）

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

        读未提交 

建立两个链接

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1;

先执行上边第一步，设立读未提交级别

START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
COMMIT;

依次执行但不提交，再去执行T1中语句

结果返回

T1读出来T2中未提交的数据 

        读已提交 

同上，我的一直有问题还没解决掉...

        可重复读

该隔离级别为mysql的默认隔离级别；它对某字段进行操作时，其他事务禁止操作该字段。它总能保持你读取的数据是一致的

-- T1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

 依次执行T1，返回值30

将T2中points改为40在执行T1 ，返回值30

-- T2
START TRANSACTION;
UPDATE customers
SET points = 30
WHERE customer_id = 1;
COMMIT;

          序列化

该隔离模式下执行的事务在对某表进行操作期间，禁止其他所有事务对该表进行任何操作

6.死锁

当不同事务均因握住了别的事务需要的’锁‘而无法完成的情况，两个事务一直在等待对方，并永远不会释放锁。

 -- T1分别更新customers和orders中两条数据
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;

-- T2 更新与T1中表顺序相反但数据相同的数据
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA' WHERE customer_id = 1;
COMMIT;

T1更新customers中数据时就会自动锁定该数据，其他事务要更新必须等待T1更新完成

T1，T2互相等待，形成死锁

最后

以上就是清新豆芽为你收集整理的MySQL入门 | day08 | 触发器、事件、事务的全部内容，希望文章能够帮你解决MySQL入门 | day08 | 触发器、事件、事务所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。