Início / dba / Perguntas / 326042
Accepted
Sergey Zolotarev
Asked: 2023-04-16 05:24:54 +0800 CST

Como preencher uma tabela de junção com uma instrução SQL no MySQL?

  • 772

eu tenho essas mesas

CREATE TABLE users
(
    id         INT          NOT NULL AUTO_INCREMENT,
    username   VARCHAR(15)  NOT NULL UNIQUE,
    password   VARCHAR(200) NOT NULL,
    name       VARCHAR(15)  NOT NULL,
    last_name  VARCHAR(25)  NOT NULL,
    department VARCHAR(50)  NOT NULL,
    salary     INT,
    age        TINYINT      NOT NULL,
    email      VARCHAR(50),
    enabled    TINYINT      NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE roles
(
    id   INT         NOT NULL AUTO_INCREMENT,
    role VARCHAR(15) NOT NULL UNIQUE,
    PRIMARY KEY (id)
);

e também uma tabela de junção entre eles

CREATE TABLE user_role
(
    user_id  INT         NOT NULL,
    username VARCHAR(15) NOT NULL,
    role_id  INT         NOT NULL,
    role     VARCHAR(15) NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users (id),
    FOREIGN KEY (role_id) REFERENCES roles (id)
);

Como posso preenchê-lo com uma instrução SQL?

Suponha que a userstabela tenha esses valores (os ids são gerados automaticamente)

VALUES ('mickey_m', '$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
        'Mickey', 'Mouse', 'sales', 180000, 95, '[email protected]', 1),

       ('donald_d', '$2y$10$E6SHpAN0aZGQ43HAO.TPiO27kDKXOGIgDc8xWDJdl2Prn2wzQzz5y',
       'Donald', 'Duck', 'information technology', 190000, 89, '[email protected]', 1),

       ('scrooge_m', '$2a$10$ycVpl2BSD6o19wa3xXtmrOxc8qZjwoIk.e3oZqgQBOE.3NHANAYqa',
       'Scrooge', 'McDuck', 'board of directors', 700000, 76, '[email protected]', 1)

E suponha que eu queira que todos tenham um USERpapel e scrooge_mtambém tenham um ADMINpapel. A tabela de junção deve ser mais ou menos assim (mais ids que talvez eu não saiba no momento de escrever a instrução SQL)

ID do usuário nome de usuário role_id papel
mickey_m DO UTILIZADOR
donald_d DO UTILIZADOR
scrooge_m DO UTILIZADOR
scrooge_m ADMINISTRADOR

Minha tentativa aparentemente ruim está abaixo (não testei, mas parece errado – embora o IDE não destaque nada aqui). Eu costumava INSERT IGNOREcontar com a possibilidade de que as linhas já estivessem lá

INSERT IGNORE INTO user_role
WITH user_to_role(user, role) AS ( VALUES ROW('mickey_m', 'USER'),
                              ROW('donald_d', 'USER'),
                              ROW('scrooge_m', 'USER'),
                              ROW('scrooge_m', 'ADMIN')
SELECT users.id, users.username, roles.id, roles.role FROM users JOIN
    roles ON users.username = user_to_role.user AND roles.role = user_to_role.role;

Quando eu executo no dbfiddle , recebo

Unknown column 'user_to_role.user' in 'on clause'
mysql

2 respostas

  1. Se as únicas coisas forem roleso id e o papel, livre-se da tabela.

    Então, em vez de discutir uma tabela muitos-para-muitos, é simplesmente uma tabela 1:muitos:

    CREATE TABLE roles (
    user_id ...
    role VARCHAR ...
    PRIMARY KEY(user_id, role),
    INDEX(role)
) ENGINE=InnoDB;

    Não inclua usernamenessa tabela; (ou seu original roles), é redundante. Em vez disso, use a JOINsempre que precisar de outras colunas.

    Ao inserir em uma tabela com um AUTO_INCREMENT, faça uma linha por vez para obter o id:

    INSERT INTO users (username, ...)
    VALUES
    ('mickey_m','...',  ', 'Mickey', 'Mouse', 'sales', 180000, 95, '[email protected]', 1);
SELECT @lii := LAST_INSERT_ID();
INSERT INTO roles (user_id, role)
    VALUES
    (@lii, 'USER'),
    (@lii, 'HACKER');

    Use INSERT IGNOREse for provável que você esteja reinserindo. Use UPDATEou INSERT ... ON DUPLICATE KEY UPDATEpara fazer alterações sem ou com a opção de também inserir.

    • 1
  2. Best Answer

    Isso parece funcionar:

    INSERT IGNORE INTO user_role (user_id, username, role_id, role)
SELECT u.id, u.username, r.id, r.role
FROM (
  VALUES
  ROW('mickey_m', 'USER'),
  ROW('donald_d', 'USER'),
  ROW('scrooge_m', 'USER'),
  ROW('scrooge_m', 'ADMIN')
) AS user_to_role (username, role)
JOIN users AS u ON u.username = user_to_role.username
JOIN roles AS r ON r.role = user_to_role.role;

    Usar pseudônimos parece opcional (acredito que pseudônimos são sempre opcionais). Isso também funciona:

    INSERT IGNORE INTO user_role (user_id, username, role_id, role)
SELECT users.id, users.username, roles.id, roles.role
FROM (
         VALUES
             ROW('mickey_m', 'USER'),
             ROW('donald_d', 'USER'),
             ROW('scrooge_m', 'USER'),
             ROW('scrooge_m', 'ADMIN')
     ) AS user_to_role (username, role)
         JOIN users ON users.username = user_to_role.username
         JOIN roles ON roles.role = user_to_role.role;

    Você ainda está livre para postar sua própria solução.

    O engraçado é que meus scripts não parecem alterar as propriedades NOT NULLou UNIQUEdas minhas colunas. Mas isso provavelmente tem a ver com o meu application.propertiese não está mais no tópico. - Serguei

    • 0

relate perguntas