在 MySQL 中将一对多关系重构为多对多：如何制定查询？
在我正在开发的应用程序的最初“版本”中，没有考虑到设计因素——没有人想到这一点。
不过看来原来的一对多关系需要重构为多对多。我的问题是如何最好地做到这一点？我使用 MySQL 来实现持久性。
填充关系表只是一次性的工作，我宁愿使用简单的查询或存储过程方法（我不太熟悉后者）；而不是编写基于 java/jdbc 的逻辑来做到这一点（我知道我可以，而且这不是太难，但这不是我想要的）
所以这里是一个关系的例子：
|VirtualWhiteBoard| -1------*- |Post|
虚拟白板可以有很多帖子。新功能是： 如果用户选择“复制”当前白板（之前没有想到），则 1 个帖子应属于多个白板 架构
如下所示：
VirtualWhiteBoard (wallName, projectName,dateOfCreation,..., Primary_Key(wallName, projectName));
Post(post_id, wallName,postData,..., Primary_Key(post_id), Foreign_Key(wallName, projectName));
虚拟白板具有复合主键（wallName，projectName）每个帖子都有一个 post_id 作为主键
问题：从 VirtualWhiteBoard 和 Post 中获取主键并将其添加到新关系“has_posts”中：
|VirtualWhiteBoard| -1------*- |has_Post| -*------1- |Post|
要保持之前的关系不变，然后删除 Post 中的 wallName 外键列。
如何最好地实现这一目标？查询就足够了还是需要存储过程？
（虽然我可以在“应用程序”中执行此操作，但我更愿意这样做，因为这样的重构必然会出现，并且我不希望存在不必要的java代码，这些代码需要维护并且个人会这样做我也希望拥有这样的技能:)
In the initial 'version' of the application that I'm working on, a design consideration wasn't taken into account - no one thought of it.
However, it seems that the original one-to-many relation needs to be refactored into a many-to-many. My question is how best to do this? I'm using MySQL for persistence.
Populating the relationship table will only be a one time effort, I'd rather go with a simple query or a stored procedure approach (I'm not well versed with the latter); rather than write java/jdbc based logic to do it (I know I can and it's not too difficult, but that's not what I want)
So here's an example of the relation:
|VirtualWhiteBoard| -1------*- |Post|
A virtual white board can have many posts. The new functionality is: 1 post should belong to multiple white boards if the user chooses to 'duplicate' current white board (not thought of before)
The schema looks like this:
VirtualWhiteBoard (wallName, projectName,dateOfCreation,..., Primary_Key(wallName, projectName));
Post(post_id, wallName,postData,..., Primary_Key(post_id), Foreign_Key(wallName, projectName));
The virtual white board has a composite primary key (wallName, projectName) and each post has a post_id as primary key
Question: Take the primary keys from VirtualWhiteBoard and Post and add it to the new relation 'has_posts':
|VirtualWhiteBoard| -1------*- |has_Post| -*------1- |Post|
To keep the previous relationships intact and then drop the foreign key column of wallName in Post.
How best to achieve this? Would a query suffice or stored procedures would be required?
(Although I can do this in the 'application' I'd prefer to do it this way, since such refactorings are bound to arise and I don't want unnecessary java-code lying around that'll need to be maintained and would personally prefer to have such a skill too :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
创建包含 post_id 和 wallName 两列的 has_Post 表，并使用以下查询填充它：
然后从 Post 表中删除 wallName 列。
Create your has_Post table with two columns post_id and wallName and populate it with this query:
Then delete the wallName column from Post table.