我的自动增量 ID 应该使用什么大小的 INT MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3562737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What size INT should I use for my autoincrement ids MySQL
提问by Michael Robinson
Currently we're using INT(21)* for all autoincrement id columns in out 30+ table database.
目前，我们对 30 多个表数据库中的所有自动增量 id 列使用 INT(21)*。
We are a blogging site, and have tables storing members, comments, blog posts and the like.
我们是一个博客网站，有表格存储成员、评论、博客文章等。
I'm quite sure we will never reach the limit of our INT(21) id columns, and would like to know:
我很确定我们永远不会达到我们的 INT(21) id 列的限制，并且想知道：
- If using INT(21) when I am sure we'll never need it is a waste of space
- If it is a waste, what the recommended size for an autoincrement id column is
- 如果在我确定我们永远不需要它时使用 INT(21) 是浪费空间
- 如果是浪费，那么推荐的自增 id 列的大小是多少
*Not my design. I'm asking this because I'm considering reducing this to say, INT(10).
*不是我的设计。我问这个是因为我正在考虑将其减少为 INT(10)。
回答by OMG Ponies
The value within the brackets is the display width.
[It] 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. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
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
INT(3)has the usualINTrange of -2147483648 to 2147483647, and values outside the range permitted by three characters are displayed using more than three characters.
[It] 可以被应用程序用来显示宽度小于为列指定的宽度的整数值，方法是用空格向左填充它们。（也就是说，这个宽度存在于结果集返回的元数据中。是否使用它取决于应用程序。）
显示宽度不限制可以存储在列中的值的范围，也不限制宽度超过列指定值的值显示的位数。例如，指定为的列
INT(3)的通常INT范围为 -2147483648 到 2147483647，超出三个字符允许范围的值将使用三个以上的字符显示。
Conclusion
结论
INT(10)or INT(21), doesn't impact the values that can be stored. If you really have a concern, the data type can easily be changed to be BIGINT with no repercussions that I'm aware of. I'd look at how many new records are being created in a given period (IE a month) & see how long it'll take to max out the INT value based on that history.
INT(10)或INT(21), 不会影响可以存储的值。如果您真的有顾虑，可以轻松地将数据类型更改为 BIGINT，而不会产生我所知道的影响。我会查看在给定时间段（即一个月）内创建了多少新记录，并查看根据该历史记录最大化 INT 值需要多长时间。
回答by Amy B
See here for the limit of each int type: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
请参阅此处了解每种 int 类型的限制：http: //dev.mysql.com/doc/refman/5.0/en/numeric-types.html
Note that INT(21) == INT(100000). The number in brackets is just how many zeros are padded to it if you specify the field should be zero-padded.
请注意INT(21) == INT(100000)。如果您指定该字段应填充零，括号中的数字就是填充了多少个零。
An unsigned intfield can hold up to 4294967295 records (see link above).
一个未签名的int字段最多可以保存 4294967295 条记录（请参阅上面的链接）。
回答by Galen
Don't worry about taking too much space, space is cheap. Choose a column type that you know you won't exceed. You'll kick yourself when you choose smallint to save space and run into issues when your database won't hold anymore data.
不要担心占用太多空间，空间很便宜。选择您知道不会超过的列类型。当您选择 smallint 来节省空间并在您的数据库不再容纳数据时遇到问题时，您会踢自己。
