表之间的MySQL自动递增 [英] MySQL auto-increment between tables

问题描述

在MySQL中，是否可以在两个不同的表中自动增加一个列?示例:table1的列为"secondaryid"，table2的列为"secondaryid". table1.secondaryid和table2.secondaryid是否可以保存相同的信息?像table1.secondaryid可以保存值1、2、4、6、7、8等，而table2.secondaryid可以保存值3、5、9、10?这样做的原因有两个:1)这两个表将在一个单独的喜欢"表中被引用(类似于用户喜欢在Facebook上的页面)，以及2)表2中的数据是使用主键的表1的子集.因此，表2中包含的信息取决于表1，因为它们是不同类别的主题. (类别为table1，主题为table2).可以做上述事情吗，还是我不知道有其他结构性工作?

In MySQL, is it possible to have a column in two different tables that auto-increment? Example: table1 has a column of 'secondaryid' and table2 also has a column of 'secondaryid'. Is it possible to have table1.secondaryid and table2.secondaryid hold the same information? Like table1.secondaryid could hold values 1, 2, 4, 6, 7, 8, etc and table2.secondaryid could hold values 3, 5, 9, 10? The reason for this is twofold: 1) the two tables will be referenced in a separate table of 'likes' (similar to users liking a page on facebook) and 2) the data in table2 is a subset of table1 using a primary key. So the information housed in table2 is dependent on table1 as they are the topics of different categories. (categories being table1 and topics being table2). Is it possible to do something described above or is there some other structural work around that im not aware of?

推荐答案

似乎您想在两个单独的表中区分类别和主题，但是要在另一个表likes中引用它们的ID，以方便用户喜欢类别或主题.

It seems you want to differentiate categories and topics in two separate tables, but have the ids of both of them be referenced in another table likes to facilitate users liking either a category or a topic.

您可以做的是创建具有categories和topics子类型的超实体表.自动递增的密钥将在超级实体表中生成，并仅插入两个子类型表之一(基于它是类别还是主题).

What you can do is create a super-entity table with subtypes categories and topics. The auto-incremented key would be generated in the super-entity table and inserted into only one of the two subtype tables (based on whether it's a category or a topic).

子类型表通过1:1关系的自动递增字段引用此超实体.

The subtype tables reference this super-entity via the auto-incremented field in a 1:1 relationship.

这样，您可以仅基于一个列(可以表示类别或主题)将超实体表链接到likes表，并且子类型表中将不存在id两者都有.

This way, you can simply link the super-entity table to the likes table just based on one column (which can represent either a category or a topic), and no id in the subtype tables will be present in both.

这里是一个简化的示例，可以用来对此建模:

Here is a simplified example of how you can model this out:

通过该模型，您可以维护类别和主题之间的关系，但可以在superentity表中将这两个实体概括化.

This model would allow you to maintain the relationship between categories and topics, but having both entities generalized in the superentity table.

此模型的另一个优点是，您可以将子类型表中的公共字段抽象到superentity表中.假设categories和topics都包含字段title和url:您可以将这些字段放在superentity表中，因为它们是其子类型的公共属性.只能将特定于子类型表的字段放在子类型表中.

Another advantage to this model is you can abstract out common fields in the subtype tables into the superentity table. Say for example that categories and topics both contained the fields title and url: you could put these fields in the superentity table because they are common attributes of its subtypes. Only put fields which are specific to the subtype tables IN the subtype tables.

这篇关于表之间的MySQL自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！