Jeśli zmierzyłeś się kiedyś z duplikatami rekordów w bazie MySQL, to zapewne doświadczyłeś również zjawiska przeciwnego - ich braku. W tym wpisie zajmiemy się sposobami znalezienia dziur w... nie, nie, nie tam... w autonumeracji.
Jak podążać króliczą norą?
Zaczniemy od najprostszego przykładu. Pozwoli on znaleźć miejca, w których numeracja jest nieciągła, ale nie wskaże wszystkich numerów, które przynależą do tej króliczej nory (jeśli jest ich więcej niż 1).
Za bazowe dane niech posłuży prosta lista rekordów:
| id | imię |
|---|---|
| 1 | Janek |
| 2 | Marek |
| 3 | Stefan |
| 6 | Waldek |
| 8 | Mirek |
Z prostej analizy tabeli widać, że przerwy zaczynają się po numerach 3 oraz 6, czyli dokładnie w miejscu o wartości 4 oraz 7. Czyli... czyli jeśli dziura jest, to jest w miejscu o numerze X+1. To już może nasunąć Ci jakiś pomysł na rozwiązanie - sprawdzanie czy w tabeli występuje wartość o jeden większa od wartości aktualnie przeszukiwanego rekordu.
Wykorzystamy tu sprzęgnięcie tabeli z samej sobą, ale warunkiem nie będzie równość wartości id z obydwu tabel, ale równość oparta na wyrażeniu X=Y+1.
SELECT t1.id
FROM tabela AS t1
LEFT JOIN tabela AS t2 ON t2.id=t1.id+1
Złączenie LEFT JOIN pozwala nam wybrać wszystkie rekordy z tabeli t1 oraz wszystkie rekordy z tabeli t2, a jeśli brak jest ich odpowiedników w tabeli t1, to otrzymamy tu wartość NULL.
Dla powyższego zapytania zostaną wybrane 4 rekordy zgodnie z poniższą tabelą:
| t1.id | t2.id | t2.id=t1.id+1 |
|---|---|---|
| 1 | 1 | |
| 1 | 2 | true |
| 1 | 3 | |
| 1 | 6 | |
| 1 | 8 | |
| 2 | 1 | |
| 2 | 2 | |
| 2 | 3 | true |
| 2 | 6 | |
| 2 | 8 | |
| 3 | 1 | |
| 3 | 2 | |
| 3 | 3 | brak 4 w t2 (NULL) |
| 3 | 6 | |
| 3 | 8 | |
| 6 | 1 | |
| 6 | 2 | |
| 6 | 3 | |
| 6 | 6 | brak 7 w t2 (NULL) |
| 6 | 8 | |
| 8 | 1 | |
| 8 | 2 | |
| 8 | 3 | |
| 8 | 6 | |
| 8 | 8 | brak 9 w t2 (NULL) |
Ponieważ nie interesują nas "niedziury" w tabeli, zmieniamy zapytanie na:
SELECT t1.id
FROM tabela AS t1
LEFT JOIN tabela AS t2 ON t2.id=t1.id+1
WHERE t2.id IS NULL
To ograniczy nam wyniki z powyższej tabeli do wartości 3, 6 i 8. I właśnie po tych miejscach zaczynają się nasze przerwy. No... może oprócz wartości 8, bo ta jest po prostu ostatnią wartością w tabeli. Aby i jej się pozbyć, rozbudowujemy zapytanie do postaci:
SELECT t1.id
FROM tabela AS t1
LEFT JOIN tabela AS t2 ON t2.id=t1.id+1
WHERE t2.id IS NULL AND t1.id<>(SELECT MAX(id) FROM tabela)
Jeśli chcesz wiedzieć jakie wartości tworzą początek dziury (a nie po jakich zaczyna się dziura), wystarczy zmienić pierwszą linijkę na:
SELECT t1.id+1
...
3.2.1. Bum!
W ten sposób otrzymamy wartości 4 oraz 7.
Czy można znaleźć całą zawartość dziury?
Można. Co prawda nie zrobimy tego sprzęgając tabelę z samą sobą, bo musielibyśmy utworzyć zapytanie UNION łącząc przykładowe zapytanie z warunkiem +1, +2, +3, itd. (aby znaleźć elementy z początku dziury i kolejne do niej należące). A ponieważ nie wiemy jak szeroka jest dziura, to nie wiemy również ile trzeba takich zmodyfikowanych zapytań dodać do tego wielkiego UNION.
Trzeba źródłową tabelę sprzegnąć z tabelą, która posiada ciągłość numeracji. A skąd wziąć taką tabelę? No, jak to skąd, stąd!
Nasze zapytanie wyglądać będzie następująco:
SELECT tmp.id FROM (
SELECT a.a+b.a*10+1 AS id FROM
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
) AS tmp
LEFT JOIN tabela AS t1 ON t1.id=tmp.id
WHERE t1.id IS NULL and tmp.id<=(SELECT MAX(id) FROM tabela)
Dla uproszczenia pokażmy je tak:
SELECT tmp.id FROM (
zapytanie_generujace liczby od 1 do 101
) AS tmp
LEFT JOIN tabela AS t1 ON t1.id=tmp.id
WHERE t1.id IS NULL and tmp.id<=(SELECT MAX(id) FROM tabela)
Sprzęgamy poprzez LEFT JOIN nasze zapytanie, które "tworzy" rekordy od 1 do 101 (możesz je rozbudować, aby generowało tysiące, dziesiątki tysięcy, itd. - szczegóły w wyżej wspomnianym wpisie) z tabelą źródłową. Jeśli po sprzęgnięciu brak jest wartości z tabeli źródłowej w zakresie wartości 1-101, to otrzymujemy wartość NULL. Aby wychwycić tylko te braki, dodajemy odpowiedni warunek do klauzuli WHERE. Dodatkowo ograniczamy zakres 1-101 do 1-8, czyli tak naprawdę sprzęgamy z tą pulą, a nie pulą ograniczoną z góry wartością 101.
Wynik jaki otrzymamy wygląda następująco:
| tmp.id |
|---|
| 4 |
| 5 |
| 7 |
Mam nadzieję, że nie będziesz szukał dziury w całym i jednak wpis określisz jako fajny.
Przydatne linki:
Plaga mrówek, czyli jak zmultiplikować rekordy zapytania.