当整数列的 auto_growth 达到数据库中的 max_value 时会发生什么？

我正在实现一个数据库应用程序，我将使用 JavaDB 和 MySQL 作为数据库。在我的表中有一个 ID 列，它的类型是整数，并且我使用数据库 auto _ Increment-function 作为值。

但是，当我得到超过20亿(或40亿)个帖子，而整数不够时，会发生什么情况呢？是整数溢出并继续，还是抛出我可以处理的异常？

是的，我可以更改为长的数据类型，但是如何检查何时需要这样做呢？而且我认为，如果对 ID 列使用 long 作为数据类型，那么获取 last _ insert _ id ()-函数就会出现问题。

50725 次浏览
小开

You will know when it's going to overflow by looking at the largest ID. You should change it well before any exception even comes close to being thrown.

In fact, you should design with a large enough datatype to begin with. Your database performance is not going to suffer even if you use a 64 bit ID from the beginning.

小开
最佳答案

Jim Martin's comment from §3.6.9. "Using AUTO_INCREMENT" of the MySQL documentation:

Just in case there's any question, the AUTO_INCREMENT field /DOES NOT WRAP/. Once you hit the limit for the field size, INSERTs generate an error. (As per Jeremy Cole)

A quick test with MySQL 5.1.45 results in an error of:

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

You could test for that error on insert and take appropriate action.

小开

Just to calm the nerves, consider this:

Suppose you have a database that inserts a new value for every time a user executes some sort of transaction on your website.

With a 64 bit integer as an ID then this is the condition for overflow: With a world population of 6 billion then if every human on earth executes a transaction once per second every day and every year (without rest) it would take more than 80 years for your id to wrap around.

Ie, only google needs to vaguely consider this problem occasionally during a coffee break.

小开

For MySQL 5.6 , 3.6.9 Using AUTO_INCREMENT in says:

Use the smallest integer data type for the AUTO_INCREMENT column that is large enough to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails.

小开

I would like to share a personal experience I just had about this. Using Nagios + Check_MK + NDOUtils. NDOUtils stores all the checks in a table called nagios_servicechecks. The primary key is a auto_increment int signed. What happens with MySQL when this limit is ranged? Well, in my case, MySQL delete all the records but the last one. The table is now almost empty. Everytime a new record is inserted the old one is deleted. Don't why this happen, but the fact is that I lost all my records. IDOUtils, used with Icinga (not Nagios), fixed this issue changing int by a bigint. It didn't generate a error.

小开

The answers here state what happens, but only one answer says how to detect the problem (and then only after the error has happened). Generally, it is helpful to be able to detect these things before they become a production issue, so I wrote a query to detect when an overflow is about to happen: 

SELECT
c.TABLE_CATALOG,
c.TABLE_SCHEMA,
c.TABLE_NAME,
c.COLUMN_NAME
FROM information_schema.COLUMNS AS c
JOIN information_schema.TABLES AS t USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
WHERE c.EXTRA LIKE '%auto_increment%'
AND t.AUTO_INCREMENT / CASE c.DATA_TYPE
WHEN 'TINYINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 255, 127)
WHEN 'SMALLINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 65535, 32767)
WHEN 'MEDIUMINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 16777215, 8388607)
WHEN 'INT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', 4294967295, 2147483647)
WHEN 'BIGINT' THEN IF(c.COLUMN_TYPE LIKE '% UNSIGNED', '18446744073709551615', 9223372036854775807) # need to quote because column type defaults to unsigned.
ELSE 0
END > .9; # 10% buffer

Hope this helps someone somewhere.


提交答案