mysql 存储过程锁_MySQL之视图、触发器、存储过程、函数、事务、数据库锁

最新推荐文章于 2023-04-05 17:09:35 发布

lkxl

最新推荐文章于 2023-04-05 17:09:35 发布

阅读量144

点赞数

文章标签： mysql 存储过程锁

本文链接：https://blog.csdn.net/weixin_28751115/article/details/113150376

版权

本文详细介绍了MySQL数据库中的视图、触发器、存储过程、事务和锁机制。视图是表的抽象，方便数据查询和更新；触发器用于在数据修改前后执行特定操作；存储过程是一组预编译的SQL语句，提高执行效率；事务确保数据一致性，支持ACID特性；锁机制确保并发访问时的数据安全。文章通过实例展示了这些概念的使用和重要性。

摘要由CSDN通过智能技术生成

一、视图

视图：是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。

视图的特点：

1.视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系；

2.视图是由基本表(实表)产生的表(虚表)；

3.视图的建立和删除不影响基本表；

4.对视图内容的更新(添加、删除和修改)直接影响基本表；

5.当视图来自多个基本表时，不允许添加和删除数据。

--创建视图

create view 视图名 assql语句;create view v1 as select id,name fromuserinfo;--使用视图

select * from视图名;select * fromv1;--更新视图

alter view 视图名 assql语句;alter view v1 as select id,name from userinfo where id<10;--删除视图

drop view视图名;drop view v1;

二、触发器

触发器：监视某种情况，并触发某种操作；对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

--创建触发器语法

create trigger 触发器名 after/before insert/update/delete

on 表名 for each row --这句是固定的

begin

--begin和end中间放置需要执行的sql语句

endafter/before：只能选一个 , after(后置触发), before(前置触发)insert/update/delete：只能选一个

下面来介绍如何使用触发器

--商品表

create tablegoods_tb(

idint primary keyauto_increment,

namevarchar(20),

numint);--订单表

create tableorder_tb(

oidint primary keyauto_increment,

gidint,

gnumint);--添加3条商品数据

insert into goods_tb(name,num) values('商品1',10),('商品2',10),('商品3',10);

如果我们在没使用触发器之前，假设我们现在卖了3个商品1，则我们需要做两件事：

--1.往订单表插入一条记录

insert into order_tb(gid,gnum) values(1,3);--2.更新商品表商品1的剩余数量

update goods_tb set num=num-3 where id=1;

现在，我们来创建一个触发器：

create trigger tg1 after insert

on order_tb foreach rowbegin

update goods_tb set num=num-3 where id=1;end

这时候我们只要执行：

insert into order_tb(gid,gnum) values(1,3);

会发现商品1的数量变为7了，说明在我们插入一条订单的时候，触发器自动帮我们做了更新操作。

但现在会有一个问题，因为我们触发器里面num和id都是写死的，所以不管我们买哪个商品，最终更新的都是商品1的数量。比如：我们往订单表再插入一条记录：

insert into order_tb(gid,gnum) values(2,3);

执行完后会发现商品1的数量变4了，而商品2的数量没变，这显然不是我们想要的结果。我们需要改进之前创建的触发器。

我们需要知道在触发器中引用行的值，也就是说要得到新插入的订单记录中的gid或gnum的值。

对于insert而言，即将插入的数据行用new来表示，行中的每一列的值用new.字段名来表示。

所以可以这样来修改触发器：

create trigger tg2 after insert

on order_tb foreach rowbegin

update goods_tb set num=num-new.gnum where id=new.gid;end

第二个触发器创建完毕后，把第一个触发器删掉：

drop trigger tg1;

再来测试一下，插入一条订单记录：

insert into order_tb(gid,gnum) values(2,3);

执行完发现商品2的数量变为7了，现在就对了。

但是，现在还存在两个其他的问题：

#1.当用户撤销一个订单的时候，我们这边直接删除一个订单，我们是不是需要把对应的商品数量再加回去呢？

对于delete而言，即将删除的数据行用old来表示，原表本来有一行，但客户取消了交易(相当于删除了这一行数据)，如果想引用被删除的这一行，用 old.字段名可以引用删除的值。

那我们的触发器就该这样写：

客户取消订单，则触发器该这样设计：

create trigger tg3 after delete

on order_tb foreach rowbegin

update goods_tb set num=num+old.gnum where id=old.gid;end

假如客户取消了交易，那么就会执行下面这条sql语句：

delete from order_tb where id=1;

这时，我们再看看商品归还没有；

