Mysql set smallest number in column (number will not small than zero)

Question

I have a column call token, it store user's credit.

This column can be add or minus and I want to set the smallest number to be zero.

Ex. 0 - 1 will store 0, (not -1)

Is any way to set smallest number to be zero in this column?

UPDATE 'table' SET token = token -1 (if token is 0, it will not small than 0)

Please provide table structure, sample data and desired results. As is, it's difficult to understand `0-1` should be `0`. — sgeddes, Oct 25 '14 at 22:37

Harsh Gupta · Accepted Answer · 2014-10-27T08:41:48.150

2

A simple solution would be to set the alter the column and set its type as unsigned integer with default value set to 0.

If it is not feasible and may cause unexpected issues, then you have to check and set the value appropriately before saving the data.

edited Oct 27 '14 at 08:41

answered Oct 25 '14 at 22:40

Harsh Gupta

4,348
2
25
30

I fail to see how this solves the problem. Try doing `select cast(0 as unsigned) - 1`. – Gordon Linoff Oct 26 '14 at 00:18
@GordonLinoff I didn't say to `cast` the value. What I meant was to alter the column definition. I will edit the post. – Harsh Gupta Oct 27 '14 at 08:40
. . Subtracting 1 from 0 is still a problem. In any case, the OP likes your answer, so I must be misinterpreting something. – Gordon Linoff Oct 27 '14 at 11:24

score 1 · Answer 2 · edited May 23 '17 at 12:27

Since it's MySQL; you can't use CHECK CONSTRAINT but you can active this constraint using a BEFORE INSERT trigger on the table like

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `table1`
FOR EACH ROW
BEGIN
    IF NEW.token < 0 THEN
        SIGNAL SQLSTATE '12345';
        SET MESSAGE_TEXT := 'check constraint on table1.Token failed';
    END IF;
END$$   
DELIMITER ;

Trigger idea taken from Mysql CHECK Constraint

If you want to change the existing Token value with < 0 then perform an UPDATE statement like

UPDATE table1 SET token = 0 where token < 0

score 0 · Answer 3 · edited May 23 '17 at 12:11

I'm borrowing from the answers to this question, but here is the general idea:

UPDATE my_table 
SET token=IF(old_token_value - x < 0, 0, old_token_value - x) 
WHERE key_value='some value';

This is all one command; I made it three lines so it could fit without scrolling.

What this will do is take the old_token_value of token, and subtract x. If it is less than 0, then set the value to 0. Otherwise, use the value of old_token_value - x, which should be positive.

Mysql set smallest number in column (number will not small than zero)

3 Answers3