转移“金钱"在 MySQL 中的 accountID 之间 [英] Transferring "Money" between accountID in MySQL

问题描述

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

I have a question which I have tried to Google but haven't find an answer to yet. What I am trying to do is transferring money between two accounts in MySQL with a stored procedure. If I for example use Call transfer (20,'Test',3,5). Then I will transfer 20 dollars from accountID 3 to accountID 5 and write the message "Test" , which is what it should do.

然而，目前可以在不存在的帐户之间发送资金，并且发送帐户 ID 的金额可以小于 0，例如呼叫转移 (20000000,'Test',34,54).

At the moment it is however possible to send money between non-existing accounts and the amount can be <0 for the sending accountID, for example call transfer (20000000,'Test',34,54).

有谁知道我如何解决这些问题(金额必须 >=0 并且只有现有的 accountID 可以接收和发送资金)?我正在使用的代码可以在下面找到:

Does anyone know how I can solve these problems(amount has to be >=0 and only existing accountIDs can receive and send money)? The code I am using can be found below:

创建表格:

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 ;

推荐答案

在程序开始时添加了金额的检查，并在 update 之前移动了 insert into transfers> 声明.transfers 表中有外键引用 account 表，所以如果你尝试插入不存在的 id 会立即失败.>

Added a check for the amount at the beginning of the procedure, and moved insert into transfers before update statements. There are foreign keys in transfers table referencing account table, so if you try to insert id that does not exist it will fail immediately.

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 ;

这篇关于转移“金钱"在 MySQL 中的 accountID 之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！