MySQL, autoincrement sequence?

Question

In a MySQL database column that has been set to AUTO_INCREMENT, can I assume that the values will always be created sequentially?

For instance, if 10 rows are inserted and receive values 1,2,3,...10, and then 3 is deleted, can I assume the next row inserted will receive 11?

The reason I ask is that I'd like to sort values based on the order in which they were inserted into the table, and if I can sort based on the auto incremented primary key it will be a little easier.

Answer 1

I've seen auto_increment mainly used for the primary key column. If you want to sort items by say date_added you should create an extra timestamp, date/datetime or int (epoch) column.

This way you make your intent explicit and easier to follow - also you can safely migrate, export and import your DB without the need to worry about how auto_increment is handled.

Answer 2

From what I understand from the manual; yes. Each table has it's own 'next auto increment value' that is incremented by the amount defined in auto_increment_increment (http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment) and that is never automatically reset, even though it can be manually reset. But as @miku said, if possible a timestamp would be preferable.