MySQL 中的类型：BigInt(20) 与 Int(20)

Types in MySQL: BigInt(20) vs Int(20)

提问by Parris

I was wondering what the difference between BigInt, MediumInt, and Intare... it would seem obvious that they would allow for larger numbers; however, I can make an Int(20)or a BigInt(20)and that would make seem that it is not necessarily about size.

我想知道什么之间的区别BigInt，MediumInt以及Int在...这似乎很明显，他们将允许更大的数字; 但是，我可以制作一个Int(20)或一个BigInt(20)，这会使它看起来不一定与大小有关。

Some insight would be awesome, just kind of curious. I have been using MySQLfor a while and trying to apply business needs when choosing types, but I never understood this aspect.

一些见解会很棒，只是有点好奇。我一直在使用MySQL并尝试在选择类型时应用业务需求，但我从未理解这方面。

回答by Bill Karwin

See http://dev.mysql.com/doc/refman/8.0/en/numeric-types.html

见http://dev.mysql.com/doc/refman/8.0/en/numeric-types.html

• INTis a four-byte signed integer.

• BIGINTis an eight-byte signed integer.

• INT是一个四字节的有符号整数。

• BIGINT是一个八字节的有符号整数。

They each accept no more and no fewer values than can be stored in their respective number of bytes. That means 2³²values in an INTand 2⁶⁴values in a BIGINT.

它们每个接受的值都不能多于可以存储在各自字节数中的值。这意味着an 中有2 ^32个值， a中有INT2 ^{64 个}值BIGINT。

The 20 in INT(20)and BIGINT(20)means almost nothing. It's a hint for display width. It has nothing to do with storage, nor the range of values that column will accept.

20 in INT(20)andBIGINT(20)几乎没有任何意义。这是显示宽度的提示。它与存储无关，也与列将接受的值范围无关。

Practically, it affects only the ZEROFILLoption:

实际上，它只影响ZEROFILL选项：

CREATE TABLE foo ( bar INT(20) ZEROFILL );
INSERT INTO foo (bar) VALUES (1234);
SELECT bar from foo;

+----------------------+
| bar                  |
+----------------------+
| 00000000000000001234 |
+----------------------+

It's a common source of confusion for MySQL users to see INT(20)and assume it's a size limit, something analogous to CHAR(20). This is not the case.

MySQL 用户看到INT(20)并假设它是一个大小限制，类似于CHAR(20). 不是这种情况。

回答by John Feminella

The number in parentheses in a type declaration is display width, which is unrelated to the range of values that can be stored in a data type. Just because you can declare Int(20)does not mean you can store values up to 10^20 in it:

类型声明中括号中的数字是显示宽度，它与数据类型中可以存储的值的范围无关。仅仅因为您可以声明Int(20)并不意味着您可以在其中存储多达 10^20 的值：

[...] This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. ...

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

[...] 应用程序可以使用这个可选的显示宽度来显示宽度小于为列指定的宽度的整数值，方法是用空格向左填充它们。...

显示宽度不限制可以存储在列中的值的范围，也不限制宽度超过列指定值的值显示的位数。例如，指定为 SMALLINT(3) 的列通常具有 -32768 到 32767 的 SMALLINT 范围，并且使用三个以上的字符显示超出三个字符允许范围的值。

For a list of the maximum and minimum values that can be stored in each MySQL datatype, see here.

有关可以存储在每个 MySQL 数据类型中的最大值和最小值的列表，请参见此处。

回答by OMG Ponies

Quote:

报价：

The "BIGINT(20)" specification isn't a digit limit. It just means that when the data is displayed, if it uses less than 20 digits it will be left-padded with zeros. 2^64 is the hard limit for the BIGINT type, and has 20 digits itself, hence BIGINT(20) just means everything less than 10^20 will be left-padded with spaces on display.

“BIGINT(20)”规范不是数字限制。这只是意味着当显示数据时，如果它使用的数字少于 20 位，它将用零填充。2^64 是 BIGINT 类型的硬限制，并且本身有 20 位数字，因此 BIGINT(20) 仅意味着小于 10^20 的所有内容都将在显示时左填充空格。

回答by Sergey Podgornyy

