How to Reset an MySQL Autoincrement Using a Max Value from Another Table

How can I reset an MySQL AutoIncrement using a MAX value from another table?

Use a prepared statement:

  SELECT @max := MAX(ID)+ 1 FROM ABC;

PREPARE stmt FROM 'ALTER TABLE ABC AUTO_INCREMENT = ?';
EXECUTE stmt USING @max;

DEALLOCATE PREPARE stmt;

How to reset AUTO_INCREMENT in MySQL

You can reset the counter with:

ALTER TABLE tablename AUTO_INCREMENT = 1

For InnoDB you cannot set the auto_increment value lower or equal to the highest current index. (quote from ViralPatel):

Note that you cannot reset the counter to a value less than or equal
to any that have already been used. For MyISAM, if the value is less
than or equal to the maximum value currently in the AUTO_INCREMENT
column, the value is reset to the current maximum plus one. For
InnoDB, if the value is less than the current maximum value in the
column, no error occurs and the current sequence value is not changed.

See How can I reset an MySQL AutoIncrement using a MAX value from another table? on how to dynamically get an acceptable value.

Reset AutoIncrement from max(column)

-- you can try this, but if it is auto incrmeent how is there records missing from the table for the key?

DECLARE @MaxColumn INT

SELECT @MaxColumn = MAX(ID)
FROM TAbleName

ALTER TABLE table AUTO_INCREMENT = @MaxColumn

How to set AUTO_INCREMENT from another table

This code will create procedure for you:

CREATE PROCEDURE `tbl_wth_ai`(IN `ai_to_start` INT)
BEGIN

SET @s=CONCAT('CREATE TABLE IF NOT EXISTS `table_name` (
`id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
`columnOne` tinyint(1) NOT NULL,
`columnTwo` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT = ', `ai_to_start`);

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

Then you may call CALL tbl_wth_ai(2); passing the parameter inside the brackets.

For example:

CALL tbl_wth_ai((SELECT id FROM `ttest` WHERE c1='b'));

Reorder / reset auto increment primary key

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

What to do if the auto-increment value reaches its limit?

Let's assume a table structure like:

CREATE TABLE `tbl` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
);

and INSERT queries like:

INSERT INTO tbl(id) VALUES (NULL);

In the real code there are also other columns in the table and they are also present in the INSERT query but we can safely ignore them because they don't bring any value to this specific issue.

When the value of column id reaches its maximum value no more rows can be inserted in the table using the query above. The next INSERT fails with the error:

SQL Error (167): Out of range value for column 'id'.

If there are gaps in the values of the id column then you can still insert rows that use values not present in the table but you have to specify the values for id in the INSERT query.


Anyway, if the type of your AUTO_INCREMENT column is BIGINT you don't have to worry.

Assuming the code inserts one million records each second (this is highly overrated, to not say impossible), there are enough values for the id column for the next half of million years. Or just 292,277 years if the column is not UNSIGNED.


I witnessed the behaviour on a live web server that was using INT(11) (and not UNSIGNED) as the AUTO_INCREMENTed PK for a table that records information about the visits of the web site. It failed in the middle of the night, after several years of running smoothly, when the visits number reached 2^31 (2 billions and something).

Changing the column type from INT to BIGINT is not a solution on a 2-billion records table (it takes ages to complete and when the system is live, there is never enough time). The solution was to create a new table with the same structure but with BIGINT for the PK column and an initial value for the AUTO_INCREMENT column and then switch the tables:

CREATE TABLE `tbl_new` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=2200000000;

RENAME TABLE `tbl` TO `tbl_old`, `tbl_new` TO `tbl`;


Related Topics



Leave a reply



Submit