How to deal with the memory overflow problem of MySQL integer data, mysql integer

How to deal with the memory overflow problem of MySQL integer data, mysql integer

Today, I received a call from a friend saying that the database was changed by someone else and the data was incorrect. After a long time of troubleshooting, the Data Type overflows (the problematic version is MySQL 5.1 ). Later, I upgraded MySQL 5.1 to MySQL 5.5 to solve this problem. This also makes me interested in learning about the processing mechanism of data overflow in different MySQL versions.

Let's take a look at the integer data and storage space supported by MySQL:

In addition, remember that mysql's data processing will be converted to bigint processing, so here we will use several bigint tests:

SELECTCAST(0ASUNSIGNED)-1; SELECT9223372036854775807+1;

MySQL 5.1:

mysql>SELECTCAST(0ASUNSIGNED)-1;+-------------------------+|CAST(0ASUNSIGNED)-1|+-------------------------+|18446744073709551615|+-------------------------+1ROWINSET(0.01 sec) mysql>SELECT9223372036854775807+1;+-------------------------+|9223372036854775807+1|+-------------------------+|-9223372036854775808|+-------------------------+1ROWINSET(0.01 sec)

MySQL 5.5, 5.6, 5.7:

mysql>SELECTCAST(0ASUNSIGNED)-1;ERROR 1690(22003): BIGINTUNSIGNEDVALUEISOUTOF range IN'(cast(0 as unsigned) - 1)'mysql> mysql> mysql> mysql>SELECT9223372036854775807+1;ERROR 1690(22003): BIGINTVALUEISOUTOF range IN'(9223372036854775807 + 1)'

When processing this type of data, you must be careful with the overflow. (For example, you can use this method to handle the problem in the early days)

This problem may result in point message, point addition, or some money-related businesses. The master database is 5.1, And the slave database MySQL is 5.5.
Suggestion: upgrade this type of business system to MySQL 5.5 or later.