MySQL 中的类型:BigInt(20) 与 Int(20) [英] Types in MySQL: BigInt(20) vs Int(20)

问题描述

我想知道 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 中有 2³² 个值，BIGINT 中有 2⁶⁴ 个值.

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 INT and 2⁶⁴ 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.

这篇关于MySQL 中的类型:BigInt(20) 与 Int(20)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！