Mysql的BigInt(20),Int(20)以及Bigint(32)有区别吗？

最新推荐文章于 2025-08-19 19:13:53 发布

原创最新推荐文章于 2025-08-19 19:13:53 发布 · 6.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #bigint

存储（SQL&NOSQL）专栏收录该内容

9 篇文章

订阅专栏

本文详细解释了MySQL中整数类型的使用，特别是INT与BIGINT的区别。INT为四字节有符号整数，BIGINT为八字节有符号整数。括号内的数字指示显示宽度而非存储大小，对ZEROFILL选项有影响。实际应用中，大多数场景使用INT已足够，只有特定情况下才考虑使用BIGINT。

原文： http://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20-etcc

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

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

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

INT is a four-byte signed integer. BIGINT is an eight-byte signed integer.

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. 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 |
+----------------------+

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:

[...] 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.

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

Mysql的数字类型（1B，2B，3B，4B，8B都有）

一个INT，占4个字节，跟JAVA中的int一样，即使是有符号也能表达21亿这么大的数据。 所以平时绝大多数情况，包括大型网站的UID，都用不了这么大的数据，好友关系表可能会超过，关系表可以考虑用BIGINT。还有就是平时时间戳需要用BIGINT。总之，不要轻易用上BIGINT，完全是浪费！