将一个表拆分为两个连接表
现在我有一个 mysql 数据库，其中有一个表userphone(id,phone,type,userid)，我想将其分成两个表，phone(id,phone,type) 和 user_phone(id,user_id,phone_id)。
我这样做是因为我还将有其他也涉及电话号码的内容，并且它们应该全部存储在一张表中。 （例如，我还将添加一个 company_phone(id,company_id,phone_id) 连接表来连接公司及其电话号码）。
有什么好的、有效的方法可以做到这一点吗？我已经添加了新表，但不确定填充它们的最佳方法。
我可以将 userphone 中的所有电话添加到 phone 表中，
INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;
但不会在新的 user_phone 表中添加条目每个电话。
有没有办法同时INSERT INTO两个不同的表？ （根据研究，我知道没有，但我不知道如何实现这一点......）
提前致谢！
Right now I have a mysql database with a table userphone(id, phone, type,userid) and I'd like to split it into two tables, phone(id,phone,type) and user_phone(id,user_id,phone_id).
I'm doing this because I'm going to have other things that also refer to phone numbers, and they ought to be all stored in one table. (e.g. I'll also be adding a company_phone(id,company_id,phone_id) join table to join companies with their phone numbers).
Is there any good, efficient way of doing this? I've added the new tables, but am not sure of the best approach for populating them.
I can add all the phones from userphone into the phone table with something like
INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;
but that doesn't add an entry into the new user_phone table for each phone.
Is there any way to INSERT INTO two different tables at once? (from research, I know there isn't, but I can't figure out how else to accomplish this...)
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
创建表 user_phone 为
将表 user_phone 创建为
alter table phone drop column user_idCreate the table user_phone as
Create your table user_phone as
alter table phone drop column user_id第一个：
插入电话（电话，类型）选择电话，从用户电话中输入；
第二：
选择u.USER_ID、p.phone_ID
来自用户电话 u
INNER JOIN 电话号码
ON u.phone = p.phone
并且 u.type = p.type
first:
INSERT INTO phone(phone,type) SELECT phone,type FROM userphone;
second:
select u.USER_ID, p.phone_ID
from userPhone u
INNER JOIN Phone p
ON u.phone = p.phone
and u.type = p.type