In the case of multiple data inserts with a single INSERT command, the LAST_INSERT_ID() function will return an automatically generated value for the first row. However, the AUTO_INCREMENT counter will increase, and the next time you insert a row in the table, holes will appear in the table. In the case of rollback of a data insertion transaction, no data will be added to a table. There are aspects to consider when using AUTO_INCREMENT, here are some: This value can be used to insert a new row into a related table. You can get the automatically generated value using the LAST_INSERT_ID() session function. But most of the time, using UUID as the primary key is a sign of pre-mature optimization and it's also a choice hard to revert afterward.INSERT INTO users(first_name, last_name, email) VALUES ('Simon', 'Wood', INTO users(first_name, last_name, email) VALUES ('Peter', 'Hopper', selection will provide the following result: Select from users table shown in dbForge Studio There are valid cases of choosing UUID e.g. And most applications are less complex than those issue tracking tools. Jira, Apple's Radar, Google's issue tracker, etc. In fact, all major issue tracking systems use an integer as the issue id. and issue id such as issue/123 is definitely more readable than issue/b1e92c3b-a44a-4856-9fe3-925444ac4c23. The tool likely will have at most 5 figure projects each containing 5 figure issues. Take the classic issue tracking/project management tool as an example. order #), inspected by the operation engineer, customer support etc.ĩ9.9% of the applications won't reach internet scale and they just consist of several models allowing CRUD operations, containing thousands of records. The primary key is not only used by the system, it's also exposed to the end user (e.g. Numbers are easy to write, easy to remember and easy to communicate. Why? Readability, and readability leads to simplicity. 95% of the time, the default choice should always be Auto Increment Integer. Attackers can also scan the integer range to explore leakage (though it shouldn't happen if ACL is implemented correctly).Īs listed above, there are Pros and Cons between the 2 approaches. Some business data can be exposed since the latest ID could represent the total number of inventories. And that service becomes a single-point-of-failure (SPOF). In a distributed system, this often means introducing a separate service to produce this sequential number. Instead, we must consult the database to figure out the next available primary key. It can't be used in the distributed system since it's quite likely that different hosts could produce exactly the same number. If the table itself has only a few columns, the extra primary key space overhead will become more significant. For Auto Increment Integer, when stored as in long format, it occupies 8 bytes. 
Thinking of issue id, obviously, issue-123 is much more readable than issue-b1e92c3b-a44a-4856-9fe3-925444ac4c23. This is especially valuable if we expose it externally. Using auto-increment integer/serial as the primary key is also quite common and every major database engine provides native support. On the other hand, PostgreSQL uses heap instead of the clustered primary key, thus using UUID as the primary key won't impact PostgreSQL's insertion performance. This is because it requires reordering the rows to place the newly inserted row at the right position inside the clustered index. For databases like MySQL, Oracle, which use clustered primary key, version 4 randomly generated UUID will hurt insertion performance if used as the primary key.People design their own UUID format to fix this and there is also a draft proposal to standardize it.
Though v1 UUID format contains the timestamp, it encodes the timestamp using little-endian in that the least significant time appears first, which renders the UUID hard to sort according to creation time. Not naturally sortable according to creation time.Version 1 UUID stores timestamp info, which could be useful sometimes.However, your security team would always insist that a publicly accessible UUID path does not meet the security standard. A sense of security since the malicious users can't guess the ID.Stateless, it can be generated on the fly.Easy for migrating data between systems since collision is only theoretically possible. No false positive for finding items using log. Most of the time, people either choose v4 (random UUID) or v1 (timestamp UUID) There are 5 standard UUID formats nowadays. By referring to some articles, I got the information below: UUID
0 kommentar(er)
