Solution to MySQL integer data overflow

Today, I received a phone call from a friend saying that someone had changed the database and the data was incorrect. After a long search, the data type overflowed (the version in question was MySQL5.1). Later, I solved this problem by upgrading MySQL5.1 to MySQL5.5 for friends. It also made me interested to see how different versions of MySQL deal with data type overflow.

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

Also keep in mind that mysql's data processing will be converted to bigint processing, so here are some tests using bigint:

SELECT CAST(0 AS UNSIGNED) - 1;
SELECT 9223372036854775807 + 1;

MySQL under 5.1:

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

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)'

If you are dealing with such data as 1, you must be careful of overflow (if you have cheated in the early days, you will use this method to deal with q-coins).

This problem may occur in the point message, the sum of points, or in a money-related business. The master library 5.1 and slave library MySQL5.5 may also be out of sync.
Recommendation: Upgrade to MySQL 5.5 if possible

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