2

I'm currently using:

SELECT MAX(id) FROM table

To discover the current id of a certain table, but i heard this can bring bad results. What is the proper way of doing that? Please, notice that i'm not INSERTING or DELETING anything before that query. I just want to know the current ID, without prior INSERT or DELETE.

vinnylinux
  • 7,050
  • 13
  • 61
  • 127
  • http://stackoverflow.com/questions/1087698/retrieve-last-updated-column-in-mysql and more... look to the right section "Related" – Aurimas Ličkus May 02 '12 at 17:57
  • 1
    the max id can differ from the auto increment value of the primary key when something is deleted in between – Hajo May 02 '12 at 18:09
  • 1
    What exactly do you want to find? The id of the last inserted row? (what if it has been deleted in the mean time?) The id of the next future) row to be inserted? The row with the maximum id? – ypercubeᵀᴹ May 02 '12 at 18:12
  • 1
    @MarcusAdams the newest entry inside the table must not be the highest one, especially when mysql is used as a cluster (and is configured to reserve e.g. 5 ids per server). my example above might be a bit confusing. – Hajo May 02 '12 at 18:15
  • @Hajo, sorry. I thought we were talking about the id in the column, not the auto_increment attribute of the table. Guess we'll wait to hear from the OP. – Marcus Adams May 02 '12 at 18:17
  • I cant use last_insert_id because i'm not inserting anything or deleting anything in the current connection. Please, pay attention to the question. :( – vinnylinux May 02 '12 at 19:17

2 Answers2

2

Perform the following SQL:

SHOW TABLE STATUS LIKE 'TABLENAME'

Then check field AUTO_INCREMENT

Alan
  • 184
  • 7
  • I think it can be on InnoDB if the database is large. On the MyISAM tables I have to hand, which are admittedly fairly small, it runs in no time at all. – Alan May 02 '12 at 21:20
1

You can use the following query:

SELECT id FROM table ORDER BY id DESC LIMIT 1;
Klaus S.
  • 1,239
  • 10
  • 18
  • This will not necessary give you the last ID inserted, only the last ID still in the table. Imagine you insert 3 records, then delete record 3. The next ID is 4, and the MAX(ID) or first ID sorted DESC is 2. – Alan May 03 '12 at 17:08