MySQL: can AUTO_INCREMENT generate values ​​out of order?

Question

Suppose I have a table:

CREATE TABLE test ( ID INT AUTO_INCREMENT PRIMARY KEY, InsertTime DATETIME ) ENGINE = InnoDB; 

And through the Apache / PHP website, in response to web requests, I continue to do this:

 INSERT INTO test (InsertTime) values (NOW()); 

Can we assume that if row1.ID > row2.ID , then row1.InsertTime >= row2.InsertTime ? Or, perhaps, through some unsuccessful combination of factors (a multiprocessor server in a replicated environment with the moons of Jupiter in the correct alignment, etc.), Can this fail?

Note. I do not have any problems. I am writing a new piece of software and am wondering if I can rely on sorting by ID as well as sorting by date (only NOW() will even be inserted in this column).

Answer 1

I think most problems will be caused by calling NOW () instead of AUTO_INCREMENT. I suspect that most computers at some point print NOW () dates out of order! This is usually either because the system administrator changed the clock, or because NTP changed the clock.

Answer 2

I think you will have some inconsistency once a year due to DST (of course, this depends on the server settings). Other than that, I don’t understand why this could fail.

Answer 3

Best of all, as I can tell, the auto-increment identifier will never stop returning records in the wrong order. However, there is another case that you need to know about when ordering by ID.

If your client ever holds onto records that will be inserted later, then when the records are read, they will not be read in the same created order. I came across this several times, for example, when creating clients for mobile applications that have intermittent network access.

Answer 4

Perhaps if someone manually resets the auto_increment in the table.

Answer 5

Yes, delete the auto_increment column, then recreate a new one.