一看，客户的订单成功取消了，商品也归还了，非常好。

#2.当客户修改一个订单的数量时，我们触发器修改怎么写?

还是上面这个数据：假如客户觉得数量少了，需要改为订购5个商品1，则触发器该这样设计：

create trigger tg4 after update

on order_tb foreach rowbegin

update goods_tb set num=num+old.gnum-new.gnum where id=old.gid; --new表示即将插入的数据行，old表示即将删除的数据行。(这里num=7+3-5)

end

执行以下sql语句：

update order_tb set gnum=5 where id=1;

成功完成操作啦

三、存储过程

存储过程：存储过程是为了完成某个数据库中的特定功能而编写的语句集合；该语句集包括SQL语句(对数据的增删改查)、条件语句和循环语句等......当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

--看现有的存储过程

show procedurestatus;--创建存储过程(无参数示例)

create procedurep1()begin

select * fromt1;end

--调用存储过程

call 存储过程名称(参数类型参数名数据类型);--删除存储过程

drop procedure 存储过程名称;

有参数存储过程：

create procedure p2(in i int,inout io varchar(50))

begin

update goods_tb set name=io where id=i;

end

set @io="商品100"; #设置初始值

call p2(3,@io); #执行存储调用

select @io; #拿到结果

create procedure p3(in i int,out o varchar(50))

begin

select name into ofrom teacher where id =i;

end

set @name=null;

call p3(1,@name);

select @name;

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用

out 仅用于返回值用

inout 既可以传入又可以当作返回值

PS：into关键字可以将前面字段的查询结果执行给 into 后面的变量

create procedure p_in(innum int)

begin

select num;

set num=100;

select num;

end;

set @num=1;

call p_in(@num);

select @num;#总结: in 参数只是将变量在存储过程内部做了修改，并没有影响到外部，@num仍为1。

create procedure p_out(out num int)

begin

select num;

set num=100;

select num;

end;

set @num=1;

call p_out(@num);

select @num;#@num从1修改为了100

out

create procedure p_inout(inout num int)

begin

select num;

set num=100;

select num;

end;

set @num=1;

call p_inout(@num);

select @num;#@num从1修改为了100

inout

体会控制：

create procedure p4(in flag char(5),innum int)

beginif flag='true'then

select namefrom teacher where num>id;

elseif flag='false'then

select namefrom teacher where num

endif; #记得结束if

end

call p4("false",3)

体会循环：计算1-100累加的和，并且返回计算结果

create procedure p5(inout n int)begin

declare sum int default 0; --声明总和变量,并且指定初始值0

declare i int; --声明变量i

set i=0; --通过set,为变量设置值

while i<=n do --开始循环

set sum=sum +i;set i=i+1;end while; --结束循环

select sum; --提供结果

set n=sum; --将计算结果提供给输出变量n;

end;set @n=100;

call p5(@n);select @n;

存储过程的优缺点：

#存储过程优点：

1.存储过程增强了SQL语言灵活性

存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性2.减少网络流量，降低了网络负载

存储过程在服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行3.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译

一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度#存储过程缺点：

1.扩展功能不方便2.不便于系统后期维护

#!/usr/bin/env python#-*- coding:utf-8 -*-

importpymysql

conn= pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')

cursor= conn.cursor(cursor=pymysql.cursors.DictCursor)#执行存储过程

cursor.callproc('p1', args=(1, 22, 3, 4))#获取执行完存储的参数

cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")

result=cursor.fetchall()

conn.commit()

cursor.close()

conn.close()print(result)

pymysql执行存储过程

四、函数

MySQL中提供了许多内置函数

--一、数学函数

round(x,y)

返回参数x的四舍五入的有y位小数的值rand()

返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。----------------------------------------------------------------------------------二、聚合函数(常用于GROUP BY从句的SELECT查询中)

avg(col) 返回指定列的平均值count(col) 返回指定列中非NULL值的个数min(col) 返回指定列的最小值max(col) 返回指定列的最大值sum(col) 返回指定列的所有值之和

group_concat(col) 返回由属于一组的列值连接组合而成的结果----------------------------------------------------------------------------------三、字符串函数

char_length(str)

返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。

concat(str1,str2,...)

字符串拼接

如有任何一个参数为NULL ，则返回值为NULL。

concat_ws(separator,str1,str2,...)

字符串拼接(自定义连接符)

concat_ws()不会忽略任何空字符串。 (然而会忽略所有的NULL)。

format(X,D)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位，并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。

