防止mysql中的值变为负数的最佳方法

我们有一个表，通过在该表中记录交易来维护帐户余额。即最近一行是帐户余额。

在记录提款时，我们希望确保余额​​永远不会出现负数。我们提出的解决方案类似于：

INSERT INTO `txns`
  (`account_id`, `prev_balance`, `txn_type`, `new_balance`,  `amount`, `description`)
SELECT 
  t.account_id, t.new_balance, $txn_type, t.new_balance - $amount, $amount, $description
FROM`txns` t
WHERE t.account_id = '$account'
  AND (select new_balance 
        FROM txns 
        WHERE account_id = '$account' 
        ORDER BY txn_id desc limit 1) >= $amount
ORDER BY txn_id desc LIMIT 1;"

但是我们有点担心 AND 子查询的性能（我们在之前的项目中遇到了子查询性能问题）。这里的开发人员都不是 sql 专家。存款没有附加条款。

这一切都在 MySQL 5.0 上

抱歉，我无法对查询的性能发表任何评论。但您可能需要考虑触发器来防止“new_balance”变为负数。 （因为在“new_balance”低于 $amount 的情况下进行空插入让我觉得很奇怪，但它仍然可能有效:)）。

See MySQL 5.0 的文档 http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html有关如何创建触发器的详细信息。

基本上，如果 NEW.new_balance 为负，您会将检查放入 BEFORE 触发器中。如果是，那么您将使用“STOP ACTION”（执行中的故意错误）来中止触发器和 INSERT 查询。请参阅评论中提到的页面上的想法。

更新：做了一些修改（我在家安装 MySQL 的借口）。

我的版本存在将每个输入到 Moneylog 的值第二次写入数据库的问题。

也许切换到存储过程是明智的。或者其他人有更好的想法，我不太喜欢数据库:)

CREATE DATABASE triggertest;
CONNECT triggertest;

CREATE TABLE transferlog (
  account SMALLINT UNSIGNED NOT NULL ,
  amount INT NOT NULL,
  new_balance INT NOT NULL
) ENGINE=INNODB;

CREATE TABLE stopaction (
  entry CHAR(20) NOT NULL,
  dummy SMALLINT,
  UNIQUE(`entry`)
);

INSERT INTO stopaction (`entry`) VALUES ('stop');

DELIMITER #
CREATE TRIGGER nonneg_insert BEFORE INSERT ON transferlog
  FOR EACH ROW BEGIN
    INSERT INTO stopaction (`entry`)
      SELECT CASE WHEN NEW.new_balance<0 THEN 'stop'
                  ELSE 'none' END;
    DELETE FROM stopaction WHERE entry!='stop';
  END;
#

CREATE TRIGGER nonneg_update BEFORE UPDATE ON transferlog
  FOR EACH ROW BEGIN
    INSERT INTO stopaction (`entry`)
      SELECT CASE WHEN NEW.new_balance<0 THEN 'stop'
                  ELSE 'none' END;
    DELETE FROM stopaction WHERE entry!='stop';
  END;
#
DELIMITER ;


INSERT INTO transferlog (`account`, `amount`, `new_balance`)  
  VALUES (1, 1000, 1000);
INSERT INTO transferlog (`account`, `amount`, `new_balance`)
  VALUES (1, -1000, 0);
INSERT INTO transferlog (`account`, `amount`, `new_balance`)
  VALUES (1, -1000, -1000);
INSERT INTO transferlog (`account`, `amount`, `new_balance`)
  VALUES (1, 10, 20);
SELECT version();

DROP DATABASE triggertest;

也许它会适合你，我的 INSERT-Lines 输出是：

mysql> INSERT INTO transferlog (`account`, `amount`, `new_balance`)  VALUES (1, 1000, 1000);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO transferlog (`account`, `amount`, `new_balance`)  VALUES (1, -1000, 0);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO transferlog (`account`, `amount`, `new_balance`)  VALUES (1, -1000, -1000);
ERROR 1062 (23000): Duplicate entry 'stop' for key 1

mysql> INSERT INTO transferlog (`account`, `amount`, `new_balance`)  VALUES (1, 10, 20);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT version();
+---------------------+
| version()           |
+---------------------+
| 5.0.67-community-nt |
+---------------------+
1 row in set (0.00 sec)