php - MySQL在一个事务中从多对多表中删除和删除

Question

是否可以在同一事务中从关联表(多对多)中删除并插入同一行？

这是我的关联表:

CREATE TABLE image_tag (
  imageid bigint(19) NOT NULL, 
  tagid   bigint(19) NOT NULL, 
  PRIMARY KEY (imageid, tagid));
ALTER TABLE image_tag ADD INDEX FKimage_tag587679 (tagid), ADD CONSTRAINT  FKimage_tag587679 FOREIGN KEY (tagid) REFERENCES tag (id);
ALTER TABLE image_tag ADD INDEX FKimage_tag426448 (imageid), ADD CONSTRAINT FKimage_tag426448 FOREIGN KEY (imageid) REFERENCES image (id);

当我在一个事务中创建新图像并插入图像、插入标签并插入 image_tag 时，一切正常。

当我想更新图像时出现问题，并且我:

• 开始交易
• 使用给定的 $id 更新图像
• 调用:DELETE FROM image_tag WHERE imageid = $id
• 检查 imageid = $id 的 image_tag 是否为空(确实如此)
• 检索新标签的 ID(其中一些保持不变)
• 尝试插入 image_tag ...但我收到异常

异常(exception):

Cannot add or update a child row: a foreign key constraint fails (`mydatabase`.`image_tag`, CONSTRAINT `FKimage_tag426448` FOREIGN KEY  (`imageid`) REFERENCES `image` (`id`))  

数据:

Original data: (194, 123), (194, 225), (194, 291)
New data: (194, 123), (194, 225), (194, 65)

源代码(PHP):

if ($editation) {
  if ($values->image->isOk()) {
    $url = $img->load($values->id)->getUrl();
    $values->image->move($this->wwwDir . "/" . $url);
  } else {
    $url = null;
  }      
  $img->update($values->name, $values->descr, $url, null, $featured);
} else {
  $uname = $img->createUniqueName($values->name);
  $url = ImageManager::PATH . "/" . $uname . ".svg";
  $values->image->move($this->wwwDir . "/" . $url);
  $img->create($userId, $values->name, $url, $values->descr, null, $featured);      
}

// Delete old image-tag associations if any
$this->db->query('DELETE FROM image_tag WHERE imageid = %i', $img->getId());

// Save tags and assign them to image
$tagIds = $this->tagManager->saveTags($values->tags);
$assoc = (new Dao\ImageTag())->setDb($this->db);
//throw new \Exception(implode(",", $tagIds));
foreach ($tagIds as $tagId) {     
  // EXCEPTION IS THROWED HERE (but only after editation)
  $assoc->create($img->getId(), $tagId);      
}

Answer 1

您是否尝试使用无效 ID 更新您的 image_tag ？ ..null、0、-1 还是其他？

查看您的代码。我认为 $img 和 $values->image 不一样，但只有在提供 $values->image 时才加载 $img 。但是，您每次都尝试更新 image_tag。