mysql 怎么负数变整数_防止值在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;"

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.

This is all on MySQL 5.0

解决方案

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 :) ).

See documenation of MySQL 5.0 for details how to create a trigger.

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.

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;

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)