Remove spaces in auto increment

Let's say I have a MySQL table with an auto incrementing id field, after which I insert 3 rows. Then I remove the second line. The table ID is now 1.3. Can I get MySQL to fix this and make it 1.2 without having to write a program to do it?


source to share

2 answers

MySQL will not let you change the indexing of the Auto-Index column after it has been created. What I am doing is to remove the Auto-Index column and then add a new one with the same name, mysql will index the newly created column with no spaces. Do this only in tables where Auto-Index is not related to the rest of the data, but is simply used as a reference for updates and deletions.

For example, I recently only did this for a table containing proverbs, where the AutoIndex column was only used when I updated or deleted the proverb, but I needed an auto index to be consistent as proverbs are pulled through a random number between 1 and a number proverbially, the presence of spaces in a sequence could lead to a random number indicating a non-existent index.




Quote from Ten Record Access (and may be available to other RDBMSs: "You cannot use Autonumber (or Auto Incremental) if the field should make sense to your users."

The only alternative I can think of (using only MySQL) is:

  • Create a trigger that adds row number to column ( not primary key )
  • Create a procedure to delete lines and update the line number (I couldn't make this work with triggers, sorry)


create table tbl_dummy(
    id int unsigned not null auto_increment primary key,
    row_number int unsigned not null default 0,
    some_value varchar(100)

delimiter $$

-- This trigger will add the correct row number for each record inserted 
-- to the table, regardless of the value of the primary key    
create trigger add_row_number before insert on tbl_dummy
for each row
    declare n int unsigned default 0;
    set n = (select count(*) from tbl_dummy);
    set NEW.row_number = n+1;
end $$

-- This procedure will update the row numbers for the records stored
-- after the id of the soon-to-be-deleted record, and then deletes it.
create procedure delete_row_from_dummy(row_id int unsigned)
    if (select exists (select * from tbl_dummy where id = row_id)) then
        update tbl_dummy set row_number = row_number - 1 where id > row_id;
        delete from tbl_dummy where id = row_id;
    end if;
end $$

delimiter ;


Note that you will be forced to delete records one by one, and you will need to get the correct primary key value for the record you want to delete.

Hope it helps



All Articles