[PHP/MySQL] Beste manier voor uniek ID in database

[mr._Anderson]

Ik begin voor mezelf aan een nieuwe website en daarbij hoort ook een database. Nu zit ik al een tijdje in de ontwerp fase en kan ik maar geen keus maken wat voor datatype ik voor de primary's keys van tabellen wil gebruiken. Ik heb veel gezocht op GoT en google, en krijg voor mezelf de volgende opties:

• AutoIncrement: dit lijkt de meeste simpele en ultieme oplossing. Echter een aantal nadelen. Het geeft performance terugslag omdat je in sommige gevallen moet gaan opvragen wat het ID is geworden wat de database geeft aan een record. Tevens kan het fout gaan bij backup's terug plaatsen of verschillende databases mergen.
• GUID / overig uniek ID: MySQL ondersteund zelf voor zover ik weet niet een GUID generatie, en ook voor PHP heb ik het nog niet kunnen vinden. Een algoritme wat er in ieder geval op lijkt zal wel in elkaar te zetten zijn (php heeft bijv. ook uniqid()) maar ook deze kent nadelen. Het is een kleine kans, maar het kan zijn dat je twee dezelfde id's genereert wat natuurlijk niet mag. Dit zou je dan eigenlijk zelf moeten gaan controleren, en records die je weg gooit zou je eventueel de id's van bij moeten houden zodat je deze niet nog een keer gebruikt, uit ultieme veiligheid. Ook dit alles heeft natuurlijk negatieve invloed op de performance.
• Zelf ophogen van de hoogst gebruikte id. Dit geeft geen problemen bij mergen of backups terug plaatsen maar is natuurlijk weer negatief op je performance omdat je zelf moet gaan kijken wat de maximale ID is. Tevens als je records weggooid bestaat de kans dat je een foreign key ergens laat staan, en deze daarna laat verwijzen naar een nieuw aangemaakt record welke dezelfde ID krijgt omdat je nu eenmaal 1 optelt bij de vorige ID. Je zou dus of niets mogen weggooien (wat ruimte kost natuurlijk) of je moet gaan bijhouden in een aparte tabel welke ID's je niet meer mag gebruiken.

Ik hoop dat het duidelijk is wat mijn gedachten gang bij de verschillende opties is geweest, en mijn vraag is of er misschien nog mensen zijn die slimmere oplossingen hebben en/of argumenten waarom ik het beste voor het één of ander zou kunnen kiezen.

[Verwijderd]

De time() als uniek id gebruiken ?

Weet je ook nog wanneer het is aangemaakt,
en is altijd uniek.

Kan hem ook nog md5 hashen, als je liever niet wil dat mensen het zien.

[whoami]

Optie 3 is het wiel opnieuw uitvinden en gaan bricoleren, en dat is een slecht idee.

Ga voor optie 1 of 2; welke van die 2 je gaat nemen zal afhangen van het doel van je applicatie en wat je met je DB gaat gaan doen:
Als je databases onder replication gaat zetten, dan kan het geen kwaad om een GUID te nemen. Als het een simpele databank is, dan zou ik voor optie 1 gaan.
Een GUID heeft ook een nadeel als het gaat om performantie; een GUID is groter dan een INT waardoor een SELECT query op de PK iets langer kan duren.

[mr._Anderson]

Verwijderd schreef op dinsdag 18 januari 2005 @ 11:00:
De time() als uniek id gebruiken ?

Weet je ook nog wanneer het is aangemaakt,
en is altijd uniek.

Kan hem ook nog md5 hashen, als je liever niet wil dat mensen het zien.

Als je met meerdere pc's heel snel bezig bent op de server kan het volgens mij zijn dat twee keer dezelfde time() wordt uitgegeven (pin me hier niet op vast)
MD5 is daarnaast hetzelfde als GUID, je kan twee keer dezelfde string genereren, als is de kans _heel_ klein.

[Verwijderd]

Ik doelde meer op om de time() te laten md5 hashen,

