我应该使用什么大小的INT数作为我的自动递增id MySQL

Currently we're using INT(21)* for all autoincrement id columns in out 30+ table database.

目前我们在30+表数据库中使用INT(21)*为所有自动增量id列。

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
• 如果使用INT(21)，而我确信我们永远都不需要它，那就是浪费空间
• If it is a waste, what the recommended size for an autoincrement id column is
• 如果是浪费，那么自动递增id列的推荐大小是多少

*Not my design. I'm asking this because I'm considering reducing this to say, INT(10).

*不是我的设计。我问这个是因为我在考虑把它简化成INT(10)

3 个解决方案

#1

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

应用程序可以使用[It]显示宽度小于列指定宽度的整数值，方法是用空格填充它们。(也就是说，这个宽度出现在带有结果集的元数据中。它是否被使用取决于应用程序。

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 usual INT range of -2147483648 to 2147483647, and values outside the range permitted by three characters are displayed using more than three characters.

显示宽度不限制可以存储在列中的值的范围，也不限制显示宽度超过列指定宽度的值的位数。例如，指定为INT(3)的列通常具有-2147483648到2147483647之间的INT范围，并且使用三个字符所允许的范围之外的值显示出来。

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值需要多长时间。

#2

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)括号中的数字表示如果指定字段应该为零，则向它填充了多少个0。

An unsigned int field can hold up to 4294967295 records (see link above).

未签名的int字段最多可以保存4294967295条记录(参见上面的链接)。

#3

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来节省空间，并在数据库不再保存数据时遇到问题时，您将会感到很不舒服。