存储过程procedure
创建 – delimiter – call
delimiter $$
create procedure get_clients() -- () 用于添加参数为存储过程传递值
begin
select * from clients;
end $$
delimiter ;
call get_clients -- 使用 call 调用存储过程
也可用 'c#',Java,python 等应用代码调用存储过程
delimiter $$
create procedure get_invoices_with_balance()
begin
select *
from invoices
where (invoice_total - payment_total) > 0;
end $$
delimiter ;
删除 – drop
drop procedure if exists get_clients -- if exists 防止重复删除
delimiter $$
create procedure get_clients ()
begin
select *
from clients
where (invoice_total - payment_total)>0;
end $$
delimiter ;
添加参数
drop procedure if exists get_clients_by_state;
delimiter $$
create procedure get_clients_by_state(state char(2))
begin
select *
from clients c
where c.state =state;
end $$
delimiter ;
-- call get_clients_by_state('CA')
drop procedure if exists get_invoices_by_client;
delimiter $$
create procedure get_invoices_by_client(client_id int) -- 类型看设计表
begin
select *
from invoices i
where i.client_id = client_id;
end $$
delimiter ;
默认值参数
drop procedure if exists get_clients_by_clients;
delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
if state is null then
set state = 'CA';
end if;
select *
from clients c
where c.state = state ;
end
$$
delimiter ;
call get_clients_by_clients(null)
drop procedure if exists get_clients_by_clients;
delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
if state is null then -- 未传参 显示全部
select *
from clients ;
else
select *
from clients c
where c.state = state ;
end if;
end
$$
delimiter ;
call get_clients_by_clients(null)
drop procedure if exists get_clients_by_clients;
delimiter $$
create procedure get_clients_by_clients(state char(2))
begin
select *
from clients c
-- 若state 为空则 c.state = c.state 永久满足
where c.state = ifnull(state,c.state) ;
end
$$
delimiter ;
call get_clients_by_clients('CA')
drop procedure if exists get_payments;
delimiter $$
create procedure get_payments
(client_id int,payment_method_id tinyint)
begin
select *
from payments p
where
p.client_id = ifnull(client_id,p.client_id) and
p.payment_method = ifnull(payment_method_id,p.payment_method);
end
$$
delimiter ;
call get_payments(null,2)
参数验证 – decimal、 singal sqlstate
drop procedure if exists make_payment;
delimiter $$
create procedure make_payment(
invoice_id int,
-- decimal 小数类型 9位数,其中2位小数
payment_amount decimal(9,2),
payment_date date
)
begin
if payment_amount < = 0 then
signal sqlstate '22003' -- signal 抛出异常
-- 给开发设置错误信息
set message_text = 'invalid payment amount';
end if;
update invoices i
set
i.payment_total = payment_amount,
i.payment_date = payment_date
where i.invoice_id = invoice_id;
end
$$
delimiter ;
call make_payment(2,-100,'2019-01-01');
输出参数 – 少用
drop procedure if exists get_uppaid_invoices_for_client;
delimiter $$
create procedure get_uppaid_invoices_for_client(
client_id int,
-- out 修正为输出参数
out invoices_count int, out invoices_tatal decimal(9,2)
)
begin
select count(*),sum(invoice_total)
into invoices_count,invoices_tatal
from invoices i
where i.client_id = client_id
and payment_total = 0;
end
$$
delimiter ;
变量
用户或会话 变量 @ – 用户断线时,变量清空
通常我们会在调用输出参数的存储过程时使用这些变量
set @invoices_count = 0
本地变量 declare decimal — 执行计算任务
drop procedure if exists get_risk_factor;
delimiter $$
create procedure get_risk_factor()
begin
-- declare 声明本地变量
declare risk_factor decimal(9,2) default 0;
declare invoices_total decimal(9,2);
declare invoices_count int;
select count(*),sum(invoice_total)
into invoices_count,invoices_total
from invoices;
set risk_factor = invoices_total / invoices_count * 5;
select invoices_total,invoices_count,risk_factor;
end
$$
delimiter ;
call get_risk_factor();
函数 – 单一值
drop function if exists get_risk_factor_for_client;
delimiter $$
-- 函数 还能返回 单一值
create function get_risk_factor_for_client(client_id int)
returns integer -- 确定返回值类型为 integer
-- 设置函数属性
-- deterministic -- 确定性
reads sql data -- 存在选择语句 读取数据
-- modifies sql data -- 修改数据
begin
declare risk_factor decimal(9,2) default 0;
declare invoices_total decimal(9,2);
declare invoices_count int;
select count(*),sum(invoice_total)
into invoices_count,invoices_total
from invoices i
where i.client_id = client_id;
set risk_factor = invoices_total / invoices_count * 5;
-- 0 / null = null
return ifnull(risk_factor,0);
end$$
delimiter ;
select client_id,
`name`,
-- 0 / null = null
get_risk_factor_for_client(client_id) as risk
from clients
触发器 – 保证数据一致性
创建
drop trigger if exists payments_after_insert;
delimiter $$
create trigger payments_after_insert -- 触发器在 payments 插入后 触发
-- before delete/update on payments
after insert on payments
for each row -- 触发器会作用于每个受影响的行
begin
update invoices
-- new 返回刚插入的行 ;old 返回刚删除或更新的行
set payment_total = payment_total + new.amount,
payment_date = new.date
where invoice_id = new.invoice_id;
end
$$
delimiter ;
insert into payments
values(default,5,2,'2019-10-10',10,1)
drop trigger if exists payments_after_delete;
delimiter $$
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;
end
$$
delimiter ;
delete from payments
where payment_id = 9
查看 – 命名习惯
show triggers like 'payments%'
使用触发器进行审计
-- 创建一个审计表
drop table if exists payments_audit;
create table payments_audit
(
client_id int not null,
date date not null,
amount decimal(9,2),
action_type varchar(50) not null,
action_date datetime not null
)
drop trigger if exists payments_after_insert;
delimiter $$
create trigger payments_after_insert -- 触发器在 payments 插入后 触发
-- before delete/update on payments
after insert on payments
for each row -- 触发器会作用于每个受影响的行
begin
update invoices
-- new 返回刚插入的行 ;old 返回刚删除或更新的行
set payment_total = payment_total + new.amount,
payment_date = new.date
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
values(default,5,2,'2019-10-10',10,1)
事件 – MySQL事件调度器 – 自动启动
创建
-- 查看事件调度器
show variables like 'event%';
-- 设置调度器为开 / 关 on / off
set global event_scheduler = on ;
删除drop,更改 alter
drop event if exists yearly_delete_stale_audit_rows;
delimiter $$
-- alter event ...
create event yearly_delete_stale_audit_rows
on schedule
-- at '2022-03-03' -- 单次定时操作
-- every 循环工作
every 1 day starts '2019-01-01' ends '2029-02-01'
do begin
delete from payments_audit;
-- where action_date < NOW() - interval 1 year;
end
$$
delimiter ;
-- 查看事件
show events like 'year%'
-- 修改
alter event yearly_delete_stale_audit_rows disable ; -- enable
事务 – 多个操作保持一致
创建
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 -- 回滚 -- 设置条件 取消处理
查看 自动提交引擎 autocommit 状态, 默认开启 on
show variables like 'autocommit'
并发和锁定 — 难点
常见并发问题
丢失更新
脏读
不可重复读
幻读 – 突然出现数据
事务隔离级别-- 默认为 repeatable read
设置
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL isolation-level
其中的<isolation-level>可以是:
– READ UNCOMMITTED
– READ COMMITTED
– REPEATABLE READ
– SERIALIZABLE
first connection
-- 只会对下一个事务起作用
set transaction isolation level read uncommitted; -- 存在脏读问题
-- mysql 执行的每一个语句 都会在事务中打包 并自动提交
select points from customers where customer_id = 1;
set transaction isolation level read committed; -- 克服脏读 但存在同一事件两次读 数据不一致
-- mysql 执行的每一个语句 都会在事务中打包 并自动提交
select points from customers where customer_id = 1;
set transaction isolation level repeatable read; -- 克服数据读取不一致,但存在幻读
start transaction;
select * from customers where state = 'VA';
commit;
set transaction isolation level serializable; -- 克服幻读
start transaction;
select * from customers where state = 'VA';
commit;
在逐行运行到select时去运行第二连接的事务但不要提交,由于是序列级别,
所以第一连接事务的select 会等待第二连接事务的提交 commit
second connection
start transaction ;
update customers set points = 20 where customer_id = 1;
-- commit ;
rollback ;
start transaction ;
update customers set state = 'VA' where customer_id = 1;
commit ;
死锁 Deadlock 错误码:1213
如果事务以相反的顺序更新记录,很可能会发生死锁
-- first connection
start transaction ;
update orders set status = 2 where order_id = 1;
update customers set state = 'cc' where customer_id = 1;
commit;
-- second connection
start transaction ;
update customers set state = 'cc' where customer_id = 1;
update orders set status = 2 where order_id = 1;
commit;
传送门
SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!
SQL存储过程有什么用?
MySql中delimiter的作用是什么?
Navicat Premium 查询sql文件保存位置在哪里?
SQLSTATE values and common error codes
从SQL语言的分类谈COMMIT和ROLLBACK的用法
关于mysql的参数autocommit
SQL-数据库脏读、不可重复读、幻象读的解释
sql server 并发_并发问题– SQL Server中的理论和实验
MySQL查看和修改事务隔离级别
3分钟搞懂MySQL事务隔离级别及SET TRANSACTION影响事务