Maar volgens mij moet er heel veel gebeuren, wil jij in 1 miliseconde, precies 2 query's hebben draaien.

Maar het lijkt me heel stug, zou eigenlijk is een test omgeving moeten maken, en kijken of het lukt om 2 dezelfde timestamp in de Database te krijgen.

[jurri@n]

Misschien dat een combinatie van time() en rand() (random) een oplossing biedt? Eventueel deze twee weer MD5-hashen.

Dus:
MD5(CONCAT(TIME(),RAND()))

De kans dat EN dezelfde miliseconde EN hetzelfde random getal gebruikt worden is ontzettend klein (maar uiteraard niet onmogelijk)

[ Voor 31% gewijzigd door jurri@n op 18-01-2005 11:19 ]

[TheDane]

Ik kom op onze servers hetzelfde probleem tegen.
2 servers die statistieken bijhouden. Uniek ID van de statistieken-tabel is een timestamp+microtime.
Dan nog krijg ik 1 keer per week een notificatie dat er een record tabel op server-x niet geinsert kan worden vanwege duplicate key., En tot overmaat van ramp, als ik beide servers wil synchroniseren moet ik expliciet beide tabellen op beide servers excluden, ook weer vanwege duplicate keys.

php heeft een mooie functie: uniqid()
Deze in combinatie met rand() en md5() zou een aardig random id moeten leveren. Ik gebruik 'm nu voor een paar andere projecten, en ben er aardig tevreden over.

[mr._Anderson]

TheDane schreef op dinsdag 18 januari 2005 @ 11:27:
Ik kom op onze servers hetzelfde probleem tegen.
2 servers die statistieken bijhouden. Uniek ID van de statistieken-tabel is een timestamp+microtime.
Dan nog krijg ik 1 keer per week een notificatie dat er een record tabel op server-x niet geinsert kan worden vanwege duplicate key., En tot overmaat van ramp, als ik beide servers wil synchroniseren moet ik expliciet beide tabellen op beide servers excluden, ook weer vanwege duplicate keys.

php heeft een mooie functie: uniqid()
Deze in combinatie met rand() en md5() zou een aardig random id moeten leveren. Ik gebruik 'm nu voor een paar andere projecten, en ben er aardig tevreden over.

Het is precies wat je zegt, uniqid() is al wel beter, en in combinatie met een prefetch en nog wat extra's zoals een md5() is het redelijk betrouwbaar. Echter nooit 100%

Ik vroeg me af of er iets is wat theoretisch ook 100% betrouwbaar is.

[TheDane]

mr._Anderson schreef op dinsdag 18 januari 2005 @ 13:15:
[...]


Het is precies wat je zegt, uniqid() is al wel beter, en in combinatie met een prefetch en nog wat extra's zoals een md5() is het redelijk betrouwbaar. Echter nooit 100%

Ik vroeg me af of er iets is wat theoretisch ook 100% betrouwbaar is.

Ja, voordat je een record wil inserten, eerst table locken, kijken of er al een record met het gegenereerde unieke ID bestaat, zo niet inserten en unlocken, zo ja, ID opnieuw genereren, checken etc.

Maar imo is dat nogal wat overhead wat voor 99% van de gevallen niet veel toevoegt. Correct me if I'm wrong.
En bij synchronisatie/migratie moet je dan 'tzelfde doen volgens mij. Niet bepaald iets om naar uit te kijken volgens mij.

[Erkens]

misschien vergis ik me hoor, maar hoe krijg je met md5 iets unieker dan wat het is? naar mijn weten is de kans op een dubbele waarde bij een hash juist groter

AutoIncrement: dit lijkt de meeste simpele en ultieme oplossing. Echter een aantal nadelen. Het geeft performance terugslag omdat je in sommige gevallen moet gaan opvragen wat het ID is geworden wat de database geeft aan een record.

En jij denkt dat het genereren van een eigen key sneller is dan een simpele API call?

Tevens kan het fout gaan bij backup's terug plaatsen of verschillende databases mergen.

