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

2021-11-20 00:00:00 types mysql

我想知道 BigInt、MediumInt 和 Int 之间的区别...更大的数字；但是，我可以制作一个 Int(20) 或一个 BigInt(20) 并且看起来它不一定与大小有关.

I was wondering what the difference between BigInt, MediumInt, and Int are... 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.

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

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

推荐答案

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

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

  • INT is a four-byte signed integer.

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

BIGINT is an eight-byte signed integer.

它们每个接受的值都不能多于可以存储在各自字节数中的值.这意味着 INT 中有 232 个值，BIGINT 中有 264 个值.

They each accept no more and no fewer values than can be stored in their respective number of bytes. That means 232 values in an INT and 264 values in a BIGINT.

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

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.

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

Practically, it affects only the ZEROFILL option:

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

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

对于 MySQL 用户来说，看到 INT(20) 并假设它是一个大小限制，类似于 CHAR(20)，这是一个常见的混淆源.事实并非如此.

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.

相关文章