Long Integer Transformation in Shorter Column: Mechanism and Formula

When inserting a long integer into a shorter integer column, MySQL typically truncates the value to fit within the specified length. However, in some cases, the behavior may differ, resulting in an unexpected transformation.

Consider a 10-digit long integer column some_number. If a value exceeding the maximum integer range (2147483647) is inserted into this column, instead of truncation, MySQL will set the value to 2147483647, the maximum allowed integer for that data type.

The Mechanism Behind Transformation

The transformation occurs due to integer overflow. When the long integer exceeds the available range, MySQL automatically interprets it as a negative integer and stores the two's complement representation. This negative value, when converted back to an unsigned integer, results in 2147483647.

Formula for Transformation

The resulting value can be calculated using the following formula:

Resulting Integer = (Original Integer & 0x7FFFFFFF)   1

For the given example, with an original integer of 715988985123857:

Resulting Integer = (715988985123857 & 0x7FFFFFFF)   1
Resulting Integer = (2147483647)   1
Resulting Integer = 2147483647

Therefore, the transformation from 715988985123857 to 2147483647 is not a truncation but rather an overflow to the maximum allowed integer for the specified data type.