Why Does a Large Integer Value Transform into 2147483647 When Inserted into a MySQL INT Column?

Understanding Integer Truncation Discrepancy

Consider a situation where you have an integer column with a length of 10, as follows:

<code class="sql">`some_number` int(10) unsigned NOT NULL</code>

When inserting a value larger than the column's allowed range:

<code class="php">$some_number = 715988985123857;
$query = "INSERT INTO this_table SET some_number = ?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param('i', $some_number);
$stmt->execute();</code>

You might expect truncation to occur, resulting in a shorter value. However, surprisingly, the value in the table transforms into 2147483647.

Mechanism Behind the Transformation

According to MySQL documentation, integer overflow for types int and integer will result in the maximum allowed number, which in this case is 2147483647. This behavior is not truncation but rather a modulo operation, which takes the remainder of the original value divided by the maximum allowed number.

Formula for Calculation

To calculate the result of integer overflow, use the following formula:

result = original_value % maximum_allowed_value

In this case:

result = 715988985123857 % 2147483647 = 2147483647

Therefore, the large value of 715988985123857 becomes 2147483647 in the database due to integer overflow and the modulo operation.

The above is the detailed content of Why Does a Large Integer Value Transform into 2147483647 When Inserted into a MySQL INT Column?. For more information, please follow other related articles on the PHP Chinese website!