A solution for Mysql integer data overflow _mysql

Today, I received a call from a friend who said that the database was changed by others, and the problem of incorrect data appeared. After a long time, the data type has overflowed (the version of the problem is MySQL 5.1).　Later, through the friend side of the MySQL 5.1 to upgrade to MySQL 5.5 to solve this problem. It also gives me an interest in understanding MySQL's handling mechanism for different versions of data type overflow.

First look at the number of integers supported by MySQL and size, storage space:

PE	Storage	Minimum Value	Maximum Value	Storage size
	(Bytes)	(signed/unsigned)	(signed/unsigned)	byte
TINYINT	1	-128	127	1 byte
		0	255
SMALLINT	2	-32768	32767	2 bytes
		0	65535
Mediumint	3	-8388608	8388607	3 bytes
		0	16777215
Int	4	-2147483648	2147483647	4 bytes
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	8 bytes
		0	18446744073709551615

In addition, please remember that MySQL data processing will be turned into bigint processing, so here is a few bigint test:

Copy Code code as follows:

SELECT CAST (0 as UNSIGNED)-1;

SELECT 9223372036854775807 + 1;

MySQL 5.1 under:

Copy Code code as follows:

mysql> SELECT CAST (0 as UNSIGNED)-1;
+-------------------------+
| CAST (0 as UNSIGNED)-1 |
+-------------------------+
| 18446744073709551615 |
+-------------------------+
1 row in Set (0.01 sec)

mysql> SELECT 9223372036854775807 + 1;
+-------------------------+
| 9223372036854775807 + 1 |
+-------------------------+
| -9223372036854775808 |
+-------------------------+
1 row in Set (0.01 sec)

MySQL 5.5, 5.6, 5.7 under:

Copy Code code as follows:

mysql> SELECT CAST (0 as UNSIGNED)-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ' (CAST (0 as UNSIGNED)-1) '
Mysql>
Mysql>
Mysql>
mysql> SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in ' (9223372036854775807 + 1) '

Where the processing of such data is to be careful to overflow (such as early have to do the harm of the Q currency is to use this method to deal with)

This problem is likely to appear in the integration message, integral addition, or some money related to the business, the main library 5.1, from the library MySQL 5.5 situation will also be a problem of synchronization.
Recommendation: This kind of business system as far as possible upgrade to MySQL 5.5 version

More details for reference: http://dev.mysql.com/doc/refman/5.7/en/out-of-range-and-overflow.html