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_INCREMENT
ed 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
Rails: Get Next/Previous Record
Oracle: SQL Query That Returns Rows with Only Numeric Values
How to Migrate an Existing Postgres Table to Partitioned Table as Transparently as Possible
Postgresql Query to Count/Group by Day and Display Days with No Data
Add Row to Query Result Using Select
What Is the Fastest Way to Truncate Timestamps to 5 Minutes in Postgres
How to Tell What Edition of SQL Server Runs on the MAChine
How to Add a Column and Make It a Foreign Key in Single MySQL Statement
How to Do a Simple 'Find and Replace" in Mssql
How to Check If an SQL Result Contains a Newline Character
Calling a Stored Procedure in Oracle with in and Out Parameters
Why Can't I Simply Add an Index That Includes All Columns