Use missing ids

Asked

Viewed 430 times

6

After seeing this question I have remembered a problem I have encountered several times:

Let’s assume that there is the following table:

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

Should it eliminate Banana and add Pêssego and AbacaxiI’ll take: 

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 always want to take advantage of the id with the missing sequence number, if there is none then it is only sequential (+1).

Previously I had solved this situation by making a request to the database and checking the missing numbers of PHP. But I think it’s not very performative, I could also try to limit the result and make several requests, but it doesn’t 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;

'Cause it will sort everything, shattering the sequence of the previous fruits.

Let’s assume:

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

Enter a new entry and run the previous query, it will be

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

But the intended is:

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

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

mysql
  • 2

    Is this out of curiosity or do you really want to do it?

  • I already solved this question in a project using php to calculate the missing numbers, but I wonder if there is any method in mysql, or some standard algorithm. In other words, curiosity.

  • 1

    Possible duplicate of How to organize auto-increment numbering of a column Id of a table in Mysql?

  • 1

    Duplicate question, you can see a solution here

  • It does not answer the question because it will order everything, and it will spoil the sequence of other numbers already assigned. Assuming 1.4, I enter a new record and it will be 1.2.3 and that is not what is intended but 1.2.4

  • The autoincrement is always incremental, only adds after the last id inserted (counter). What you’re looking for is a data structure called "static ordered list," you can implement this without using autoincrement, but I don’t see the need for a situation where it would be feasible to do this.

  • I never mentioned autoincrement. But in sequence, although the field is numerical I never mentioned that it would be autoincrement.

  • But the idea of an ID field must be an auto-incremental, because there is no other solution-algorithm, native in mysql, to automatically generate the field.

Show 3 more comments

1 answer

6


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 on SQL Fiddle.

What we did here was basically a LEFT JOIN of the table itself, relating the num on the one hand with the num - 1 of the other, and taking only those without correspondence (i.e., only those with interval).

I am calling numbers, because if you reuse, it is not convenient to call "id", because the number does not identify only each element.

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

  • Very good, thank you @Bacco. It is "simple" and works to the full.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.