我有一个尝试过向Google提出的问题，但尚未找到答案。我想做的是使用存储过程在MySQL中的两个帐户之间转移资金。例如，如果我使用呼叫转移（20，“测试”，3、5）。然后，我将20美元从accountID 3转移到accountID 5并输入消息“ Test”，这是它应该执行的操作。
但是，目前可以在不存在的帐户之间进行转帐，并且发送帐户ID的金额可以小于0，例如呼叫转移（20000000，“测试”，34，54）。
有谁知道我该如何解决这些问题（金额必须> = 0，并且只有现有的帐户ID才能接收和汇款）？我正在使用的代码可以在下面找到：
创建表：
Create table Account
(
AccountID int AUTO_INCREMENT primary key
,amount decimal check (amount >= 0));
Create table Transfers
(
TransfersID int AUTO_INCREMENT primary key
,amount decimal
,from_account_id int not null
,to_account_id int not null
,note varchar(50) not null
,datetime datetime not null
,Constraint from_account_key foreign key (from_account_id) references
Accounts (id)
,Constraint to_account_key foreign key (to_account_id) references Accounts
(id)
);
insert into accounts (id, amount) values (1, (rand()*100));
insert into accounts (id, amount) values (2, (rand()*100));
创建存储过程：
delimiter //
create procedure transfer (amount int, note varchar(50), sending_account
int, receiving_account int)
begin
start transaction;
update accounts as A
set A.amount = A.amount - amount
where A.AccountID = sending_account;
update accounts as A
set A.amount = A.amount + amount
where A.AccountID = receiving_account;
insert into transfers values
(TransfersID, amount, sending_account, receiving_account, note, now());
commit work;
end //
delimiter ;
最佳答案
在过程开始时添加了金额检查，并将insert into transfers移到了update语句之前。 transfers表中有外键引用account表，因此，如果您尝试插入不存在的id，它将立即失败。
delimiter //
create procedure transfer (amount int, note varchar(50), sending_account
int, receiving_account int)
this_proc:begin
start transaction;
if amount <= 0 then
leave this_proc;
end if;
insert into Transfers values
(TransfersID, amount, sending_account, receiving_account, note, now());
update Account as A
set A.amount = A.amount - amount
where A.AccountID = sending_account;
update Account as A
set A.amount = A.amount + amount
where A.AccountID = receiving_account;
commit work;
end //
delimiter ;