As far as I know, there is only one small difference is when you are trying to insert value which is out of range.

据我所知，只有一个小的区别是当您尝试插入超出范围的值时。

In examples I'll use 401421228216, which is 101110101110110100100011101100010111000(length 39characters)

在示例中，我将使用401421228216，即101110101110110100100011101100010111000（长度为39 个字符）

• If you have INT(20)for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, only if it's less then INT(32) -> 2147483647(or 2 * INT(32) -> 4294967295for UNSIGNED)

• 如果您有INT(20)系统，这意味着在内存中分配至少 20 位。但是如果你插入的值大于2^20，它就会被成功存储，只有当它小于 then INT(32) -> 2147483647（或2 * INT(32) -> 4294967295for UNSIGNED）

Example:

例子：

mysql> describe `test`;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(20) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> SET sql_mode = '';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected, 1 warning (0,06 sec)

mysql> SELECT * FROM `test`;
+------------+
| id         |
+------------+
| 4294967295 |
+------------+
1 row in set (0,00 sec)

• If you have BIGINT(20)for system this means allocate in memory minimum 20 bits. But if you'll insert value that bigger than 2^20, it will be stored successfully, if it's less then BIGINT(64) -> 9223372036854775807(or 2 * BIGINT(64) -> 18446744073709551615for UNSIGNED)

• 如果您有BIGINT(20)系统，这意味着在内存中分配至少 20 位。但是，如果您插入的值大于2^20，它将被成功存储，如果小于 then BIGINT(64) -> 9223372036854775807（或2 * BIGINT(64) -> 18446744073709551615for UNSIGNED）

Example:

例子：

mysql> describe `test`;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | bigint(20) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);
Query OK, 1 row affected (0,04 sec)

mysql> SELECT * FROM `test`;
+--------------+
| id           |
+--------------+
| 401421228216 |
+--------------+
1 row in set (0,00 sec)

回答by Jayhello

Let's give an example for int(10) one with zerofill keyword, one not, the table likes that:

让我们举一个 int(10) 的例子，一个是 zerofill 关键字，一个不是，表格是这样的：

create table tb_test_int_type(
    int_10 int(10),
    int_10_with_zf int(10) zerofill,
    unit int unsigned
);

Let's insert some data:

让我们插入一些数据：

insert into tb_test_int_type(int_10, int_10_with_zf, unit)
values (123456, 123456,3147483647), (123456, 4294967291,3147483647) 
;

Then

然后

select * from tb_test_int_type; 

# int_10, int_10_with_zf, unit
'123456', '0000123456', '3147483647'
'123456', '4294967291', '3147483647'

We can see that

我们可以看到

• with keyword zerofill, num less than 10 will fill 0, but without zerofillit won't

• Secondly with keyword zerofill, int_10_with_zf becomes unsigned int type, if you insert a minus you will get error Out of range value for column...... But you can insert minus to int_10. Also if you insert 4294967291 to int_10 you will get error Out of range value for column.....

• 使用关键字zerofill，num 小于 10 将填充 0，但没有zerofill它不会

• 其次，使用关键字zerofill，int_10_with_zf 变为 unsigned int 类型，如果插入减号，则会出现错误Out of range value for column.....。但是您可以在 int_10 中插入减号。此外，如果您将 4294967291 插入 int_10，您将收到错误Out of range value for column.....

Conclusion:

结论：

1. int(X) without keyword zerofill, is equal to int range -2147483648~2147483647

2. int(X) with keyword zerofill, the field is equal to unsigned int range 0~4294967295, if num's length is less than X it will fill 0 to the left

1. int(X) 不带关键字zerofill，等于 int 范围 -2147483648~2147483647

2. int(X) with keyword zerofill，字段等于unsigned int 范围0~4294967295，如果num的长度小于X，则向左填充0

回答by Debasis Sabat

I wanted to add one more point is, if you are storing a really large number like 902054990011312 then one can easily see the difference of INT(20)and BIGINT(20). It is advisable to store in BIGINT.

我想补充一点是，如果你存储一个真正的大数字，如902054990011312那么就可以很容易地看到的差异INT(20)和BIGINT(20)。建议存放在BIGINT.