为什么不使用记录的创建时间作为主键？ [英] Why not use the creation time of a record as a primary key?

问题描述

我有一个表，它有一个自动递增的PK和creation_date字段，这是unix时间戳。

我想知道为什么不丢失自动递增字段，并使用创建日期字段PK，因为它是唯一的（我使用1/1000的第二精度）。

I have a table, which have an auto-incremented PK and creation_date field, which is the unix timestamp.
I am wondering why not lose the auto-incremented field and use the creation date field as the PK, as it is unique (I am using 1/1000 of a second accuracy).

对于：我正在删除已建立索引的行。

反对：有重复的轻微（非常微小）机会，但它是容易处理这个非常罕见的事件。

For: I am killing an indexed row.
Against: there is a slight (very very slight) chance of a duplicate, but it is easy to handle this very rare event.

数据库是mysql。

推荐答案

你的数据可能会改变（无意义的ID永远不会）...当你意识到你存储时间在本地区域和DST发生什么会发生什么？如果要存储UTC和/或针对特定时区？有关更多排序注意事项，请参阅 wcoenen的回答。

The general answer is that your data may change (where a meaningless id never will)... what happens when you realise that you're storing time in the local zone and DST kicks in? If you want to store against UTC and/or against a specific time zone? For more ordering considerations see wcoenen's answer.

如果你开始创建1000的行第二，你必须打乱数据使其工作做某事，它不是为了。也许你会添加一个消歧列，使你的索引更大更慢...

If you start creating 1000's of rows a second, and you're having to mess with data to "make it work" doing something it was not intended for. Perhaps you'd add a disambiguation column that would make your index bigger and slower ...

然后当你的项目变得流行，人们开始试图运行报告/查询和它使用日期作为PK ??? !!!

And then when your project becomes mega popular and people start trying to run reports/queries and "it's using a date as a PK???!!!"

还要考虑使用允许非主列上的聚簇索引的数据库。

Also consider using a database that allows clustered indexes on non-primary columns.

这篇关于为什么不使用记录的创建时间作为主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！