![]() Also for SQL Server there is an option to generate sequential UUIDs. Basically like the same idea as the ULID. The link lists a lot of implementations for all kinds of programming languages. Basically 16 bytes where first part is a timestamp and rest is a random number. ULID (Universally Unique Lexicographically Sortable Identifier).The alternatives to generate primary keys MySQL has utility functions for converting UUID to and from binary(16) like UUID_TO_BIN() and BIN_TO_UUID(). You should really store them as binary(16) rather than strings since otherwise you will end up using char(36) in the DB. When you store them as binary(16) you will need som extra tweaking to display the values for the humans.See this Percona blog and also Stackoverflow. It will put unnecessary stress on the DB to fetch pages to the memory since it is not likely that the correct page is already fetched. The index is usually sorted and since UUID is random the DB server needs to inserts the new record in the middle somewhere instead of just append. But worse is that the UUID is a bit too random to make good use in a DB since they cannot be efficiently indexed in a B+tree that for instance MySQL uses.All tables that are using primary and foreign keys will take the hit when going from integer/long/bigint to 16 bytes UUID. So it will take up a lot of space in the DB compared to integers. And you can probably avoid a clumsy developer from deleting a record in wrong table since the UUID:s are unique over all tables too. ![]() There isn't any risk that someone is trying to guess a UUID. UUID:s also has the benefit that there will not be any bottlenecks when generating those. UUID (Universally unique identifier) can come to rescue since it is globally unique and you don't need any central location to coordinate the generation. It is very handy and the persistence layer will be very easy to manage using for instance JPA and Spring-data or any corresponding framework for Micronaut or Quarkus.īut the inevitably time comes when data from one DB needs to be migrated/merge to another DB and you realise that ID:s aren't unique between DB:s using that approach. I have seen a lot of schemas that are using integer datatype for the primary key and that the DB is responsible to generate the ID using a sequence generator. Instead surrogate keys are the preferred way. We can start ruling out natural keys since most likely that will have some privacy issues. Which datatype to pick for primary keys in the DB?
0 Comments
Leave a Reply. |