例如：select format(12332.1,4); 结果为： '12,332.1000'

insert(str,pos,len,newstr)

在str的指定位置插入字符串

pos：要替换位置其实位置len：替换的长度

newstr：新字符串

例如:select insert('abcd',1,2,'tt'); 结果为: 'ttcd'

select insert('abcd',1,4,'tt'); 结果为: 'tt'特别的：

如果pos超过原字符串长度，则返回原字符串

如果len超过原字符串长度，则由新字符串完全替换

insrt(str,substr)

返回字符串str中子字符串的第一个出现位置。left(str,len)

返回字符串str 从开始的len位置的子序列字符。

例如:select INSTR('abc','c'); 结果为： 3

select INSTR('abc','d'); 结果为： 0

lower(str)

变小写upper(str)

变大写reverse(str)

返回字符串str，顺序和字符顺序相反。

例如:select reverse('1234567') 结果为：7654321

substring(str,pos) , substring(str FROM pos) substring(str,pos,len) , substring(str FROM pos FOR len)

不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

mysql> select substring('Quadratically',5); --从第5位开始截取

-> 'ratically'mysql> select substring('foobarbar' FROM 4); --从第4位开始截取

-> 'barbar'mysql> select substring('Quadratically',5,6); --从第5位开始截取,截取6个长度

-> 'ratica'mysql> select substring('Sakila', -3); --从倒数第3位开始截取

-> 'ila'mysql> select substring('Sakila', -5, 3); --从倒数第5位开始截取,截取3个长度

-> 'aki'

----------------------------------------------------------------------------------四、日期和时间函数

curdate()或current_date() 返回当前的日期

curtime()或current_time() 返回当前的时间

dayofyear(date) 返回date是一年的第几天(1~366)

dayofmonth(date) 返回date是一个月的第几天(1~31)

dayofweek(date) 返回date所代表的一星期中的第几天(1~7)

dayname(date) 返回date的星期名，如：select DAYNAME(CURRENT_DATE);year(date) 返回日期date的年份(1000~9999)

quarter(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);month(date) 返回date的月份值(1~12)

monthname(date) 返回date的月份名，如：select MONTHNAME(CURRENT_DATE);

week(date) 返回日期date为一年中第几周(0~53)

hour(time) 返回time的小时值(0~23)

minute(time) 返回time的分钟值(0~59)

now() 返回当前的日期和时间

