MySQL Integer Data overflow Solution

MySQL Integer Data overflow Solution

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:

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, remember that mysql's data processing will be converted to bigint processing, so here we will use several bigint tests:

Copy codeThe Code is as follows:
Select cast (0 as unsigned)-1;

SELECT 9223372036854775807 + 1;

MySQL 5.1:
Copy codeThe Code is as follows:
Mysql> select cast (0 as unsigned)-1;
+ ------------------------- +
| CAST (0 as unsigned)-1 |
+ ------------------------- +
| 1, 18446744073709551615 |
+ ------------------------- +
1 row in set (0.01 sec)

Mysql> SELECT 9223372036854775807 + 1;
+ ------------------------- +
| 9223372036854775807 + 1 |
+ ------------------------- +
|-1, 9223372036854775808 |
+ ------------------------- +
1 row in set (0.01 sec)

MySQL 5.5, 5.6, 5.7:
Copy codeThe Code is 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 )'

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.

For more details, refer to: http://dev.mysql.com/doc/refman/5.7/en/out-of-range-and-overflow.html

How to solve the problem of Mysql field data Overflow

I don't know if you are an oracle or SQL server? This is a self-join problem. First, we need to sort, mark the row number, and then compare it with the upper and lower rows of the table, so it is a self-join.
Orcale:
Select a. vseq, a. declaredate as declaredate1, B. declaredate as declaredate2
From
(Select vseq, declaredate, rownum as row from mac505 order by vseq, declaredate),
(Select vseq, declaredate, rownum as row from mac505 order by vseq, declaredate) B
Where a. vseq = B. vseq and a. row + 1 = B. row and a. declaredate plus 1000 <B. declaredate

SQL server only has row_number () function in Versions later than 2005. Therefore, the following script can only be used in Versions later than 2005.
Select a. vseq, a. declaredate as declaredate1, B. declaredate as declaredate2
From
(Select vseq, declaredate, row_number () orver (order by vseq, declaredate) as row from mac505),
(Select vseq, declaredate, row_number () orver (order by vseq, declaredate) as row from mac505) B
Where a. vseq = B. vseq and a. row + 1 = B. row and a. declaredate plus 1000 <B. declaredate

Question about Integer Data overflow ???

100000000000000 indicates a negative number, so the answer must be reduced by 1 and then reversed.
011111111111111 //-1
100000000000000 // reverse
Finally, convert the number to decimal and add a negative number.