Long Integer Is Transformed When Inserted in Shorter Column, Not Truncated. Why? What Is the Formula

Long integer is transformed when inserted in shorter column, not truncated. Why? What is the formula?

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

The integer overflow will set the max allowed number in the DB as

2147483647

So you need bigint datatype for storing bigger integer

When I insert '50261111111s11, it inserts 2147483647 instead

First, the string '50261111111s11' is implicitly coerced to 50261111111¹.

Then it is capped at 2147483647², which is the maximum value for an INT field in MySQL.

(Unless an Order ID is a constrained integer, such an auto-increment key, maybe it should be a CHAR/VARCHAR column?)

---

¹ See the INSERT statement:

This might involve [an implicit] type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type.

(MySQL might implement a direct conversion/truncation to INT, but consider the case where the value supplied was CONVERT("50261111111s11", SIGNED INTEGER) - the result is 50261111111, not 0.)

² From 11.2.6 Out-of-Range and Overflow Handling:

• If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

• If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

MySQL: Inserting into Table Confusion

The max limit of int is 2147483647. Hence to fix the error, change your datatype to VARCHAR

mysql database does not store complete number of memberId

Facebook uid's don't have a set length, therefore there is no way to know how big to set your table so set your column type to an unsigned bigint, how to do this in phpmyadmin is like this:

Telephone number changed to 2147483647? How to represent telephone numbers?

The fact that telephone directory numbers (DNs) resemble integers notwithstanding, using a integer of any width to store a DN is very bad practice indeed.

That's because, as you have discovered, DNs that get manipulated as if they were integers sometimes become corrupted.

Using a 32-bit number to store a North American Dialing Plan number (Canada, Caribbean, US) is just flat incorrect. Partway through the 429 area code, your numbers will be corrupted even if you manage to use an unsigned integer.

Use varchar(20) and you'll be fine.

Here's something to think about: ITU-T recommendation E.123 for representing telephone numbers. http://en.wikipedia.org/wiki/E.123

---

Related Topics