在MySQL / Sequel中将VARCHAR列转换为INT

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

我试图将数据库中的列从VARCHAR设置为INT(值已经在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)

每个数字都是唯一的(带有VARCHAR值)，当将列设置为INT时，所有值都变得相同(2147483647)。注意，VARCHAR中的所有值都不包含字母(我检查过)

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.

现在我不知道为什么会发生这种情况，数据来自CSV，我在Sequel Pro中导入的时候检查了数据是否有不同的数据(不是2147483647)。在GUI中，它显示它是不同的，但是当全部导入时，它将自动变成2147483647。

What is happening, and how can I prevent this?

发生了什么事，我该如何预防?

2 个解决方案

#1

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

当您转换整数类型Int范围之外的数字时，从-2147483648开始。2147483647(即从-231到231-1)，MySql将值限制为可以存储在Int中的最大值，即2147483647。

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

您所显示的两个值都超出了可由Int表示的范围。您需要使用BigInt表示它们。

#2

Try changing the type of the column to BIGINT , this should solve your problem.

尝试将列的类型更改为BIGINT，这会解决您的问题。

The issue here is that the number stored in the VARCHAR is bigger then the maximum number that an INTEGER can hold and therefore out of the range.

这里的问题是存储在VARCHAR中的数字大于一个整数可以容纳的最大数字，因此超出了范围。

Here you can find a document explaining all about Numerical types.

在这里，您可以找到一份解释所有数值类型的文档。

#1