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

原文： 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 .

 

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