MySQL中如何让多个表共享id列

发布于 2024-12-20 13:18:34 字数 369 浏览 0 评论 0原文

这个问题以前曾被问过，但从未真正得到回答（至少是我想要的方式）。我的数据库中有三个表：threads、responses 和 votes。我需要他们只共享 id 字段。所以我可以做类似的事情

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE id = 15

，并且只会检索线程、响应或（独占）id = 15 的投票记录。

有没有可能的方法可以在不创建额外表的情况下执行此操作？我不是在问这是否是一个好主意（这可能不是），而是问是否可能以及如何去做。

原文

This question has been asked before but never really answered (at least the way I want to do it). I have three tables in my database, threads, responses and votes. I need them to share the id field only. So I can do something like

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE id = 15

and will only retrieve a thread a response or (exclusive) a vote record with id = 15.

Is there a possible way to do this without creating an extra table? I'm not asking if it's a good idea (which is probably not), but if it's possible and how to do it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乖乖公主 2024-12-27 13:18:34

如果我理解正确的话，你想获取线程，或者响应，或者用 id = 5 投票。在 MySQL 中没有机会提供跨表的 id 字段唯一性（即防止创建响应并用相同的 id 值投票），但是你可以在你的应用程序中做到这一点。

您可以通过以下方式获取所需的行：

SELECT id, name, 'thread' AS `type` FROM threads WHERE id=5
UNION
SELECT id, name, 'response' AS `type` FROM responses WHERE id=5
UNION 
SELECT id, name, 'vote' AS `type` FROM votes WHERE id=5

If I understand right, you want to fetch thread, or response, or vote with id = 5. There is no opportunity to provide id field uniqueness across tables (i.e. prevent create response and vote with the same id value) in MySQL, but you can do it in your app.

And you can fetch needed row this way:

SELECT id, name, 'thread' AS `type` FROM threads WHERE id=5
UNION
SELECT id, name, 'response' AS `type` FROM responses WHERE id=5
UNION 
SELECT id, name, 'vote' AS `type` FROM votes WHERE id=5

回复收藏 0 原文

Saygoodbye 2024-12-27 13:18:34

您的选择将引发错误，因为 where 子句中的 id 字段不明确。我想这三个表中都有一个 id 字段。

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE id = 15

因此，即使 id 字段在这些字段之间“以某种方式共享”，您也必须使您的 where 子句明确：

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE t.id = 15

此外，我认为线程和响应具有多对一的关系，这意味着可以是多个响应对应于单个线程。这意味着 responses 表将有一个 thread_id 字段。 responses 表中的 id 字段不能与相应 thread 记录中的 id 字段具有相同的值，因为 id 字段在两个表中必须是唯一的。

同样的逻辑也适用于响应和投票之间的关系（单个响应将有很多票）。

因此我得出结论，没有可能的方法在这三个表之间共享id字段。

Your select will throw an error, because the id field in the where clause is ambiguous. I suppose there is an id field in all of these three tables.

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE id = 15

So even if the id field is "somehow shared" between these fields, you will have to make your where clause unambiguous:

SELECT * FROM threads AS t JOIN responses AS r JOIN votes AS v
WHERE t.id = 15

Furthermore, I suppose threads and responses have a many-to-one relationship which means there can be several response corresponding to a single thread. This means the responses table would have a thread_id field. The id field in the responses table could not have the same value as the id field in the corresponding thread record, since the id field must be unique in both tables.

The same logic goes to the relationship between responses and votes (a single response would have many votes).

Hence I conclude there is no possible way to share the id field between these three tables.

回复收藏 0 原文

~没有更多了~