用外键将表拆分成两个表 [英] Split table into two tables with foreign keys

问题描述

我有一个表:drupal.comments，除其他外，还有列:

I have one table: drupal.comments, with amongst others, the columns:

cid: primary key
uid: foreign key to users table, optional
name: varchar, optional
email: varchar, optional

描述中说:UID是可选的，如果为0，则匿名注释；否则为0.在这种情况下，将设置名称/电子邮件.

The description says: UID is optional, if 0, comment made by anonymous; in that case the name/email is set.

我想将其分为两个表rails.comments和rails.users，其中始终有一个用户:

I want to split this out into two tables rails.comments and rails.users, where there is always a user:

id: primary key
users_id:  foreign key, always set.

因此，对于每个drupal.comment，我需要从drupal.comments.name/drupal.comments.email创建一个新用户，并创建一个rails.comment，其中rails.comment.users_id是正义的ID.创建的用户.

So, for each drupal.comment, I need to create either a new user from the drupal.comments.name/drupal.comments.email and a rails.comment where the rails.comment.users_id is the ID of the just created user.

或者，如果rails.user的用户名/电子邮件已经存在，我需要获取该users_id并将其用于新注释记录作为外键.

Or, if username/email already exists for a rails.user, I need to fetch that users_id and use that on the new comment record as foreign key.

或者，如果设置了drupal.comment.uid，则需要将其用作users_id.

Or, if drupal.comment.uid is set, I need to use that as users_id.

这在SQL中可行吗? 是否可以从一个源中获取查询，但是可以用SQL填充多个表?还是有一些(My)SQL技巧可以实现这一点?还是应该只使用Ruby，PHP或其他某种语言编写脚本?

Is this possible in SQL? Are queries that fetch from one source, but fill multiple tables possible in SQL? Or is there some (My)SQL trick to achieve this? Or should I simply script this in Ruby, PHP or some other language instead?

推荐答案

我进一步搜索后发现，显然是

I searched further and found that, apparently, it is not possible to update/insert more then one table in a single query in MySQL.

因此，该解决方案必须在SQL之外进行脚本编写/编程.

The solution would, therefore have to be scripted/programmed outside of SQL.

这篇关于用外键将表拆分成两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！