MySQL: Using DATETIME as primary key

My database will be storing a large number of data points, so I am using an unsigned BIGINT as the primary key.

Would it ever make开发者_如何学编程 sense to use a DATETIME object as the primary key?

Thanks,

Yes if course it makes sense for a date/time to be a key or part of a key if you need to uniquely identify discrete points or periods of time. I can't say if that applies to your scenario but as a general rule there's no fundamental reason why keys can't be based on time - almost any data warehouse does it.

No because it can't be guaranteed to be unique. Stick with BIGINT. You can put a nice index on the DateTime for querying and it will be good enough.

It wouldn't make sense, as you would be limited to one record per second without any actual reason for that.

It makes sense if your data comes from a single time-ordered set. Say, a record of financial transactions. If you have multiple data points which naturally occurred at different instants, but have the same timestamp due to rounding, change the low-order bits to discriminate them.

This is more problematic in MySQL than in other databases, because timestamps are stored with only 1-second precision. (Edit: as of 5.6.4, MySQL has microseconds precision on time types)

If you happen to have multiple observations per second this will fail. For this reason it's probably better not to unless you can guarantee that there will never be more than one point per second.