第四部分:高阶主题——触发器、事件、事务、并发
触发器
-
CREATE TRIGGER
-
AFTER(BEFORE) INSERT(UPDATE、DELETE、SELECT) ON payments
在payments表中,插入后,
-
FOR EACH ROW
对每一行都起作用
-
NEW.amount
获取新插入数据的字段值,与之相对应的OLD.amount 获取刚刚删除数据的字段值
CREATE TRIGGER payments_after_insert AFTER INSERT ON payments -- 指明触发的时机 FOR EACH ROW -- 指明作用范围,这里指每一行 BEGIN -- 触发体 (满足触发条件后要执行的SQL语句) UPDATE invoices SET payment_total = payment_total + NEW.amount, payment_date = NEW.date WHERE invoice_id = NEW.invoice_id; END; -- 触发体
插入之前 invoices表
执行插入语句
INSERT INTO payments VALUES (DEFAULT, 5, 3, DATE(NOW()), 10, 1);
执行后满足触发条件,执行触发体中的sql代码,更新invoices表数据
-
-
SHOW TRIGGER
查询已创建的触发器,不同于函数,存储过程,视图,事件,可以从图形化界面中显示
-
审计表。可以用来记录用户的操作行为
-- 审计表 USE sql_invoicing; CREATE TABLE payments_audit ( client_id INT NOT NULL, date DATE NOT NULL, amount DECIMAL(9, 2) NOT NULL, action_type VARCHAR(50) NOT NULL, action_date DATETIME NOT NULL ); 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, payment_date = NEW.date WHERE invoice_id = NEW.invoice_id; INSERT INTO payments_audit VALUES (5,NEW.date,NEW.amount,'INSERT',NOW()); END; DROP TRIGGER IF EXISTS payments_after_delete; CREATE TRIGGER payments_after_delete AFTER DELETE ON payments FOR EACH ROW BEGIN UPDATE invoices SET payment_total = payment_total - OLD.amount WHERE invoice_id = OLD.invoice_id; INSERT INTO payments_audit VALUES (5,OLD.date,OLD.amount,'DELETE',NOW()); END; INSERT INTO payments VALUES (DEFAULT, 5, 3, DATE(NOW()), 10, 1); DELETE FROM payments WHERE payment_id = 15;
事件
-
CRETE EVENT
-
ON SCHEDULE … AT / EVERY
事件计划执行的时间,AT ‘指定的时间’ 如果没有具体的时间,是当天的00:00:00执行
EVERY 1 YEAR / DAY / MONTH (STARTS ‘开始时间’ ENDS ‘结束时间’ )
-
DO BEGIN (要执行的SQL语句) END
-- CREATE EVENT DROP EVENT IF EXISTS yearly_delete_stale_audit_rows; -- 事件如果是执行一次的,并且执行完了,就销毁了 CREATE EVENT yearly_delete_stale_audit_rows ON SCHEDULE -- 定义计划执行时间 AT '2024-08-19 14:05:00' -- 执行一次,在什么时候,如果没有设置具体的时间,默认是00:00:00执行 -- EVERY 1 YEAR STARTS '2024-01-01' ENDS '2084-01-01' -- EVERY 每多少年月日执行一次,STARTS 和 ENDS 可写可不写 DO BEGIN DELETE FROM payments_audit WHERE action_date < DATE_SUB(NOW(),INTERVAL 1 YEAR); END;
-
-
ALERT EVENT
修改事件或者修改事件属性
-- 修改事件体或者一些属性 ALTER EVENT yearly_delete_stale_audit_rows ON SCHEDULE EVERY 1 DAY STARTS '2024-08-19 00:00:00' ENDS '2024-08-25 00:00:00' DO BEGIN DELETE FROM payments_audit WHERE action_date < DATE_SUB(NOW(),INTERVAL 1 YEAR ); END; ALTER EVENT yearly_delete_stale_audit_rows ENABLE ; ALTER EVENT yearly_delete_stale_audit_rows DISABLE ;
-
SHOW EVENT
SHOW EVENTS LIKE 'yearly%';
事务
怎么说呢,事务就是把一堆SQL语句围起来成一个整体,成功提交了就全部成功了,失败了就全部失败了。然后事务可以多个一起运行,可以设置隔离级别(为了处理多个事务并行出现的问题)
那些傻逼ACID就不去记了,没什么鸟用,什么四大特性,全是废话
-- 事务
USE sql_store;
START TRANSACTION ;
INSERT INTO orders(customer_id, order_date, status)
VALUES (1,'2024-08-19',1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,1);
COMMIT ;-- ROLLBACK 手动回滚事务
-- 默认一条SQL就是一个事务,Mysql将一条sql自动放入一个事务中,然后自动提交
SHOW variables like 'autocommit%';
SET AUTOCOMMIT = OFF;
-
LAST_INSERT_ID()
获取最后一条插入数据的ID
-
多个事务对同一行数据进行操作时,会对这一行数据上锁(行锁)后进入的事务会等待先进入的事务执行完后再执行(正常情况下是这样的),先执行前四句,不COMMIT,然后再去启动一个新的事务,这个事务因该会打圈圈等待的,用MysqlWorkBench是这样的,但是我用DataGrip他会给我自动提交第一个未结束的事务🙃。查了下原因可能是DG做了优化。。。我也不知道是不是这样,这里如果有知道的大佬可以解答一下
-
TRANSACTION ISOLATION LEVEL
-
READ-UNCOMMITED
这个隔离级别,事务会读取其他事务未提交的数据
执行顺序:136–>11–>12 13 14–>137
结果:读出的数据数据是未提交的数据20
如果事务发生了回滚,就可能读出数据库不存在的数据,就发生了所谓的脏读
-
READ-COMMITED
这个隔离级别,就可以解决脏读问题,事务未提交的数据,别的事务无法读取
但是如果一个事务里面有多次对同一行数据进行读取,会出现两次读取的数据不一致的问题
执行顺序:141–>142–>143–>11–>12 13 14–>15–>144–>145
结果:143读出2352 144读出20
-
REPEATABLE-READ
MySQL默认隔离级别,可以解决两次读出数据不一致的问题,两次读取都是一样
**执行顺序:**148–>149–>150–>151–>11–>12 13 14 15–>152–>153
**结果:**151–20 152–20
可能造成的后果,如果另一个事务对数据进行了跟新,当前事务可能无法读取到更新后的数据
-
SERIALIZABLE
最高隔离级别,必须等待另一个事务结束提交后才执行SQL
-
-
DEAD LOCK
两个事务相互等待对方释放锁(事务提交后释放锁)
-- DEAD LOCK USE sql_store; START TRANSACTION ; UPDATE customers SET state = 'VA' WHERE customer_id = 1; -- 1 对customer_id = 1的数据加锁 UPDATE orders SET status = 1 WHERE order_id = 1; -- 3 请求order_id = 1 的数据 COMMIT ;
START TRANSACTION ; UPDATE orders SET status = 1 WHERE order_id = 1;-- 2 对order_id = 1的数据加锁 UPDATE customers SET state = 'VA' WHERE customer_id = 1; -- 4 请求customer_id = 1的数据 COMMIT ;