mysql delete and then insert. Does not insert into delete line for AI table! Why?

I am not sure I am correct in this.

I have insert 5 rows with values with id as primary key and autoinc开发者_运维问答rement.

Then I delete row number 2-4.

when I insert on a new line with none id, the new id becomes "6". Is this normal?

How is it possible if you want the mysql to insert in the deleted row? what are the settings?

Yes, this is normal. If you want to insert with a specific ID number you have to specify that number on your insert statement.

The idea of auto increment is for the value to continually increase.

This is normal, though for some database engines you might receive 2, but usually it will be 6. In MSSQL it is possible to specify a value for an autoinc field with particular setting. Not sure what it is in mysql.

That's the expected behavior. Autoincrement primary keys are designed to be unique and continually increasing - so they are not assigned again.

If you TRUNCATE a table the autoincrement value is being reset while it stays as it is if you delete all rows with an DELETE query - that's a subtle but sometimes important difference.

As webdestroya suggested, the only possibility to reuse old and deleted autoincrement values is to set them specifically on the INSERT statement.