MySQL unsigned int type data use some things

Take signed as an example, you can store -2147483648 to 2147483647 and take Unsigned as an example, you can save 0 to 4294967295, but in the operation of such fields, you must be careful to avoid the deletion of the overdo, but become full of blood fill.

Normally, MySQL can do this when the number of operations is reduced

Pgsql

The code is as follows

Copy Code

--Transaction start-- SELECT ' example ' from ' test ' WHERE ID =: ID --Use the program to calculate the new value of example, and then transfer back UPDATE ' test ' SET ' example ' = n WHERE id =: ID; --Commit Transaction --Transaction Start--select ' example ' from ' test ' WHERE ID =: ID --Use the program to calculate the new value of example, and then transfer back UPDATE ' test ' SET ' example ' = n WHERE id =: ID; --Commit Transaction

But smarter people would probably do that to reduce Query times (assuming a minus 1)

Pgsql

The code is as follows	Copy Code
UPDATE ' test ' SET ' example ' = ' example '-1; UPDATE ' test ' SET ' example ' = ' example '-1;

However, the above line of SQL Statement has a risk. Since there is no boundary check, when example = 0, a further reduction of 1 will become 4294967295.

The following figure is an example:

This is my own screenshot of the page, the figure of the number of Plurk to 4,294,967,294, is more than 4.2 billion pens, suppose I send a pen out a second, in my lifetime will not be stamped (1 * 86400 * 365 * 100 = 3153600000, 3.5 billion). Page links

Therefore, when this field is recorded "Amount" or "point", it will have a great impact on the data (as long as the program Transaction/work flow is not well controlled, the poor can also get rich overnight).

Although the problem is very serious, but the improvement method is also very simple, as long as the WHERE conditions limit, you can immediately avoid this problem.

Pgsql

The code is as follows	Copy Code
UPDATE ' test ' SET ' example ' = ' example '-' 1 ' WHERE ' example ' > 0; UPDATE ' test ' SET ' example ' = ' example '-' 1 ' WHERE ' example ' > 0;

The question has been returned to the authorities, and I wonder when the BUG will be corrected.