Search code examples
mysqlsqlsequelpro

Converting VARCHAR column to INT in MySQL / Sequel Pro

I'm trying to set the column in my database from VARCHAR to INT (values are already in DB)

When doing this action, the following happens (example):

| number (varchar) | number (int |
| 20090911913      > 2147483647  |
| 3009092113       > 2147483647  |

Every number is unique (with VARCHAR value), when setting the column to INT all values become the same (2147483647). Note that all values while being VARCHAR do not contain letters (I checked it)

Now I don't know why this happens, data comes from a CSV and I checked while importing in Sequel Pro if records have a different number (not being 2147483647). In the GUI it shows it's different but when all is imported it will automatically become 2147483647.

What is happening, and how can I prevent this?

Solution

  • When you convert numbers that are outside the range of signed integer type Int, which is from -2147483648.. 2147483647 (i.e. from -231 to 231-1), MySql limits the value to the max value that can be stored in an Int, i.e. 2147483647.

    Both values that you show are outside the range representable by an Int. You need to use BigInt form them instead.