Automatyczna dekrementacja 'id' podczas usuwania rekordu z MySQL

pytanie zadane 28 grudnia 2015 w SQL, bazy danych przez ScriptyChris Mędrzec (190,190 p.)

Ponoć nie jest to zalecane działanie. Mam bazę danych z produktami. Gdy dodam kilka produktów, ich id w bazie automatycznie inkrementuje się. Problem pojawia się, gdy usunę z bazy jeden lub kilka rekordów. Wtedy, gdy ponownie dodam produkty, to otrzymują one następne id (auto inkrementacja nadal działa). Efekt jest mniej więcej taki:

Po wstawieniu produktów do bazy:

id: 1, producent: apple ...

id: 2, producent: sony,

id: 3, producent: samsung

id:4 , producent: motorola,

Po usunięciu np. 2 ostatnich (jest ok):

id: 1, producent: apple ...

id: 2, producent: sony,

Ale gdy chcę dodać jakiś produkt, to powstaje coś takiego:

id: 1, producent: apple ...

id: 2, producent: sony,

id: 5, producent: lg

Chciałbym, aby id było w tym przypadku z powrotem 3, a nie 5. Bo gdy usunę tak kilka(naście produktów), to potem po wstawieniu innych (nowych) w tabeli pojawia się id: 10, albo 14 i nie wygląda to dobrze (z punktu widzenia "ludzkiego"). Czy da się dekrementować id przy usuwaniu rekordu z bazy, albo wyświetlać to później (w HTML) "poprawnie" z punktu widzenia człowieka (tzn. id zawsze posortowane od 1 do ostatniego, bez przeskoków po usuniętych produktach).

Czy jest to na tyle złe działanie (zmiana ID rekordu w tabeli), że w ogóle nie stosuje się tego typu działań?

3 odpowiedzi

odpowiedź 28 grudnia 2015 przez Comandeer Guru (600,810 p.)

Tak, jest to SKRAJNIE ZŁE I GŁUPIE DZIAŁANIE.

Pola typu AUTOINCREMENT powstały po to, by zawsze generować jednoznaczne i unikalne identyfikatory konkretnych zasobów. To oznacza, że każdy zasób ma inny numerek. Z tego można wysnuć kolejny wniosek, że w chwili, gdy zasób jest usuwany, numerek powinien przepaść razem z nim.

Czemu? Wystarczy sobie wyobrazić jak działałoby REST API gdyby ten sam URL wskazywał różne zasoby… A do tego prowadzi bawienie się z mechanizmem AI IDs…

komentarz 28 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

To może lepiej wyłączyć auto_increment w MySQL, ale za to przy dodawaniu produktu do tabeli (HTML) dodawać tam ID (przez JS). Wtedy ID kolejnego produktu, nawet po usunięciu rekordów, będzie zawsze o 1 większe od poprzedniego ALE istniejącego w tabeli, bo ID nie będzie zależne od bazy ale od tabeli w HTML? Czy to też głupi pomysł i ID nie powinno się zmieniać?

komentarz 28 grudnia 2015 przez Comandeer Guru (600,810 p.)

Szczerze? To jest jeszcze głupszy pomysł.

Po prostu używaj AI tak, jak to zostało przewidziane. I uwierz mi na słowo, że zostało to dobrze przewidziane ;)

komentarz 28 grudnia 2015 przez writen Nałogowiec (29,060 p.)

Na potrzeby tego tematu uznałbym, że nie można tak zrobić, a nawet jeśli, to nikt nie wie jak.

Trzeba być naprawdę pedantem, żeby robić takie rzeczy tylko ze względu na estetykę.

komentarz 28 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

To może jeszcze inaczej. Zostawić id auto inkrementowane dla MySQL. Ale w tabeli pokazywać numer (czy tam l.p) w sensie, numer produktu w tabeli - żeby się "ładnie" wyświetlało? Mnie chodzi o to, aby pokazywało kolejne numery produktów (nawet jeśli kilka usunę, a potem dodam nowe), bo AI bazy danych "psuje" ten widok, mimo że jest to prawidłowe dla bazy danych.

komentarz 28 grudnia 2015 przez Comandeer Guru (600,810 p.)

Ale numerek przy wyświetlaniu rozwiązuje się… przy wyświetlaniu (hint: licznik pętli!).

odpowiedź 29 grudnia 2015 przez Schizohatter Nałogowiec (39,600 p.)

odpowiedź 29 grudnia 2015 przez sonquer Gaduła (4,280 p.)

Jeżeli tak zrobisz, to doprowadzisz kolego do anomalii w bazie danych. Mam rozumieć że pole id jest PK?

Pokaż 7 poprzednich komentarzy

komentarz 30 grudnia 2015 przez sonquer Gaduła (4,280 p.)

Zgodnie z normami baz danych zapytania są kolejkowane. Więc zakładam sytuację w której 2 użytkowników w tym samym momencie wykonało tę samą procedurę. Po stronie, powiedzmy php. I do bazy leci:

SELECT COUNT(*) FROM TABELA <- user1 ( zwraca powiedzmy 5 bo tyle jest rekordów, a do zmiennej leci id + 1 czyli 6 )
SELECT COUNT(*) FROM TABELA <- user2 ( zwraca powiedzmy 5 bo tyle jest rekordów, a do zmiennej leci id + 1 czyli 6 )
INSERT INTO ... <- user1 ( dodaje rekord o id 6 )
INSERT INTO ... <- user2 ( dodaje rekord o id 6 )

No i mamy 2 produkty o tym samym id. Jak to obejdziesz? Bo taka sytuacja jest bardzo prawdopodobna. I uwierz bądź nie, ale jeżeli coś się może spieprzyć to się spieprzy. Pozdr!

komentarz 30 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

Ok ja wierzę że dla wielu użytkowników to musiałbym to lepiej przemyśleć i zrobić. Tylko że ta aplikacja jest dla 1 użytkownika bo jest uruchamiana na localhost. Piszę ją, bo takie mam zadanie na laboratorium ;)

komentarz 30 grudnia 2015 przez sonquer Gaduła (4,280 p.)

Widzisz, bo ja już patrzę na problemy z poziomu wyżej. Okej, jest to rozwiązanie aczkolwiek ja polecałbym użyć triggerów, są one dużo bezpieczniejsze i uwierz mi jeżeli to zastosujesz i ogarniesz to zabłyśniesz tam u siebie :-)

komentarz 30 grudnia 2015 przez ScriptyChris Mędrzec (190,190 p.)

Dzięki za radę, ale ja jeszcze przez chwilę trzymam się z dala od PHP i MySQL póki nie muszę robić rzeczy trudniejszych od użycia AJAXa i z pomocą PHP przesłać coś do bazy i z powrotem

P.S. Gdybym chciał lub musiał... to jak użyć/zastosować te triggery?

komentarz 30 grudnia 2015 przez sonquer Gaduła (4,280 p.)

Manuale prawdę Ci powiedzą :)
https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

Tłumacz sobie że są to takie eventy, tzn. jeżeli stanie się coś to ma się wykonać coś, czyli jeżeli usuniemy jakiś rekord poleceniem DELETE to trigger się wykona albo przed albo po.. Jest to logiczne, nawet bardzo :-)

+ EN Tutorial:
https://www.youtube.com/watch?v=BeG4IHFjqVg

Fajnie że udało mi się przeciągnąć kolejną osobę na ciemną stronę mocy. Powodzenia i wytrwałości!