Incorrect Integer (2147483647) is inserted into MySQL?

Incorrect Integer (2147483647) is inserted into MySQL?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

The Silent Data Loss: Why 2147483647 Appears in Your MySQL Table

You are likely encountering a classic PHP type coercion issue when interacting with large integers. The specific value 2147483647 is the maximum limit of a signed 32-bit integer (INT_MAX). When your code inserts a value larger than this threshold, and you subsequently see exactly 2147483647 in your database instead of the expected Steam ID, the data has been silently truncated before it even reaches MySQL.

The root cause lies in how PHP handles integers internally versus how MySQL defines column types. In a standard 32-bit environment, PHP treats integers using a signed 32-bit representation. If your $steam64 variable holds a value exceeding 2147483647, the language itself cannot represent the full magnitude of that number in its integer type. However, if you have not explicitly handled this conversion, PHP might be silently converting large numeric strings to their maximum valid integer representation, or you are inadvertently casting a string to an int using functions like int() or (int) which cap at PHP_INT_MAX on 32-bit systems (often manifesting as 2147483647 if the architecture is strictly 32-bit and the variable was treated loosely).

The code snippet you provided illustrates a common pattern that exacerbates this issue:

$sql_query = "INSERT INTO users_info (steam64) VALUES ('$steam64')";

This approach relies on string interpolation. While this looks simple, it introduces two distinct failure modes depending on the data type of $steam64. If $steam64 is a numeric string representing a Steam ID larger than 32-bit, PHP treats it as a string. However, if your logic anywhere prior involved casting that variable to an integer—perhaps thinking "it's just a number"—you hit the ceiling immediately.

When you run var_dump($steam64), you see the value. This is deceptive because var_dump displays the content of the variable regardless of whether PHP has already truncated it in memory or if it is currently stored as a string that looks like an integer. If you echo $steam64, and it outputs 2147483647 despite being larger, the truncation happened during the assignment or a prior operation.

The Solution: Use Unsigned Types or Strings

To prevent this data loss, you must align your PHP variable type with the capabilities of your database column. Steam IDs are essentially unique identifiers that can easily exceed the range of a 32-bit integer.

Option 1: Switch to BIGINT in MySQL
The most robust solution is to ensure your database schema supports 64-bit integers. If your table currently defines the steam64 column as INT, you must alter it to BIGINT. A standard INT in MySQL typically maps to a signed 32-bit integer (range -2,147,483,648 to 2,147,483,647).

Run this migration command:

ALTER TABLE users_info MODIFY COLUMN steam64 BIGINT UNSIGNED;

By using BIGINT UNSIGNED, you expand the range to approximately 1.8 × 10^19, which comfortably fits all Steam IDs without truncation. This allows PHP's integer type (which is platform-dependent but usually 64-bit on modern servers) to pass the full value to MySQL safely.

Option 2: Treat IDs as Strings
If altering the schema is not immediately feasible or if you prefer a schema-agnostic approach, store Steam IDs as VARCHAR. This removes all ambiguity regarding integer size limits. You must ensure that $steam64 is passed as a string in your SQL query and that your PHP code does not attempt to cast it to an int during insertion.

// Ensure the variable is treated strictly as a string or already is one
$sql_query = "INSERT INTO users_info (steam64) VALUES ('" . addslashes($steam64) . "')";

Note: While prepared statements are preferred for security against SQL injection, the type handling remains critical. If you use PDO with prepared statements, ensure your bound parameter type is PDO::PARAM_STR rather than PDO::PARAM_INT.

Why This Matters for Production

Relying on default integer behavior in PHP is a dangerous habit when dealing with external API data like Steam IDs. The var_dump function can be misleading because it shows the current state of the variable, which might already be truncated if the runtime environment is 32-bit or if an explicit cast occurred earlier in your script.

In a production environment, silent truncation leads to duplicate user records or matching errors when comparing IDs between your database and the Steam Web API. By explicitly defining your column as BIGINT UNSIGNED or storing these identifiers as strings, you eliminate the risk of hitting the 2147483647 ceiling. This ensures that the value returned by the Steam API is preserved exactly as intended, regardless of the underlying architecture of your PHP installation.

Our network