mysql——选择并插入查询到具有条件的多个表中

I dont know it is possible or not in MySQl, but I have to import the old database table to new normalized database table. I have 2 table user and user_roles in new one and the previous one only has admin

我不知道在MySQl中是否可行，但我必须将旧的数据库表导入新的规范化数据库表。我在新的表中有两个表用户和user_roles，而前一个表只有admin

newdb.users Table
user_id | username | password

newdb.user_roles Table
user_id | role_id

old database table

旧的数据库表

olddb.admin Table
id | username | password | user_type
1  | user1    | ***      | admin
3  | user7    | ***      | staff

admin role_id is 1 and staff role_id is 2

管理员role_id为1,role_id为2。

So the new table after SQL insert should look like.

因此，SQL insert之后的新表应该是这样的。

newdb.users Table
user_id | username | password
1       | user1    | ***   
3       | user7    | ***   

newdb.user_roles Table
user_id | role_id
1       |  1
3       |  2

How to do this using MYSQL query. Thanks.

如何使用MYSQL查询来实现这一点。谢谢。

2 个解决方案

#1

You should be basing this information on a roles table:

您应该将此信息基于角色表:

create table roles (
    int id auto_increment primary key,
    role_name varchar(255)
);

insert into roles (role_name)
    select distinct user_type
    from old.admin;

Then:

然后:

insert into new.users(username, password)
    select distinct username, password
    from old.admin;

(distinct is not necessary if username is not repeated in original table.)

(如果用户名在原始表中没有重复，则不需要区分。)

And:

和:

insert into user_roles(user_id, role_id)
    select u.id, r.id
    from new.users u join
         old.admin a
         on u.user_name = a.user_name and
            u.password = a.password join  -- this condition may not be necessary
         new.roles r
         on a.user_type = r.role_name;

Although you can hard-code values into the query, I think it is a good idea to let the database do the work for you. This approach is more general.

虽然可以将值硬编码到查询中，但我认为最好让数据库为您做这些工作。这种方法更为普遍。

#2

In order to insert roles from old table to new table:

为了将角色从旧表插入到新表:

INSERT INTO newdb.user_roles 

SELECT 
id,
CASE WHEN user_type='admin' THEN 1
     WHEN user_type ='staff' THEN 2 END AS role_id
FROM olddb.admin

In order to insert users from old table to new table:

为了将用户从旧表插入到新表:

INSERT INTO newdb.users
SELECT 
id,
username,
password
FROM olddb.admin

#1