CODEWITHSUNDEEP
mysql
HeikoG
HeikoG

Reputation: 1813

Count(*) Vs. Max(Id)

If have a table where I do bulk imports from CSV files.

First column is the Id field with autoincrement. What bothers me is: When I do a

Select count(*)

And a

Select max(Id)

I get different values. I would have expected those to be identical ? What am I missing ?

Upvotes: 1

Views: 538

Answers (1)

tadman
tadman

Reputation: 211560

If you insert 10 rows, delete 5, then insert 10 more then your COUNT(*) will not match MAX(id).

You can also insert an id way ahead of where it should be, like in an empty table INSERT ... (id) VALUES (9000000) will kick up your MAX(id) significantly despite having only 1 row.

Rolled-back transactions can also interfere with this.

If you want to know the next increment, check the AUTO_INCREMENT value, but be aware that this is only a guess, the actual value used may differ by the time you actually get around to inserting.

If you want them to match then you need to:

  • Start with a table where AUTO_INCREMENT=1, as in it's either brand new or has been cleared with TRUNCATE.
  • Insert using auto-generated id values as one transaction, or as a series of transactions where all of them have been fully committed.

Upvotes: 1

Related Questions