4

I know auto_increment is the way to go but I can not use auto_increment feature since the column in my table might repeat, its not unique. When I insert a new row to a table I need a way to find the next available spot to insert it.

For example table structure:

 Primary Key = (ID, UserID)
 ID   UserID 
 3    6
 3    1
 1    3

Now when i do insert query i want to isert it at ID = 2 and not 4. With auto_increment it gives me 4

Is there a solution without using the loop in PHP? So far what i have is I fetch all rows into array and then find the next available digit in ID. Is it possible to do this without fetching all rows in PHP and just doing it on MySQL query ?

Improve this question

4 Answers 4

Reset to default
6 
SELECT t1.id+1 AS MISSING_ID
FROM the_table AS t1
LEFT JOIN the_table AS t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id LIMIT 1;

I made a fiddle: http://sqlfiddle.com/#!2/4d14d/2

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

hm this seems to be working, im trying to think if this will ever fail and return ID that already exists and not available one.
I used that method once, and till today, never failed :P
I believe this will fail if I don't have ANY id's in my table.
Right; if you don't have any IDs in the table, it'll return the empty set. It's up to you whether or not you consider that a failure. But it also "fails" to account for the possibility of if e.g. 1 is the missing ID...if you have 2,3,4 in your table, it will return 5 instead of 1
To address the above-mentioned problems, replace FROM the_table AS t1 with FROM (SELECT * FROM the_table UNION DISTINCT SELECT 0 AS id) AS t1. That basically adds a 0 as an additional row in the table
3

No, it is not possible without processing the data. The preferred method to correct this issue is to adjust your table structure to support a unique, auto-incrementable field. Failing that, you will have to process the data (either in PHP or via an SQL statement) to find an open slot.

Improve this answer

6 Comments

yea i guess thats the only way, the thing is my data grows very fast, daily there will be approximately 10,000 records going in, im afraid auto_increment will run out at some point. Out of 10,000 records inserted up to 6-7k gets deleted so i thought i would re-use those deleted IDs
@GGio An unsigned bigint field will support 18,446,744,073,709,551,615 records. Will that suffice to contain your data set?
@I Am Not Procrastinating, I said "without processing." Your solution, while it may work, clearly includes a substantial amount of processing. This may be acceptable to the OP because it isn't on the script side, but it certainly will require more cycles than a properly-structured table and autoincrement.
I think i will restructure it to use auto_increment although @IAmNotProcrastinating solution works, dont want to risk the possibility of it picking the same number instead of available one. Or running into a time issue since as i said my data grows fast so doing join and processing data would get slower as my data grows.
If you think it will grow massively it's better think in use AUTO_INCREMENT.
|
0

This should do the trick:

SELECT
  min_table.ID+1 AS start,
  MIN(max_table.ID) - 1 AS end
FROM
  your_table AS min_table,
  your_table AS max_table
WHERE
  min_table.ID < max_table.ID
GROUP BY
  min_table.ID
HAVING
  start < MIN(max_table.ID)

The left hand column will return the first available spot in the sequence gap, and the second is the highest number in that particular gap.

Source: http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

Improve this answer

Comments

0

My workaround for not loaded project:

Suppose, you have questionset with question_id 's which belong to certain topic_id.

Suppose, user navigates and clicks "<Prev" "Next>" buttons to navigate questions.

You have only current id. Catching the direction of navigation, topic_id, question_id you can do a loop

  do {

  // query base, doing question_id++ or question_id-- depending on needed    direction until you find next id within topic_id 

  } while( id!=null ) `

using incrementation or decrementation depending on direction of your move

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.