我想从表存储和表用户插入数据存储器

Question

我有三个表： store， users和 storeuser。

表storeuser具有store和users表的外国键。

这三个表具有ID列为storeid，userid，suserid，并且正在自动插入上述ID列。

现在，我想从表store和表user插入数据storeuser：

CREATE TABLE `store2`.`store` (
  `storeid` INT(10) NOT NULL AUTO_INCREMENT,
  `sname` CHAR(45) NOT NULL,
  `stype` CHAR(45) NOT NULL,
  `location` CHAR(45) NULL,
  PRIMARY KEY (`storeid`));
CREATE TABLE `store2`.`users` (
  `userid` INT(10) NOT NULL AUTO_INCREMENT,
  `uname` CHAR(45) NOT NULL,
  `age` SMALLINT(5) NOT NULL,
  PRIMARY KEY (`userid`));
CREATE TABLE `store2`.`storeuser` (
  `suserid` INT(10) NOT NULL,
  `storeid` INT(10) NOT NULL,
  `userid` INT(10) NOT NULL,
  PRIMARY KEY (`suserid`),
  INDEX `storeid_idx` (`storeid` ASC),
  INDEX `userid_idx` (`userid` ASC),
  CONSTRAINT `storeid`
    FOREIGN KEY (`storeid`)
    REFERENCES `store2`.`store` (`storeid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `userid`
    FOREIGN KEY (`userid`)
    REFERENCES `store2`.`users` (`userid`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

Answer 1

您的请求很奇怪，或者也许我不正确理解。

基本上，您想为每对StoreID＆amp;UserId(再次自动插入(彼此无关？

我不确定我是否很好地了解您，但是根据我的理解，这是一个解决方案：

mysql> select * from store;
+---------+-------+-------+----------+
| storeid | sname | stype | location |
+---------+-------+-------+----------+
|       1 | a     | b     | c        |
|       2 | a1    | b2    | c3       |
+---------+-------+-------+----------+
mysql> select * from users;
+--------+-------+-----+
| userid | uname | age |
+--------+-------+-----+
|      1 | 1     |   2 |
|      2 | 3     |   4 |
+--------+-------+-----+
mysql> desc storeuser;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| suserid | int(10) | NO   | PRI | NULL    | auto_increment |
| storeid | int(10) | NO   | MUL | NULL    |                |
| userid  | int(10) | NO   | MUL | NULL    |                |
+---------+---------+------+-----+---------+----------------+

现在，我将插入物插入店用户：

mysql> insert into storeuser select '',storeid, userid from store join      
users on store.storeid=users.userid;

插入后：

mysql> select * from storeuser;
+---------+---------+--------+
| suserid | storeid | userid |
+---------+---------+--------+
|       1 |       1 |      1 |
|       2 |       2 |      2 |
+---------+---------+--------+

这是您需要的吗？