MySQL：自动递增组合键
好的，我有一个看起来像这样的表：
Post
˪ Id
˪ Version
˪ Title
˪ Content
这个想法是 Id 和 Version 一起将成为主键，因为您可以多次发布同一篇文章，但版本不同。
我的问题是：我想让 Id 自动递增。在这样的设置中这可能吗？例如，当我插入帖子的第二个版本时，事情会变得混乱吗？
看来我需要在这里澄清一些事情。我想要做的是这样的事情：
INSERT INTO posts VALUES (NULL, 0, "A title", "Some content");
这将创建一个带有一些自动递增 id 的新帖子。假设它的 id 为 7。我现在想创建一个新版本：
INSERT INTO posts VALUES (7, 1, "A new title", "Some adjusted content");
这可行吗？或者 id 仍然会得到一个自动递增的值吗？即使它确实有效，我是否在做一些从数据库设计角度来看不应该做的事情等等？
Ok, I have a table that will look something like this:
Post
˪ Id
˪ Version
˪ Title
˪ Content
The idea is that the Id and Version together will be the primary key since you can have a single post multiple times but of different versions.
My question is this: I would like to have the Id to auto increment. Is this possible in a setup like this? Will things be messed up when I insert the second version of a post for example?
Seems like I need to clear some things up here. What I would like is to do something like this:
INSERT INTO posts VALUES (NULL, 0, "A title", "Some content");
That would create a new post with some auto incremented id. Let's say it got the id 7. I now want to create a new version:
INSERT INTO posts VALUES (7, 1, "A new title", "Some adjusted content");
Would that work? or would the id still get an auto incremented value? And even if it did work, am I doing something I shouldn't from a database design point of view, et cetera?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
考虑以下示例数据：
现在，用户更改帖子的标题和内容，创建一个具有自动递增 id 的新行：
这是某些帖子的第二版，但您无法看到它是否是帖子一的新版本（条目id 1）或发布两个（id 2 的条目）。
您需要一些东西来识别该帖子。您可以按照 Tobias 在他的回答中建议的那样将 post_id 列添加到表中。
此外，可以完全删除自动递增的 id 并使用复合主键（post_id，version） - 要点是拥有一个不会自动递增的 post_id，以便您可以拥有这样的数据：
这里，它很明显，第三行代表帖子 1 的新版本。
Consider the following example data:
Now a user changes title and content of a post, creating a new row with auto-incremented id:
This is version two of some post, but you cannot see if it is a new version of post one (entry with id 1) or post two (entry with id 2).
You need something to identify the post. You can add a post_id column to your table as Tobias suggests in his answer.
Further, it is possible to remove the auto-incremented id completely and use a composite primary key (post_id,version) - the main point is to have a post_id which is not incremented automatically so that you can have data like that:
Here, it is clear that the third row represents a new version of post 1.
我会这样做：
新 ID 仅指定该数据集的唯一 ID。然后 post_id 指定该数据集属于哪个帖子，版本代表条目的修订版本。
I would do it like this:
The new ID only specifies a unique ID for this dataset. The post_id then specifies to which post this dataset belongs an the version stands for the revision of the entry.
为什么不使用
Id作为某种版本指示器，否则您将有两列用于相同的目的，这被认为是数据库设计中的缺陷。Why don't you use the
Idas some kind of version indicator, otherwise you'd have two columns serving the same purpose which is considered a flaw in DB design.您将使用复合主键，其中 id 和 version 一起标识条目。然后你可以auto_increment id。当您添加新条目时，它将自动递增。当您修改帖子时，您明确定义了 id 和修订版，并且 id 不会增加。
You would use a composite primary key where id and version together identify the entry. You can then auto_increment id. When you add a new entry it will auto increment. When you revise a post you explicitly define id and revision and id will not be incremented.