Automatically set mysql autoincrement to min value

Question

i just wondered, if there is an elegant way of automatically resetting the autoincrement of a mysql table to the lowest value according to the present content.

example:

mytable:

1  content of row 1
2  content of row 2
3  content of row 3
4  content of row 4
5  content of row 5

now the autoincrement will be at 6

but before i insert new contents, i delete row 3,4 and 5. the content would look like this:

1  content of row 1
2  content of row 2

the autoincrement would still be at 6.

and this is the issue.

i would like the autoincrement to be at 3, because it is the lowest possible value according to the inserted IDs.

the would prevent extremely large numbers, if the autoincrement would grow "infinitely" and get out of range of a 12 digits long integer.

thanks for any suggestion!

Solution 1

This depends on your storage engine,

For MyISAM and for InnoDB on MySQL 5.6+, you can set the AUTO_INCREMENT value for the table to say 1. That value will automatically be increased to the maximum current value + 1. Do that like this.

ALTER TABLE table_name AUTO_INCREMENT = 1;

For InnoDB on MySQL < 5.6, this will not work and you will need to do this manually like this:

SELECT MAX(autoincrement_field) + 1 FROM table_name INTO @maxautoinc;
ALTER TABLE table_name AUTO_INCREMENT = @maxautoinc;

Note in that last case, the two queries will need to be run with the same database connection.

Solution 2

Reset Auto Increment IDs.

http://community.spiceworks.com/scripts/show/3042-reset-auto-increment-ids

update all auto increment columns in a database to the smallest possible value based on current values in the databases. We needed to do this after cleaning out a database.

Use a Prepared Statement within a Stored Procedure:

drop PROCEDURE if exists reset_autoincrement;
DELIMITER //
CREATE PROCEDURE reset_autoincrement (IN schemaName varchar(255))
 BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE o_name VARCHAR(255);
    DECLARE o_table VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.`COLUMNS` WHERE extra LIKE '%auto_increment%' and table_schema=schemaName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: LOOP
     FETCH cur1 INTO o_name, o_table;

     IF done THEN
       LEAVE read_loop;
     END IF;

  set @qry1 = concat('SELECT MAX(`',o_name,'`) + 1 as autoincrement FROM `',o_table,'` INTO @ai'); 
  PREPARE stmt1 FROM @qry1;
  EXECUTE stmt1;

  IF @ai IS NOT NULL THEN
      SELECT  o_name, o_table;
   select @qry1;
   select @ai;
   set @qry2 = concat('ALTER TABLE `',o_table,'` AUTO_INCREMENT = ', @ai);
   select @qry2;
   PREPARE stmt2 FROM @qry2;
   EXECUTE stmt2;
  END IF;

    END LOOP;

    CLOSE cur1;
 END //
DELIMITER ;


call reset_autoincrement('my_schema_name');