Dat is het enige echte argument om niet voor autoincrement te kiezen imo, alleen hoe vaak doe je dit?
Fout gaan is trouwens geen issue, je moet het zelf goed controleren niet simpelweg terug gooien

[ Voor 9% gewijzigd door Erkens op 18-01-2005 13:29 ]

[Bosmonster]

Snap de redenatie voor autoincrement ook niet. Performance is geen issue in dit geval. Backups terugplaatsen is ook geen probleem, aangezien je de ID's ook gewoon mee kunt backuppen natuurlijk

Mergen kan een probleem zijn, maar hetzelfde probleem heb je met het mergen van zelfgegenereerde ID's: Je kunt dubbele tegenkomen... Bij mergen zul je dus je gekoppelde data mee moeten laten cascaden in de update.

[ Voor 11% gewijzigd door Bosmonster op 18-01-2005 13:43 ]

[mr._Anderson]

Erkens schreef op dinsdag 18 januari 2005 @ 13:28:
misschien vergis ik me hoor, maar hoe krijg je met md5 iets unieker dan wat het is? naar mijn weten is de kans op een dubbele waarde bij een hash juist groter


[...]

En jij denkt dat het genereren van een eigen key sneller is dan een simpele API call?


[...]

Dat is het enige echte argument om niet voor autoincrement te kiezen imo, alleen hoe vaak doe je dit?
Fout gaan is trouwens geen issue, je moet het zelf goed controleren niet simpelweg terug gooien

Bij mijn weten is de theorie achter een hash dat het een unieke string zou moeten opleveren, als de tekst zelfs maar een klein stukje veranderd krijg je een hele andere hash. Het zou dus bij mijn weten dus juist 'unieker' moeten zijn.

Het gaat mij niet om het genereren van een key, het gaat er mij om dat je na het inserten van een record misschien nog iets met die record wilt doen waar je de ID voor nodig hebt. Als je zelf de ID hebt gegenereerd kan je hem opslaan in een variabele en heb je hem nog. Als je gebruik maakt van autoincrement zal er weer connectie gelegd moeten worden met de database om achter het ID te komen wat je net hebt ge-insert.

Iets waar ik bang voor ben bij bijv. een backup is het volgende geval:
records tabel 1:
ID-userName
1-user1
2-user2
3-user3

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2

je gooit iets weg uit tabel 1, en voegt daarna iets toe:
ID-userName
1-user1
2-user2
4-user4

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2
4-tekst-userid=4

Waar ik dus bang voor ben na de backup is het volgende geval:
ID-userName
1-user1
2-user2
3-user4

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2
4-tekst-userid=4 <- deze foreign key klopt nu dus niet meer

Ik hoop dat het bovenstaande duidelijk is

[marty]

mr._Anderson schreef op dinsdag 18 januari 2005 @ 16:38:
[...]


Bij mijn weten is de theorie achter een hash dat het een unieke string zou moeten opleveren, als de tekst zelfs maar een klein stukje veranderd krijg je een hele andere hash. Het zou dus bij mijn weten dus juist 'unieker' moeten zijn.

Dat iets op het oog meer verschilt wil niet zeggen dat het unieker is. (vind dat maar een vaag begrip trouwens)
En je vergist je hier toch echt, want een hash van 32 karakters heeft gewoon een beperkt aantal mogelijkheden (ook al zijn dat er héél veel) en dus loop je altijd het risico met twee verschillende strings op dezelfde hash uit te komen

Het gaat mij niet om het genereren van een key, het gaat er mij om dat je na het inserten van een record misschien nog iets met die record wilt doen waar je de ID voor nodig hebt. Als je zelf de ID hebt gegenereerd kan je hem opslaan in een variabele en heb je hem nog. Als je gebruik maakt van autoincrement zal er weer connectie gelegd moeten worden met de database om achter het ID te komen wat je net hebt ge-insert.

