mysql - 使用 INNER JOIN 在重复键上插入

Question

给定t.id、a.id、t1.name和t2.name，如何做我添加或更新 t1_has_t2.data？

如果当前有记录，我可以更新它。

UPDATE t1_has_t2
INNER JOIN t1 ON t1.id=t1_has_t2.t1_id
INNER JOIN t2 ON t2.id=t1_has_t2.t2_id
SET t1_has_t2.data=123
WHERE t1.name="foo" AND t1.t_id=333 AND t2.name="bar" AND t2.t_id=333;

如果当前不存在记录，如何插入？

编辑。会是像下面这样的吗？在 JOIN 中包含 t 似乎是一种浪费。

INSERT INTO t1_has_t2(t1_id,t2_id,data)
SELECT t1.id, t2.id, 123
FROM t
INNER JOIN t1 ON t1.t_id=t.id
INNER JOIN t2 ON t2.t_id=t.id
WHERE t1.name="foo" AND t1.t_id=333 AND t2.name="bar" AND t2.t_id=333
ON DUPLICATE KEY SET t1_has_t2.data=123;

编辑2。啊，也许我现在明白了。我只是通过 t1 和 t2 共享的 t.id 将 t1 和 t2 相互加入？

INSERT INTO t1_has_t2(t1_id,t2_id,data)
SELECT t1.id, t2.id, 123
FROM t1
INNER JOIN t2 ON t2.t_id=t1.t_id
WHERE t1.name="foo" AND t1.t_id=333 AND t2.name="bar" AND t2.t_id=333
ON DUPLICATE KEY UPDATE t1_has_t2.data=123;

-- MySQL Script generated by MySQL Workbench
-- 08/08/16 07:40:04
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`accounts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`accounts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `accounts_id` INT NOT NULL,
  PRIMARY KEY (`id`, `accounts_id`),
  INDEX `fk_t_accounts_idx` (`accounts_id` ASC),
  CONSTRAINT `fk_t_accounts`
    FOREIGN KEY (`accounts_id`)
    REFERENCES `mydb`.`accounts` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `t_id` INT NOT NULL,
  `t_accounts_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_t1_t1_idx` (`t_id` ASC, `t_accounts_id` ASC),
  UNIQUE INDEX `un1` (`t_id` ASC, `name` ASC),
  CONSTRAINT `fk_t1_t1`
    FOREIGN KEY (`t_id` , `t_accounts_id`)
    REFERENCES `mydb`.`t` (`id` , `accounts_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t2` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `t_id` INT NOT NULL,
  `t_accounts_id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_t2_t1_idx` (`t_id` ASC, `t_accounts_id` ASC),
  UNIQUE INDEX `un2` (`t_id` ASC, `name` ASC),
  CONSTRAINT `fk_t2_t1`
    FOREIGN KEY (`t_id` , `t_accounts_id`)
    REFERENCES `mydb`.`t` (`id` , `accounts_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`t1_has_t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1_has_t2` (
  `t1_id` INT NOT NULL,
  `t2_id` INT NOT NULL,
  `data` VARCHAR(45) NULL,
  PRIMARY KEY (`t1_id`, `t2_id`),
  INDEX `fk_t1_has_t2_t21_idx` (`t2_id` ASC),
  INDEX `fk_t1_has_t2_t11_idx` (`t1_id` ASC),
  CONSTRAINT `fk_t1_has_t2_t11`
    FOREIGN KEY (`t1_id`)
    REFERENCES `mydb`.`t1` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_t1_has_t2_t21`
    FOREIGN KEY (`t2_id`)
    REFERENCES `mydb`.`t2` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

如果我明白你想要什么 Insert on Duplicate Key Update 就可以了(IODKU)。

数据加载:

insert accounts(id) values (NULL); -- id = 1
insert t(accounts_id) values (1); -- id = 1

insert t1(t_id,t_accounts_id,name) values (1,1,'n1'); -- id=1
insert t1(t_id,t_accounts_id,name) values (1,1,'n2'); -- id=2
insert t2(t_id,t_accounts_id,name) values (1,1,'n1'); -- id=1   
insert t2(t_id,t_accounts_id,name) values (1,1,'n2'); -- id=2

insert t1_has_t2(t1_id,t2_id,data) values(1,1,'one_one'); -- success
insert t1_has_t2(t1_id,t2_id,data) values(1,77,'x'); -- Error 1452 as expected
insert t1_has_t2(t1_id,t2_id,data) values(77,1,'x'); -- Error 1452 as expected
insert t1_has_t2(t1_id,t2_id,data) values(1,2,'one_two'); -- success
insert t1_has_t2(t1_id,t2_id,data) values(2,1,'two_one'); -- success
insert t1_has_t2(t1_id,t2_id,data) values(2,2,'two_two'); -- success

您的查询:

UPDATE t1_has_t2
INNER JOIN t1 ON t1.id=t1_has_t2.t1_id
INNER JOIN t2 ON t2.id=t1_has_t2.t2_id
SET t1_has_t2.data='I am a string'
WHERE t1.name="n1" AND t1.t_id=1 AND t2.name="n1" AND t2.t_id=1;

IODKU:

insert t1_has_t2(t1_id,t2_id,data) values(2,2,'two_two_version002')
on duplicate key update data='anchovies';

查看结果:

select * from t1_has_t2;
+-------+-------+---------------+
| t1_id | t2_id | data          |
+-------+-------+---------------+
|     1 |     1 | I am a string |
|     1 |     2 | one_two       |
|     2 |     1 | two_one       |
|     2 |     2 | anchovies     |
+-------+-------+---------------+

您还可以查看

insert ignore t1_has_t2(t1_id,t2_id,data) [something];

设计时会默默地成功或失败。