1

I have a table where every row has an id, and every time I insert a new row, it akes the highest id and adds 1. However, I'd like new rows to take back the number let by rows that were deleted. How can I find the lowest id that does not exist? Thank you

PoutchiPatch
  • 168
  • 1
  • 11
  • 9
    Don't do it. Auto-increment primary keys should not be messed with. – juergen d Oct 02 '13 at 14:54
  • It is indeed a duplicate. Will delete – PoutchiPatch Oct 02 '13 at 14:56
  • This is a bad idea. First you could let your mysql engine take care of creating a unique identifier(id) and it is not advised to do this yourself. Second A id has to be unique and even the deleted row has to have their own id. For example when you have a user with id 3 named william and you delete that user and insert a new one with the name jan. William is willing to see his data and searches on his id 3 he suddenly finds information about jan. You shut not do this let mysql do the task with auto increment – botenvouwer Oct 02 '13 at 14:56
  • 1
    Further point about doing this. With an auto_increment field MySQL will ensure they are unique when generating them. If you try and reuse one that has been freed off you stand a chance of 2 statements retrieving the same id at the same time, and both trying to use it. – Kickstart Oct 02 '13 at 15:02

4 Answers4

5

You can use LEFT JOIN on the same table, and look if the next id exists.
I don't think this solution is really nice with a lot of rows, but it works :

SELECT (t.id + 1)
FROM `table` as t
LEFT JOIN `table` s ON s.id = (t.id + 1)
WHERE s.id IS NULL
ORDER BY t.id
LIMIT 1
zessx
  • 68,042
  • 28
  • 135
  • 158
3

Don't re-use id values that were previously used.

Example scenario:

  • Say your site has an abusive user who sends provocative emails. Emails make reference to the user's primary key number. You delete the user's account for violations, but the emails are still out there. You reassign the deleted id to a new, nice user, but he keeps getting complaints about the past abusive emails that he was not responsible for.

Auto-increment values should be unique but there is no need for them to be consecutive.

A 32-bit signed integer can be up to 231-1, or 2147483647. A 64-bit signed integer can be up to 263-1, or 9223372036854775807. You aren't likely to use up all integers.

MySQL does not take the highest value and add 1. It allocates the next value in the sequence. You may have missing values due to inserts failing, rows being deleted, or transactions being rolled back. This is normal. If you try to work around it, your application will become slow and complex. You should just use the auto-increment feature as designed, and accept that there will be some inevitable gaps.

Exception: if you restart the MySQL service, InnoDB's auto-increment values are reset to MAX(id)+1.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • People fetishize ID numbers way too much. Most of the time they shouldn't be exposed directly to the user. If necessary, add a presentable "sequence number" that is purely cosmetic and can be adjusted as necessary without ever screwing up data integrity, or recycling old numbers. – tadman Oct 02 '13 at 15:16
  • 1
    @tadman, yeah, in my book [SQL Antipatterns](http://pragprog.com/book/bksqla/sql-antipatterns), I called the recycling of auto-increment values "Pseudokey Neat-Freak." – Bill Karwin Oct 02 '13 at 16:22
0

You can use AUTO_INCREMENT to get a uniquely increasing ID, but they are generally not reused. What benefit do you get from reusing old values?

Ref: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Stephen Rosenthal
  • 773
  • 4
  • 15
0

Try this:

select id+1
from users
where (id+1) not in (select id from users) limit 1

SQLFiddle: http://www.sqlfiddle.com/#!8/3e5a0/5

Note, I agree with other people who indicate you should decide whether or not this is a good design to use.. If you have the option to not re-use numbers, I'd strongly recommend you do just that...

Sparky
  • 14,967
  • 2
  • 31
  • 45