Will MySQL reuse deleted ID's when Auto Increment is applied
InnoDB resets the auto_increment field when you restart the database.
When InnoDB restarts, it finds the highest value in the column and then starts from there.
This won't happen in MyISAM because it caches the last incremented id.
Update
This feature/bug has been around since 2003 and can lead to serious issues. Take the example below,
Table t1 has an auto-inc primary key.
Table t2 has a column for the primary key in t1 without a foreign key "constraint". In other words, when a row is deleted in t1 the corresponding rows in t2 are orphaned.
As we know with InnoDB restart, an id can be re-issued. Therefore orphaned rows in t2 can be falsely linked to new rows in t1.
This bug has been finally fixed in MySQL 8.0.0 WL#6204 - InnoDB persistent max value for autoinc columns.
InnoDB will keep track of the maximum value and on restart preserve that max value and start from there.
When you have a primary key field that is also auto_increment, no matter how many rows (or in what order) are removed, the ids that no longer exist are not used again, and the field is incremented continuously for each row.
However, when your primary key consists of multiple fields (E.G. an auto_increment field and another field), the auto_increment field is reused, and only when the deleted id is the last id. What this means is if you have values like 1, 2, 3, 4, and 5, and you remove the row with the field value of 5, the next row will have an id of 5. However, if you remove the row with the id of 2, this will, again, not be used, and the next row will have an id of 6.
As described with the InnoDB engine the last PK utilised will be reused if the row is deleted prior to a reboot as it is not cached. If this PK is a Foreign Key (FK) in another table problems can arise (FK hell). This is how I discovered the problem when a little old lady shot up to 195 cm(!) as the deleted person's additional data was picked up. The work around for this is either to implement 'ON DELETE CASCADE' for the child tables or (not my preferred option) to code around this issue.