在mysql中,使用存储过程来实现购物,需要用到事务操作,start transaction ;
当user的money不足以支付商品时,应当进行回滚。获取异常用 declare continue handler for SQLEXCEPTION set e=1;
-- 产品表
create table productn(
pid int key auto_increment,
num int unsigned,
price float(7,2) default 0.00,
title char(50)
)//
insert into productn(pid,num,price,title) value(1,5,50.00,'宝强牌帽子')//
-- 用户表
create table usern(
uid int key auto_increment,
username char(20) unique not null,
password char(32) not null,
money float(10,2) unsigned default 0.00
)//
insert into usern(uid,username,password,money) value(1,'xiaosong','123123',80.00)//
create procedure buy(in pidn int,in uidn int,in numn int)
begin
declare jiage float(7,2) default 0.00;
declare zongjia float(9,2) default 0.00;
declare e tinyint(1) default 0;
declare continue handler for SQLEXCEPTION set e=1;
-- 获取价格
select price into jiage from productn where pid=pidn;
-- 算出总价
set zongjia=jiage*numn;
-- 开启事务
start transaction;
-- 扣款
update usern set money=money-zongjia where uid=uidn;
-- 出库
update productn set num=num-numn where pid=pidn;
-- 判断是否有异常
if e=1 then
rollback;
select 0 as re;
else
commit;
select 1 as re;
end if;
end//
!!!!!!!
然而在实际调用过程中, call buy(1,1,2)// 判断异常并没有起作用,购物操作仍然执行,用户的money被置为0。
这样就需要更改mysql的配置文件 my.ini 中的配置项 sql-mode
在phpstudy中该项只包含两个值 :sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
最好把该项置成: sql-mode="ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT_,ANSI_QUOTES"