6/19/2023 0 Comments Mysql uuid primary key default![]() ![]() Concerns about fragmentation of clustered indexes on UUID fields can be addressed effectively by just scheduling a maintenance task to rebuild the indexes regularly and stop them from getting fragmented. ![]() These days, size isn't really an issue unless your databases really are very large indeed and if that is the case, then altogether different solutions might be required. Once you have done this, what is the advantage in adding another integer field which must also be indexed? There is a fair amount of confusion about UUID data types plus, it has to be said, a fair amount of propaganda trying to discourage people from using them because of some perceived inefficiency in indexing and concerns about size. I'm still not understanding why it is necessary to provide an incrementing integer in addition to a UUID? If you are using the UUID as a key for your heterogenous joins, you must in any event, index it for performance. It's not clear what problem this would solve. Since the field is 128 bits long in either case, it saves no space and just introduces a nuisance when generating new key values since you would constantly have to convert uuid() to binary(16). The UUID is just a flag put there to uniquely identify the record and has no other meaning.Īs for converting UUIDs to BINARY(16), why would anyone do this? It looks to me as if it would cause more problems than it would solve. In all cases, it would be better to ORDER BY some other attribute of the record such as a create date or a sequence field put there just for that purpose. Choosing sequential UUIDs doesn't help and I have to question any design which relies on ordering by a surrogate key value in any case. It's true that UUIDs are large but, if you need to guarantee uniqueness, there are few other choices. Presumably, this is what the choice of UUIDs is designed to alleviate. ![]() If every customer has a separate database but, for some purposes it is necessary to join across more than one of them, then key clashes will occur on incrementing integers. There seems to be some misunderstanding of the OPs post. Or is there any reason /benefit to move from MySQL to PostgreSQL ? My_row_id bigint PRIMARY KEY NOT NULL AUTO_INCREMENT, Row_id for PK and FK and the uuid as additional column CREATE TABLE IF NOT EXISTS document ( Id binary(16) NOT NULL Default (unhex(replace(uuid(),'-',''))),ĪLTER TABLE document ADD CONSTRAINT FK_Document_User FOREIGN KEY (user_id) REFERENCES user (id) Store UUID as binary CREATE TABLE IF NOT EXISTS document ( `id` binary(16) NOT NULL Default (unhex(replace(uuid(),'-',''))),ĪLTER TABLE document ADD CONSTRAINT FK_Document_User FOREIGN KEY (user_id) REFERENCES user (my_row_id) `my_row_id` bigint PRIMARY KEY NOT NULL AUTO_INCREMENT Invisible, Now have read something about the MySQL feature GIPK "Global-Invisivle-Primary-Column".As i understand if its enabled the auto increment PK column "my_row_id" will be added to the table.It looks like this, but i think that makes it more complex and i also need the UUID so what the benefit of this feature ? CREATE TABLE IF NOT EXISTS `user` ( I know that a uuid is bad for performance and need more space therefore i plant to store it as binary(16). I'm working currently on a SaaS Web App where each customer has its own database.Īs PK it want to use an UUID instead of the auto increment bigint to avoid collisions, make the Api a little bit more secure and to has an Unique identifier when it comes to an error. ![]()
0 Comments
Leave a Reply. |