mysql - MySQL中accountID之间转 "Money"

Question

我有一个问题，已尝试通过 Google 搜索但尚未找到答案。我想做的是使用存储过程在 MySQL 中的两个帐户之间转账。例如，如果我使用调用转移 (20,'Test',3,5)。然后我会从 accountID 3 转 20 美元到 accountID 5 并写消息 "Test"，这是它应该做的。

不过，目前可以在不存在的账户之间汇款，并且发送账户 ID 的金额可以小于 0，例如调用转账 (20000000,'Test',34,54)。

有谁知道我该如何解决这些问题(金额必须 >=0 并且只有现有的 accountID 才能收款和汇款)？我正在使用的代码可以在下面找到:

创建表格:

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 ;

Answer 1

在过程的开头添加了金额检查，并将 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 ;