防止值在MySQL中变为负数的最佳方法 [英] Best way to prevent a value from going negative in mysql
问题描述
我们有一个表,该表通过在该表中记录交易来维护帐户余额.即最近的一行是帐户余额.
We have a table that maintains account balances by recording transactions in that table. i.e. the most recent row is the account balance.
记录提款时,我们要确保余额永远不会为负.我们建议的解决方案是这样的:
When recording a withdrawal, we would like to ensure that the balance can never go negative. Our proposed solution is something like:
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;"
但是,如果使用ANDed子查询(我们在先前的项目中遇到子查询性能问题),我们会有点担心性能.这里的开发人员都不是sql专家.存款没有附加条款.
But we are a bit concerned about the performance if the ANDed subquery (we had subquery performance issues on a previous project). None of the developers here are sql specialists. Deposits do not have the additional clause.
这一切都在MySQL 5.0上
This is all on MySQL 5.0
推荐答案
抱歉,我无法说出有关查询性能的任何信息.但是您可能希望考虑使用触发器来防止'new_balance'的情况变为负面. (因为在'new_balance'低于$ amount的情况下执行null插入使我感到很奇怪,但这仍然可行:)).
I cannot say anything about the performance of the query, sorry. But you might want to consider triggers to prevent the case of the 'new_balance' ever becoming negative. (Because it strikes me as odd to do a null-insert in case the 'new_balance' is lower than $amount, but it might work nevertheless :) ).
有关详细信息,请参见 MySQL 5.0的说明如何创建触发器.
See documenation of MySQL 5.0 for details how to create a trigger.
如果NEW.new_balance是负数,则基本上将其放入BEFORE触发器中.如果是,那么您将使用"STOP ACTION"(执行中的故意错误)中止触发器和INSERT查询.在评论中查看提及页面上的想法.
Basically you would put the check, if NEW.new_balance ís negative into a BEFORE-trigger. If yes, then you would use a "STOP ACTION", a deliberate error in execution, to abort the trigger and INSERT-query. See ideas on the mentioned page in the comments.
更新:修改了一下(我在家里安装MySQL的借口).
我的版本存在一个问题,即对于输入到moneylog中的每个值,都需要第二次写入数据库.
也许建议切换到存储过程.或者其他人有一个更好的主意,我对DB的兴趣不大:)
Update: Tinkered a little bit around (my excuse for installing MySQL at home).
My version has the problem of writing a second time to the DB for each value entered into moneylog.
Maybe switching to a stored proc would be advisable. Or somebody else has a better idea, I'm not that much into DB :)
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的输出是:
Maybe it will suit you, my output for the INSERT-Lines is:
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)
这篇关于防止值在MySQL中变为负数的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!