Mysql prevents negative numbers

Question

Is their int field in mysql where negative numbers are not allowed? or more specifically, if a negative number is inserted into the field, it will insert zero. I ask about this because we have a scoring system and we do not allow people to have negative ratings. Therefore, if their count reaches zero, he simply inserts zero. I try to do this without asking for a user account to see if it falls below zero.

Answer 1

In addition to the DDL (INT UNSIGNED) change recommended by others, I would also change the logic of your application. You speak:

I try to do this without asking for a user account to see if it falls below zero.

You do not need to explicitly check a single request:

UPDATE your_table SET score = GREATEST(score + ?, 0) -- This '?' is the adjustment to the score WHERE user_id = ? 

Now your application cannot UPDATE score fall below zero and will not generate errors or warnings depending on the SQL mode.

Answer 2

Yes. You can create an int field and mark it as UNSIGNED .

From the MySQL 5.0 Reference Guide :

 INT[(M)] [UNSIGNED] [ZEROFILL] A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295. 

Answer 3

MySQL has an UNSIGNED qualifier for integer types.

Negative values ​​will be clamped to zero, but generate a warning:

 mysql> create table test ( id int(5) unsigned not null ); Query OK, 0 rows affected (0.05 sec) mysql> insert into test values (-1), (5), (10); Query OK, 3 rows affected, 1 warning (0.01 sec) Records: 3 Duplicates: 0 Warnings: 1 mysql> select * from test; +----+ | id | +----+ | 0 | | 5 | | 10 | +----+ 3 rows in set (0.01 sec) 

Answer 4

If you are working in sql strict mode, this will throw an error and the insert / update will fail.

I usually create a custom function for this kind of thing. (In this case, the very trivial "if (expr1, expr2, expr3)" will do the trick