MySQL mysql中autoincrement可以产生的最大ID号是多少
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10707072/
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 is the biggest ID number that autoincrement can produce in mysql
提问by user123_456
I have an database that is rapidly filled with data we talk about 10-20k rows per day.
我有一个数据库，里面充满了我们每天谈论 10-20k 行的数据。
What is a limit of an ID with and autoincrement option? If ID is created as INTEGER then I can do max value of 2,147,483,647 for unsigned values?
带有和自动增量选项的 ID 的限制是多少？如果 ID 被创建为 INTEGER 那么我可以为无符号值做 2,147,483,647 的最大值？
But what when autoincrement goes above this? Does it all collapses? What would be solution then?
但是当自动增量超过这个时怎么办？会全部崩塌吗？那会有什么解决办法呢？
I am sure that a lot of people have big databases, and I would like to hear them.
我相信很多人都有大型数据库，我很想听听他们的意见。
Thank you.
谢谢你。
回答by Dan Armstrong
If you are worried about it growing out of bounds too quickly, I would set the PK as an UNSIGNED BIGINT. That gives you a max value of 18446744073709551615, which should be sufficient.
如果您担心它过快越界，我会将 PK 设置为 UNSIGNED BIGINT。这为您提供了 18446744073709551615 的最大值，这应该足够了。
回答by evilReiko
| Min. (inclusive) | Max. (inclusive)
-----------------------------------------------------------------------------
INT Signed (+|-) | -2,147,483,648 | 2,147,483,647
-----------------------------------------------------------------------------
INT Unsigned (+) | 0 | 4,294,967,295
-----------------------------------------------------------------------------
BIGINT Signed (+|-) | -9,223,372,036,854,775,807 | 9,223,372,036,854,775,806
-----------------------------------------------------------------------------
BIGINT Unsigned (+) | 0 | 18,446,744,073,709,551,615
If you have mysql table with column ID (INT unsigned), and the table has 4,294,967,295 records, then you try to insert 1 more record, the ID of the new record will be automatically changed and set to the max which is "4,294,967,295", so you get a MySQL error message Duplicate entry '4294967295' for key 'PRIMARY', you will have duplicated IDs if the column is set as Primary Key.
如果你有一个 mysql 表的列 ID (INT unsigned)，并且该表有 4,294,967,295 条记录，那么你尝试再插入 1 条记录，新记录的 ID 将自动更改并设置为最大值，即“4,294,967,295”，因此，您会收到一条 MySQL 错误消息Duplicate entry '4294967295' for key 'PRIMARY'，如果将该列设置为主键，则会出现重复的 ID。
2 Possible Solutions:
2 可能的解决方案：
- Easy Approach: Extend the limits, by setting the ID to BIGINT unsigned, just like what Dan Armstrongsaid. Although this doesn't mean it's unbreakable! and performance might be affected when the table gets really large.
- Harder Approach: Use partitioning,which is a little more complicated approach, but gives better performance, and truly no database limit (Your only limit is the size of your physical harddisk.). Twitter (and similar huge websites) use this approach for their millions of tweets (records) per day!
- 简单的方法：通过将 ID 设置为BIGINT unsigned来扩展限制，就像Dan Armstrong所说的那样。虽然这并不意味着它牢不可破！当表变得非常大时，性能可能会受到影响。
- 更难的方法：使用分区，这是一种稍微复杂的方法，但提供更好的性能，并且真正没有数据库限制（您唯一的限制是物理硬盘的大小。）。Twitter（和类似的大型网站）每天使用这种方法处理数百万条推文（记录）！
