主页 / dba / 问题 / 326042
Accepted
Sergey Zolotarev
Asked: 2023-04-16 05:24:54 +0800 CST

如何在 MySQL 中用一条 SQL 语句填充连接表？

  • 772

我有这些桌子

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)
);

还有它们之间的连接表

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)
);

如何用一条SQL语句来填充它？

假设，users表有这些值（ids是自动生成的）

VALUES ('mickey_m', '$2y$10$i5SJc2KAriHGn7Dz2rRQHuQ3JfBxlzMaPVKP1YdEJCukryDY9NbVC',
        'Mickey', 'Mouse', 'sales', 180000, 95, 'mickey_m@gmail.com', 1),

       ('donald_d', '$2y$10$E6SHpAN0aZGQ43HAO.TPiO27kDKXOGIgDc8xWDJdl2Prn2wzQzz5y',
       'Donald', 'Duck', 'information technology', 190000, 89, 'donald_d@outlook.com', 1),

       ('scrooge_m', '$2a$10$ycVpl2BSD6o19wa3xXtmrOxc8qZjwoIk.e3oZqgQBOE.3NHANAYqa',
       'Scrooge', 'McDuck', 'board of directors', 700000, 76, 'scrooge@gmail.com', 1)

假设，我希望每个人都有一个USER角色，并且scrooge_m也都有一个ADMIN角色。连接表应该看起来像这样（加上id我在编写 SQL 语句时可能不知道的 s）

用户身份 用户名 角色 ID 角色
米奇_m 用户
唐纳德_d 用户
斯克鲁奇_m 用户
斯克鲁奇_m 行政

我的明显错误尝试如下（我没有测试它，但它看起来是错误的 - 不过，IDE 在这里没有突出显示任何内容）。我曾经INSERT IGNORE考虑过这些行可能已经存在的可能性

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;

当我在dbfiddle上运行它时，我得到

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

2 个回答

  1. 如果其中只有rolesid 和角色，则删除该表。

    然后，不再讨论多对多表，而是简单的 1:多表：

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

    不要包含username在该表中；（或你原来的roles），它是多余的。相反，JOIN当您需要其他列时，请使用 a 。

    当使用 插入到表中时AUTO_INCREMENT，一次执行一行，这样您就可以获得 id：

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

    INSERT IGNORE如果您可能要重新插入，请使用。使用UPDATE或INSERT ... ON DUPLICATE KEY UPDATE进行更改，不带或带插入选项。

    • 1
  2. Best Answer

    这似乎有效：

    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;

    使用别名似乎是可选的（我相信别名始终是可选的）。这也有效：

    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;

    您仍然可以自由地发布您自己的解决方案。

    有趣的是我的脚本似乎没有改变我的列的NOT NULL属性UNIQUE。但这可能与我有关application.properties，不再是主题。-谢尔盖

    • 0

相关问题