als jij binnen een scripttaal een 'unieke' id genereert dan kun je nooit met 100% zekerheid zeggen dat die niet al in de database voorkomt. Het uiterst geringen snelheidsverschil (benchmark het voor de gein eens) dat je daar mee wint kan volgens mij nooit opwegen tegen dat nadeel.

Iets waar ik bang voor ben bij bijv. een backup is het volgende geval:
records tabel 1:
ID-userName
1-user1
2-user2
3-user3

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2

je gooit iets weg uit tabel 1, en voegt daarna iets toe:
ID-userName
1-user1
2-user2
4-user4

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2
4-tekst-userid=4

Waar ik dus bang voor ben na de backup is het volgende geval:
ID-userName
1-user1
2-user2
3-user4

records tabel 2:
ID-inhoud-tabel1ID
1-tekst-userid=1
2-tekst-userid=2
3-tekst-userid=2
4-tekst-userid=4 <- deze foreign key klopt nu dus niet meer

Ik hoop dat het bovenstaande duidelijk is

zoals al eerder werd gezegd: daar hoef je niet bang voor te zijn, want ook de (unieke) ids backup je. Bij het terugzetten kunnen dus nooit spontaan andere ids ontstaan

[ Voor 5% gewijzigd door marty op 18-01-2005 16:57 ]

[Verwijderd]

TheDane schreef op dinsdag 18 januari 2005 @ 13:24:
[...]
Ja, voordat je een record wil inserten, eerst table locken, kijken of er al een record met het gegenereerde unieke ID bestaat, zo niet inserten en unlocken, zo ja, ID opnieuw genereren, checken etc.

Locken is niet echt nodig. Als de kolom waar de UID in komt de primary key is (wel waarschijnlijk), of als constraint UNIQUE heeft, dan kun je eenvoudigweg geen twee rijen met dezelfde UID toevoegen^[1].

Zodra je dat probeert te doen geeft de tweede INSERT operatie een foutmelding terug; de aanroepende code kan de fout inspecteren en indien nodig opnieuw proberen met een nieuwe (willekeurige) UID.


_{[1] Mits de database garandeert dat INSERTs atomair uitgevoerd worden, maar dat zal wel -- niet getest/opgezocht}

[mr._Anderson]

marty schreef op dinsdag 18 januari 2005 @ 16:56:
[...]
zoals al eerder werd gezegd: daar hoef je niet bang voor te zijn, want ook de (unieke) ids backup je. Bij het terugzetten kunnen dus nooit spontaan andere ids ontstaan

Op je eerste twee alinea's / verklaringen zeg ik: oke bedankt voor de uitleg.

De laatste heb ik dan toch nog een vraagje over...
Je zegt "Bij het terugzetten kunnen dus nooit spontaan andere ids ontstaan", dit geld dus ook in het geval van een autoincrement datatype?

Als dit zo is, en ik het dus goed begrijp kan je alleen maar problemen krijgen met autoincrement datatype's wanneer je verschillende databases gaat mergen om de zoveel tijd?

[marty]

mr._Anderson schreef op dinsdag 18 januari 2005 @ 17:35:
[...]


Op je eerste twee alinea's / verklaringen zeg ik: oke bedankt voor de uitleg.

De laatste heb ik dan toch nog een vraagje over...
Je zegt "Bij het terugzetten kunnen dus nooit spontaan andere ids ontstaan", dit geld dus ook in het geval van een autoincrement datatype?

Als dit zo is, [...]

Dit is zo

Als je een backup maakt wordt ook het veld met de auto-increment ids gebackupped. En bij het terugzetten worden dus ook die waardes weer teruggezet en niet nieuwe gegenereert.

[4VAlien]

Mysql heeft een aparte functie namelijk mysql_insert_id() zodat je 1e punt niet helemaal meer opgaat, misschien moet je daar even naar kijken. En je merge probleem krijg je ook als je je derde punt gebruikt omdat je namelijk niet voor meerdere databases die apart leven 1 gecentraliseerde functie gaat gebruiken.