MySQL 中的两个表之间是否可以共享一个集合?
我目前正在设计一个数据库。
我有一个包含 20,000 多条记录的表,其中有一个集合(4 个值)。我还制作了另一个表(100 多条记录),该表将在同一集合上有一个枚举(同一集合中的 1 个值)
当前示例:
tbl1 tbl2
ID | Letters | Stuff ID | Letter | Stuff
---------------------- ---------------------
0 | A,B,C,D | ... 0 | D | ...
1 | A,B,C,D | 1 | C |
2 | C,D | 2 | A |
3 | B,C,D | 3 | D |
...
有没有办法确保集合相同,我可以比较枚举和集合?
随着数据的变化,我可能还需要向集合中添加更多选项。是否需要为该集合建立一个单独的表,然后再为其建立一个关联表?
我刚才所说的示例:
tbl1 tbl2
ID | Stuff ID | LetterID | Stuff
------------ ------------------------
0 | ... 0 | 3 | ...
1 | 1 | 2 |
2 | 2 | 0 |
3 | 3 | 3 |
...
tblLetters tblLetters1 (Association table)
ID | Letter tbl1Id | letterId
------------ ------------------
0 | A 0 | 0
1 | B 0 | 1
2 | C 0 | 2
3 | D 0 | 3
...? ...
我对此唯一主要关心的是关联表的大小是否太大(tbl1 中的大多数行将包含该集合的所有 4 个元素)。
谢谢你!抱歉,如果我没有很好地解释我的问题。我对 SQL 很陌生。
I am currently in the process of designing a database.
I have a table of 20,000+ records, which has a set in it (4 values). I also am making another table (100+ records) which will have an enum over the same set (1 value from the same set)
Example of current:
tbl1 tbl2
ID | Letters | Stuff ID | Letter | Stuff
---------------------- ---------------------
0 | A,B,C,D | ... 0 | D | ...
1 | A,B,C,D | 1 | C |
2 | C,D | 2 | A |
3 | B,C,D | 3 | D |
...
Is there a way to make sure that the sets are the same, and can I compare the enum and the set?
I also might need to add more options to the set as our data changes. Would a separate table for that set be necessary, and then an association table for that?
Example of what I just said:
tbl1 tbl2
ID | Stuff ID | LetterID | Stuff
------------ ------------------------
0 | ... 0 | 3 | ...
1 | 1 | 2 |
2 | 2 | 0 |
3 | 3 | 3 |
...
tblLetters tblLetters1 (Association table)
ID | Letter tbl1Id | letterId
------------ ------------------
0 | A 0 | 0
1 | B 0 | 1
2 | C 0 | 2
3 | D 0 | 3
...? ...
My only major concern with this is whether the size of the association table would be too big (most of the rows in tbl1 will have all 4 elements of the set).
Thank you! Sorry if I didn't explain my problem very well. I'm very green when it comes to SQL.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您的第二个解决方案似乎很好,通常应避免 1 列中的逗号分隔值。您可能不需要 ID,我会删除列的 ENUM 类型,并使用 tblLetters 中字母的实际类型/列定义:
可能向 tblLetters1.letter 添加
FOREIGN KEY
约束& tbl2.letter 强制使用 tblLetters 中的现有字母。从任何标准来看,总共 80K 行并不算多,应该没问题(不过使用正确的索引)
Your second solution seems fine, comma separated values in 1 column should normally be avoided. You might not need an ID, and I'd drop the ENUM type for the column, and use the actual type / column definition of the letter in tblLetters:
Possibly add a
FOREIGN KEY
constraint to tblLetters1.letter & tbl2.letter to enforce an existing letter from tblLetters.And 80K rows in total is not many by any standard, it should be fine (use the proper indexes though)
我要回答你的问题......
因此,根据我的理解,您只想确保表在枚举和设置字段中具有“选项”或“变量”。
您可以做的是:
您应该看到的是,
从技术上讲,您需要做的是确保两个表具有相同的变量。您可以使用脚本来完成此操作,或者在执行 ALTER TABLE 时注意这一点。
I'm going to take a stab at your question....
So from what I understand, you just want to make sure the tables have the "options" or "variables" in the enum and set fields.
What you can do is:
What you should see is
All you would need to to, technically, is make sure both tables have the same variables. You can do this with a script or just be aware of it when you do an ALTER TABLE.