from_unixtime(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts--重点:

date_format(date,format) 根据format字符串格式化date值

mysql> select date_format('2009-10-04 22:23:00', '%W %M %Y');-> 'Sunday October 2009'mysql> select date_format('2007-10-04 22:23:00', '%H:%i:%s');-> '22:23:00'mysql> select date_format('1900-10-04 22:23:00',-> '%D %y %a %d %m %b %j');-> '4th 00 Thu 04 10 Oct 277'mysql> select date_format('1997-10-04 22:23:00',-> '%H %k %I %r %T %S %w');-> '22 22 10 10:23:00 PM 22:23:00 00 6'mysql> select date_format('1999-01-01', '%X %V');-> '1998 52'mysql> select date_format('2006-06-00', '%d');-> '00'

----------------------------------------------------------------------------------五、加密函数

md5()

计算字符串str的md5校验和

例如:select md5('1234') 结果为：81dc9bdb52d04dc20036dbd8313ed055

password(str)

返回字符串str的加密版本，这个加密过程是不可逆转的

例如:select password('1234') 结果为:*A4B6157319038724E3560894F7F932C8886EBFCF----------------------------------------------------------------------------------六、控制流函数

case when [test1] then [result1]... else [default] end如果testn是真，则返回resultn，否则返回defaultcase [test] when [val1] then [result1]... else [default] end如果test和val1相等，则返回result1，否则返回defaultif(test,t,f)

如果test是真，返回t；否则返回f

ifnull(arg1,arg2)

如果arg1不是空，返回arg1，否则返回arg2

例如:select ifnull('bbb','abc'); 结果为: bbbselect ifnull(null,'abc'); 结果为: abcnullif(arg1,arg2)

如果arg1=arg2返回null；否则返回arg1

例如:select nullif('bbb','bbb');结果为： null

select nullif('aaa','bbb');结果为： aaa

View Code

更多函数：官方链接

自定义函数

--创建自定义函数

create functionfunc1(

i1int,

i2int)returns int --设置返回类型

begin

declare num int; --声明num类型

set num=i1+i2;return(num);end

--调用自定义函数

select func1(1,5);

在sql语句中使用自定义函数

select func1(参数1,参数2),字段名 from 表名;

删除自定义函数

drop function 自定义函数名;

函数与存储过程的区别：

五、事务

1.什么是事务？

一组sql语句批量执行，要么全部执行成功，要么全部执行失败。

2.为什么要使用事务这个技术呢？

现在的很多软件都是多用户，多程序，多线程的，对同一个表可能同时有很多人在用，为保持数据的一致性，所以提出了事务的概念。

举个例子：A 给 B 转账，A 的账户 -1000元，B 的账户就要 +1000元，这两个update 语句必须作为一个整体来执行，不然 A 扣钱了，B 没有加钱这种情况很难处理。

3.事物的特性

原子性：对于其数据修改，要么全都执行，要么全都不执行；

一致性：数据库原来有什么样的约束，事务执行之后还需要存在这样的约束，所有规则都必须应用于事务的修改，以保持所有数据的完整性；

隔离性：一个事务不能知道另外一个事务的执行情况(中间状态)；

持久性：即使出现致命的系统故障也将一直保持。不要告诉我系统说commit成功了，回头电话告诉我，服务器机房断电了，我的事务涉及到的数据修改可能没有进入数据库。

PS:

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务；

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行；

事务用来管理 insert，update，delete 语句。

4.事务控制语句

begin或start transaction --开启一个事务

commit --提交事务，并使已对数据库进行的所有修改称为永久性的

rollback --回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

savepoint --保存点，可以把一个事物分割成几部分；在执行 rollback 时可以指定在什么位置上进行回滚操作

PS:set autocommit=0(禁止自动提交)set autocommit=1(开启自动提交)

5.例子

--创建表

create tableaccount(

idint(50) not null auto_increment primary key,

namevarchar(50) not null,money decimal(10,2) not null);--插入数据

insert into account(name,money) values("A",2000),("B",1000);--执行转账

start transaction; --开启事物

--执行sql语句操作

update account set money=money-500 where id=1;update account set money=money+500 where id=2;commit; --手动提交事物

select * from account; --查看结果

--保存点使用

start transaction;insert into account(name,money) values("C",1111);

savepoint sp1;--设置保存点

insert into account(name,money) values("D",2222);rollback to sp1; --事物回滚到保存点

commit;select * from account;

六、数据锁

需求:：有一个账户，两个人在同一时间要对此账户操作，A要对账户充值100块，B要从账户中取出100，操作前都要先看一下账户的余额然后再操作。

示例(手动模拟并发)，最终导致数据不一致。

--A进行充值(窗口1)

--充值前，先查看余额

set @m=0;select money into @m from account where name="XX账户";select @m;--看到余额后，充值100块

update account set money=@m+100 where name="XX账户";select * fromaccount;--B进行取款(窗口2)

--取款前，先查看余额

set @m=0;select money into @m from account where name="XX账户";select @m;--看到余额后，取款100块

update account set money=@m-100 where name="XX账户";select * from account;

1. 锁的基本概念

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2.锁的基本类型

多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。

因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

共享锁(Shared Lock，也叫S锁)共享锁表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。(如果试衣间的门还没被锁上，顾客都能够同时进去参观)

排他锁(Exclusive Lock，也叫X锁)排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间的门从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开)

3. 实际开发中常见的两种锁

悲观锁：顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block(阻塞)，当自己拿完数据的时候就能让别人操作了。传统的关系型数据库里边就用到了很多这种锁机制。

注意：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

设置完autocommit后，就可以执行正常业务了。

--A进行充值(窗口1)

start transaction; --开始事务1

--充值前，先查看余额

set @m=0;select money into @m from account where name="XX账户" for update;select @m;--看到余额后，充值100块

update account set money=@m+100 where name="XX账户";commit;select * fromaccount;--B进行取款(窗口2)

start transaction; --开始事务2

--取款前，先查看余额

set @m=0;select money into @m from account where name="XX账户" for update;select @m;--看到余额后，取款100块

update account set money=@m-100 where name="XX账户";commit;select * from account;

这次会发现B当前查询会进入到等待状态，不会显示出数据，当A的sql执行完毕提交事物后，B的sql才会显示结果。

注意1：在使用悲观锁时(一定要给表指定主键)，如果表中没有指定主键，则会进行锁表操作；

注意2:悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。

使用乐观锁的两种方式：

1.使用数据版本(Version)记录机制实现，这是乐观锁最常用的一种实现方式。

何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的"version"字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。