前面介绍的大部分 SQL 语句都是对一个或者多个表的单个查询,但是实际情况下一个完整的操作可能是由多个语句组合而成的,比如考虑下面这个下单流程:
可以说存储过程就是数据库 SQL 语言层面上的代码封装和重用,可以回传值,也可以接受参数。可以将其视为批文件,但作用不仅限于批处理。
存储过程简单、安全、高性能。不过有些数据库管理员会限制存储过程的创建权限,只允许用户使用,但不允许用户创建存储过程。
# 创建一个计算平均价格的存储过程 # 查看创建存储过程的语句 # 调用存储过程查询平均价格
这里的存储过程使用了参数,也可以不使用参数,和其他语言中的函数类似。
删除使用 drop
关键字,如果不存在这个存储过程会报错,此时可以增加 if exists
关键字:
变量(variable) 内存中一个特定的位置,用来临时存储数据。
存储过程输入了 4 个参数,一个输入参数,还有三个用来存储的参数,每个参数用 in
(传递给存储过程)、out
(从存储过程传出)、inout
(对存储过程传入和传出)指定参数。
MySQL 中的变量都必须以 @ 开始,存储过程中检索得到的值使用 into
保存到相应变量,之后可以就可以查询到变量中存储的值了。
# 存储过程输入输出参数
# 调用存储过程查询产品平均价格
# 查询刚刚输出的变量
再试个例子,使用存储过程计算出指定订单号的总价,并输出到变量中:
# 计算指定订单号的总价格,并输出到变量中
存储过程也可以使用 if (条件) then ... elseif (条件) then ... else
语句,比如现在要计算折扣后的商品价格,总商品数量 3 件 8 折,4 件 7 折,这里使用存储过程:
# 首先为了方便后面计算订单总金额,创建一个查询订单总金额的视图
# 总商品数量 3 件 8 折,4 件 7 折,计算折扣后的产品价格,
# 创建一个变量保存商品总数
# 计算该订单号的商品总件数
# 小于 3 件无折扣
# 调用存储过程查询折扣后的金额
这个例子中我们使用了一个临时变量 prod_count,计算出该订单总件数之后将其赋到这个临时变量中,然后在之后的 if else
条件语句中对其进行判断,再通过视图计算出总金额,最后保存给输出变量。
有时,需要在检索出来的行中前进或后退一行或多行,这就是使用游标的原因。游标(cursor)是一个存储在 MySQL 服务器上的数据库查询,它不是一条 select
语句,而是被该语句检索出来的结果集。在存储了游标之后,应用可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。
MySQL 中的游标只能用于存储过程或函数。
游标处理分为下面几个步骤:
select
语句;
select
语句把数据实际检索出来;
end
语句时关闭游标。
首先使用 declare
定义了几个局部变量,这几个变量用来存中间值,其中默认值为 false 的 done
是循环的终止条件,将在后面的 repeat
语句中用来作为判断是否继续循环的标志位,当 repeat
没有更多行供循环时满足 not
found
,此时给 done
赋值 true 终止循环。在循环体中使用上一章的存储过程给表 ordertotals 填充计算的订单实际价格。
declare
语句是有顺序的,局部变量需要在句柄之后定义,句柄必须在游标之后定义,否则会报错。
除了 repeat
循环外,MySQL 还支持 loop
循环、while
循环,基本大同小异,可以自己查询学习一下。
如果你想要某些语句在事件发生时自动执行,可以考虑触发器。
for each row
表示对每个插入行执行触发器。只有表
# 创建一个触发器,在新的产品插入时给临时变量赋值
触发器要谨慎使用,由于触发器是针对每一行的,对增删改非常频繁的表上切记不要使用触发器,因为会非常消耗资源。
insert
触发器内可以通过访问名为 new
的虚拟表访问被插入的行;
before insert
语句中可以通过更改 new
虚拟表中的值来修改插入行的数据;
# 插入用户后获取这个新用户自动生成的的 ID 并且赋值给临时变量
before
经常被用于数据验证。
delete
触发器内可以通过访问名为 old
的虚拟表来访问被删除的行;
old
虚拟表中的字段都是只读的,不能修改。
这里使用 before
而不是 after
的原因是,如果因为某种原因顾客信息不能存档,delete
操作将会放弃,避免信息丢失。
update
触发器内可以通过访问名为 old
的虚拟表访问更新前的值,访问名为 new
的虚拟表来访问更新后的值;
old
虚拟表中的字段都是只读的,不能修改。
事务处理(transaction processing)可以用来维护数据库的完整性,它保证成批的 MySQL 操作要么完全执行,要么完全不执行。
举个例子,如果我们要转账给别人,首先把别人账户增加钱,再把我们账户上钱扣除,如果中间出现问题,那么麻烦就大了。
或者在当前数据库中,如果我们要添加一个订单信息,分为下面几步:
如果发生了某种数据库故障(超出磁盘限制、安全限制、表锁等),阻止了一个完整的流程,会出现什么情况 。如果故障出现在 1 和 2 之间,这没什么关系,因为一个顾客没有订单信息是合法的,如果出现在 3 和 4 之间,那么就会出现一个空的订单,这个订单没有包含的产品信息,这很严重,如果出现在 5 时,添加 orderitems 过程中出现问题,那么可能出现订单信息不完整的情况,也很严重。
使用事务可以避免这个情况,如果中间发生了问题,那么则回退到某个安全的状态。
那么使用事务如何处理这个过程呢:
MySQL 中用户的任何一个更新操作(写操作)都被视为一个事务,这就是所谓的隐含提交(implicit commit),相当于 MySQL 帮你在后台提交了。
可以针对每个连接使用 set autocommit=0
来设置 MySQL 不自动提交更改,设置之后,每个 SQL 语句或者语句块所在的事务都需要显式 commit
才能提交事务。
但在事务处理块中,提交不会隐含地进行,需要你自己来显式的调用:
# 删除订单详情表总中 20007 相关订单详情,再删除订单表中的 20007
由于设计到订单和订单详情,所以使用事务来保证订单是完整的被删除,而不是部分删除。如果两个 delete
语句中发生了错误,那么 commit
将不会被执行。
之前的 rollback
、commit
只能对整个事务处理块整体提交或回滚,某些复杂场景下可能要部分回滚或者部分恢复,比如之前例子,如果订单信息增加失败,可能要回滚到添加用户信息后。
此时可以使用保留点,这样在发生问题时回滚到保留点处即可。保留点使用比较简单:
保留点可以使用多一点,当在事务完成时,他们将会被自动释放,也可以使用 release savpoint
来手动释放。
对数据库来说,用户应该对他们需要的数据具有适当的访问权,既不能多也不能少。
给不同的用户提供不同的访问权,这就是访问控制。
对于 root 登陆的使用需要十分谨慎小心,仅在绝对需要时才使用,不应在日常 MySQL 操作中使用 root 账户。
设置权限用 grant
关键字:
# 显示用户张三的权限
权限设置和用户设置还有很多内容,不是本文的重点,可以百度一下或者看文档。
数据库也是经常需要备份的,可以使用以下方法:
backup table
或 select into outfile
转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名,此系统文件必须不存在,否则会出错。数据可以用 restore table
来复原。
check table
用来针对许多问题对表进行检查。
repair table
来修复。
optimize table
来收回所用空间,从而优化表的性能。
记录 Mysql 运行过程中的 Error、Warning、Note 等信息,系统出错或者某条记录出问题可以查看错误日志。
记录包括查询、修改、更新等的每条语句。
包含一些事件,描述了数据库的改动,如建表、数据改动等,主要用于备份恢复、回滚操作等。
flush logs
可以手动产生新文件,Binlog 十分重要,产生问题要回滚用 Binlog 就可以了。
记录执行缓慢的任何查询,在优化数据库时比较有用。
性能是数据库永恒的追求,对于性能有以下 Tips:
show variables;
和 show status;
查看配置
select
中使用 or
快。
网上的帖子大多深浅不一,甚至有些前后矛盾,在下的文章都是学习过程中的总结,如果发现错误,欢迎留言指出,如果本文帮助到了你,别忘了点赞支持一下哦,你的点赞是我更新的最大动力!~