插入运行总数

Question

在MySQL事务性数据库中插入运行总计存在问题。需要你的帮助解决方案和意见。我桌子的结构是，

create table `wtacct` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ACCOUNT_NO` varchar(16),
  `AMOUNT` float(16,2),
  `BALANCE` float(16,2)
);

请注意，其他字段已被移除，以使其成为简单的示例。

我在做交易，

• 10美元来自帐户1001和
• 10克朗，2002年账户

插入查询

INSERT INTO wtacct (ID, ACCOUNT_NO, AMOUNT, BALANCE) 
VALUES ('', 1001, -10, 100), ('', 2002, 10, 5000);

我想要余额，

第1001号帐户余额=帐户1001-10的最后交易余额。

我的解决方案和局限性

解决方案1

在insert语句中，将子查询放在balance字段中：

select balance from wtacct where account_no=1001 and id in(select max(id) from wtacct where account_no=1001)

限制: Mysql不支持插入数据(wtacct)的表选择查询(wtacct)。

解决方案2

使用insert into select语句

insert into wtacct select '' ID, 1001 ACCOUNT_NO, -10 AMOUNT, (BALANCE-10) BALANCE where account_no=1001 and id in(select max(id) from wtacct where account_no=1001)

限制：用于第一个事务帐户1001在wtacct中没有记录，所以select查询不会返回第一个事务的任何记录。

解决方案3

取变量中的平衡，并在insert语句中使用它。

select @balance1001 :=balance from wtacct
  where account_no=1001 and id in(select max(id) from wtacct where account_no=1001)

select @balance2002 :=balance from wtacct
  where account_no=2002 and id in(select max(id) from wtacct where account_no=2002)

INSERT INTO wtacct (ID, ACCOUNT_NO, AMOUNT, BALANCE) 
VALUES ('', 1001, -10, @balance1001-10), ('', 2002, 10, @balance2002+10);

限制：有机会改变select和insert查询执行之间的时间平衡。此外，它昂贵的，3查询执行所需。

解决方案4

插入并更新余额

INSERT INTO wtacct (ID, ACCOUNT_NO, AMOUNT, BALANCE) 
VALUES ('', 1001, -10, 0);

UPDATE wtacct set balance = (ifnull(Select balance from wtacct where account_no=1001 and id in(select max(id) from wtacct where id <last_insert_id() and account_no=1001),0) -10) 
  where id =last_insert_id() and account_no=1001

........

限制：查询成本很高。它需要4(两次插入和2次更新)查询执行。注last_insert_id()是php函数

解决方案5

在insert语句上使用触发器。在触发器中，将更新余额，计算最后的事务值和插入金额。

限制：触发器不支持事务行为，可能失败。

请给出您的解决方案和对上述解决方案的意见。请注意，在上面的例子中，它们可能是一些语法错误/错误。请别理他们。

Answer 1

我没有看到列出的一个很大的限制是潜在的竞赛条件，其中两行同时插入到表中。这两个插入都有可能从前一行获得当前的“余额”。

一个问题:您是否也有一个单独的“当前余额”表，为每个帐户保存当前“余额”的单个值？还是您只依赖上一次交易中的“余额”。

就我个人而言，我会在一个单独的“帐户余额”表上跟踪当前的余额。我将在INSERT/UPDATE触发器之前使用它来维护该行中的值，并使用它返回帐户的当前余额。

例如，我定义了这样一个触发器，当将一行插入到wtacct表中时会触发该触发器：

CREATE TRIGGER wtacct_bi
BEFORE INSERT ON wtacct
FOR EACH ROW
BEGIN
  IF NEW.amount IS NULL THEN
     SET NEW.amount = 0;
  END IF
  ;
  UPDATE acct a
     SET a.balance = (@new_balance := a.balance + NEW.amount)
   WHERE a.account_no = NEW.account_no
  ; 
  SET NEW.balance = @new_balance
  ;
END$$

扳机的设置..。

CREATE TABLE acct 
( account_no  VARCHAR(16) NOT NULL PRIMARY KEY
, balance     DECIMAL(20,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB
;

CREATE TABLE wtacct 
( id          BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
, account_no  VARCHAR(16) NOT NULL COMMENT 'FK ref acct.account_no'
, amount      DECIMAL(20,2) NOT NULL
, balance     DECIMAL(20,2) NOT NULL 
, FOREIGN KEY FK_wtacct_acct (account_no) REFERENCES acct (account_no)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB
;

我使用单独的“当前余额”表的原因是，给定的account_no只有一行，而该行保留了帐户的当前余额。

触发器中的UPDATE语句应该获得要更新的行的独占锁。该排他锁阻止任何其他UPDATE语句同时更新同一行。UPDATE语句的执行将将插入的当前事务行中的amount添加到当前余额中。

如果我们使用的是甲骨文或PostgreSQL，我们可以使用一个返回子句来获取分配给\'balance\‘列的值。

在MySQL中，我们可以使用用户定义的变量来解决这个问题。我们要分配给该列的新值首先分配给user_defined变量，然后分配给该列。

我们可以将用户定义变量的值赋值给插入到wtacct中的行的wtacct列。

此方法的目的是在一条语句中检索和更新当前余额，以避免任何竞赛条件。

UPDATE语句定位该行，获取该行上的排他(X)锁，检索当前余额(从\' balance \‘列中检索值)，计算新的当前余额，并将其分配回\' balance \’列。然后继续保持锁，直到事务完成。

触发器完成后，INSERT语句(最初触发触发器)继续进行，试图将新行插入wtacct。如果失败，那么INSERT语句所做的所有更改和触发器的执行都会回滚，从而保持所有的一致性。

一旦会话发出提交或回滚，就会释放acct中行上的排他(X)锁，其他会话可以在acct中获得该行上的锁。

Answer 2

我已经为MySql使用了存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `example_add`(IN dr Int, IN cr Int)
BEGIN

DECLARE LID int;
Declare Balance decimal(16,2);

INSERT INTO example (Debit,Credit)
VALUES (dr, cr);

SET LID = LAST_INSERT_ID();

SET Balance = (select SUM(Debit) - SUM(Credit) as Balance from example);

UPDATE Example SET  Balance = Balance WHERE ID = LID;

END

使用example_add(10,0)或example_add(0,15)，然后选择并查看结果。