When designing a hash/indexed system, is a timestamp a good base for unique IDs?

I find myself currently working on several projects which include user entries in a database which need to be uniquely identified. (Some examples are a userscript manager and a list of my apps.)

Is using a timestamp a good way or starting point to manage these entries and en开发者_运维问答sure they are unique?

In short, no.

Timestamps aren't inherently unique, due to various causes, including:

• Multiple threading (including threads on multiple hosts); and
• Timestamp granularity - that is, the clock itself - run up a pair of test programs, one which sends messages and one which "processes" them with their timestamp, and you'll see that you get many entries with the same timestamp, because on many platforms, the system clock doesn't change millisecond every millisecond. Worse, you can't depend on this behavior: it's dependent on platform and speed.

So, as colinmark noted in a comment, use a uuid if you need a unique identifier.

I would probably concatenate timestamp and something else, like a user_id or the text of the entry, and throw in some random characters if you can, then hash the whole thing.

Something like SELECT MD5(CONCAT(entry_time, NOW())) would probably do the trick, and it's nice and simple.

There are two reasons not to do this:

• there's no guarantee that timestamps are unique. It's entirely possible that two users interacting with the system will share a timestamp; even if this is unlikely, it's possible so you have to code around it.
• your unique identifier should be meaningless. A timestamp is sorta kinda meaningless, but it's possible someone would ask you to update the timestamp to deal with timezone issues, or daylight savings, or "I meant to post that yesterday"; in that case, you can't guarantee you'd break the uniqueness requirement.

Also, MySQL already includes a guaranteed way of assigning unique identifiers (auto increment integers), and UUIDs are widely considered to be the best way of doing this cross-platform. I'd go with the majority on this one...