在mysql上需要大于20位的整数怎么办?

2022-01-14 00:00:00 int mysql bigint varchar

似乎 BIGINT 是 MySQL 上可用的最大整数,对吧?

Seems like BIGINT is the biggest integer available on MySQL, right?

例如,当您需要存储 BIGINT(80) 时该怎么办?

What to do when you need to store a BIGINT(80) for example?

为什么在某些情况下,例如 Twitter API 文档中的某处,他们建议我们将这些大整数存储为 varchar?

Why in some cases, like somewhere in the Twitter API docs, they recommend us to store these large integers as varchar?

选择使用一种类型而不是另一种的真正原因是什么?

Which is the real reason behind the choice of using one type over another?

推荐答案

大整数实际上并不限于 20 位,它们仅限于可以用 64 位表示的数字(例如,数字 99,999,999,999,999,999,999 不是有效的大整数,尽管它有 20 位长).

Big integers aren't actually limited to 20 digits, they're limited to the numbers that can be expressed in 64 bits (for example, the number 99,999,999,999,999,999,999 is not a valid big integer despite it being 20 digits long).

你有这个限制的原因是底层硬件可以相对快速地操作原生格式整数,而数字的文本版本(往往)需要一次处理一个数字.

The reason you have this limitation is that native format integers can be manipulated relatively fast by the underlying hardware whereas textual versions of a number (tend to) need to be processed one digit at a time.

如果您想要一个大于最大 64 位无符号整数 18,446,744,073,709,551,615 的数字,那么您需要将其存储为 varchar(或其他文本字段)并希望你不需要对它做太多的数学运算.

If you want a number larger than the largest 64-bit unsigned integer 18,446,744,073,709,551,615 then you will need to store it as a varchar (or other textual field) and hope that you don't need to do much mathematical manipulation on it.

或者,您可以使用 decimal(65,0) 作为列类型.

Alternatively, you can look into floating point numbers which have a larger range but less precision, or decimal numbers which should be able to give you 65 digits for an integral value, with decimal(65,0) as the column type.

相关文章