MySql auto increment re-jigged

Did you ever notice that MySQL auto increment used as default value to write in, if column value is null (or not specified). This is a really great feature, used I think in 80% of tables as id. If you delete the last row of a table eg. where id=5, time comes for inserting a new record with ‘null’ as id value, auto_increment gives id=6 as next to use. In other words if we delete a row auto increment can’t pick up the deleted values. Normaly when the table has user_id whit auto increment it’s fine becouse we don’t want to use an existing user_id what’s maybe deleted temporary.
In some cases all numbers need to be used, if id makes a connection to other data eg.: simming qualifiers, the start positions are always filled if somebody can’t start, the person will be replaced(delete and insert) to fill the gap. The example is really simple and at this stage you maybe think you can manually delete and insert but imagine 1000’s of inserts and dozens of deletes daily.

To go around this problem, one way is to use a stored routine for the inserts and deletes. If you have a table in your database for storing the removed id values then calling a rutine for insert can include a line for check for available id, if not normal insert is executed, call a delete rutine can copy id(or more columns if needed) before actually delete the record.

A sample script, to give a general idea of  the solution mentioned before:

• MySql
• Sql
• sql functions
• Stored Procedures