Use missing ids

Question

Use missing ids

Navigation

#1 by (6 votes)

6

After viewing this question I remembered of a problem that I have come across many times:

Let's suppose that the following table exists:

Fruta
-----------
1 | Maça
2 | Banana
3 | Pêra

If I delete Banana and add Pêssego and Abacaxi I'll get:

Fruta
-----------
1 | Maça  
3 | Pêra  
4 | Pêssego  
5 | Abacaxi

But what I want is:

Fruta
-----------
1 | Maça
2 | Pêssego
3 | Pêra
4 | Abacaxi

In other words, I want to always use the id with the missing sequence number, if there is none then it is only a sequence (+1).

Previously I had resolved this situation by making a request to the database and checking the missing numbers through PHP. But I think it's not very performative, you could also try to limit the result and make multiple requests, but it does not help either.

In terms of performance is there any method in sql to solve this problem? Or even some standard algorithm (in any language, PHP was an example) to evaluate these situations?

The following code does not answer the question:

SET @count = 0;
UPDATE 'tabela' SET 'tabela'.'id' = @count:= @count + 1;

It will sort everything, strangling the previous fruits.

Let's suppose:

Fruta
-----------
1 | Maça
4 | Abacaxi

I enter a new record and execute the previous query, it will be

Fruta
-----------
1 | Maça
2 | Abacaxi
3 | Maça

But what you want is:

Fruta
-----------
1 | Maça
2 | Maça
4 | Abacaxi

That is, we do not want to lose the connection to the number already associated with the existing fruits.

mysql

asked by anonymous 20.04.2018 / 16:08

1 answer

How to generate the alphabet with white space between the letters? About fixed footer position only at bottom of page

score 6 · Accepted Answer

If you want performance, a very simple way to find free numbers is this:

SELECT
  a.num - 1 AS livre
FROM
  frutas AS a
  LEFT JOIN frutas AS b ON a.num - 1 = b.num
WHERE
  b.num IS NULL AND a.num > 1

See working in SQL Fiddle .

What we did here basically was a% w of the table with itself, relating the LEFT JOIN on one side to the num on the other, and taking only the unmatched ones (ie only those with intervals) .

I'm calling numbers, because if you reuse it, it's no longer appropriate to call "id", since the number does not just identify each element.

The "limitation" of this query is that it depends on whether there is already a number in the database to detect the previous "free", but this is not a problem since it will reuse, the order of the reused vacancies should not be so important, as long as you keep those used (and eventually you fill the vacancies, you will end up at number 1 anyway);