I second the sentiment about not using randomness as a proxy for uniqueness. In general it's probably not gonna be a problem doing it your way, but if yer writing a blog article about it... best to demonstrate the "right" way.
There's an RFC for UUIDs, which should be the first port of call when considering this stuff: tools.ietf.org/html/rfc4122 (warning... like with all RFCs, it's pretty dry reading ;-)
Another consideration is make sure you use a DB which actually stores the UUID as an integer (or has a specific UUID type, which will be an integer under the hood), if you want the DB to scale. String-based UUID values don't index so cleanly. For small (and possibly medium) loads, this is unlikely to be an issue, but should be a consideration.
One way to mitigate this is to still use just an auto-increment int as the PK for a table and in FKs, but for stored objects that need to be fetched via a public interface, use a UUID for that lookup (for the less-hackable URLs, as per your article).
Oh sure, sorry I was not clear. I was talking about the distinction between the concepts of integers vs strings in the general sense, not an implementation-specific one.
I figured the size require was a given, but probably should not have assumed.
with innodb, the primary key is added to every indexrow.
so lets say we have the following table
|uuid|name(10)|password(10)|
with indexes:
primary key uuid, index row length 16
index name, index row length 16+10
index name,password, index row length 16+10+10
this adds quite the overhead, i would go for the layout
|id(int)|uuid|name(10)|password(10)|
id: auto increment, only used for internal inter table relations
uuid: used as unique id on the outside of the application
where we guarantee that uuid will stay the same for ever, and is used the user identification
id could change when we export/import the dataset in a new application or when we connect to an external application
where
I jumped down Kati's throat a little hastily - uuid4() is RFC compliant as far as I could skim. It sets the correct bits for a version 4 UUID, which generates the rest of the bits randomly. That's a valid UUID.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I second the sentiment about not using randomness as a proxy for uniqueness. In general it's probably not gonna be a problem doing it your way, but if yer writing a blog article about it... best to demonstrate the "right" way.
There's an RFC for UUIDs, which should be the first port of call when considering this stuff: tools.ietf.org/html/rfc4122 (warning... like with all RFCs, it's pretty dry reading ;-)
Another consideration is make sure you use a DB which actually stores the UUID as an integer (or has a specific UUID type, which will be an integer under the hood), if you want the DB to scale. String-based UUID values don't index so cleanly. For small (and possibly medium) loads, this is unlikely to be an issue, but should be a consideration.
One way to mitigate this is to still use just an auto-increment int as the PK for a table and in FKs, but for stored objects that need to be fetched via a public interface, use a UUID for that lookup (for the less-hackable URLs, as per your article).
when using mysql as reference; Substitute integer with binary(16) because a integer is just 4 bytes long, and a UUID needs 16 bytes
Oh sure, sorry I was not clear. I was talking about the distinction between the concepts of integers vs strings in the general sense, not an implementation-specific one.
I figured the size require was a given, but probably should not have assumed.
I see MySQL does not have an integer type that can contain a UUID:
dev.mysql.com/doc/refman/5.7/en/in...
That said, how well will a binary column work as a PK and for FK relationships?
Bad,
with innodb, the primary key is added to every indexrow.
so lets say we have the following table
|uuid|name(10)|password(10)|
with indexes:
this adds quite the overhead, i would go for the layout
|id(int)|uuid|name(10)|password(10)|
id: auto increment, only used for internal inter table relations
uuid: used as unique id on the outside of the application
where we guarantee that uuid will stay the same for ever, and is used the user identification
id could change when we export/import the dataset in a new application or when we connect to an external application
where
I jumped down Kati's throat a little hastily - uuid4() is RFC compliant as far as I could skim. It sets the correct bits for a version 4 UUID, which generates the rest of the bits randomly. That's a valid UUID.