在没有主键的地方插入 [英] Insert Where Not Exists-Without Primary Key

问题描述

我有3个表:牙医，分组和groupdentlink.许多牙医通过groupdentlink表链接到许多组.

I have 3 tables: dentists, groups, and groupdentlink. Many dentists link to many groups through the groupdentlink table.

因此，我尝试进行查询，在该查询中将行插入groupdentlink(将状态中的所有牙医与状态中的所有组链接起来)，但前提是这些行尚不存在.简而言之，我想添加新行而不覆盖现有行或复制它们.

So I'm trying to make a query where it will insert rows into groupdentlink (linking all dentists in the state with all the groups in the state) but only if those rows don't already exist. In a nutshell I want to add new rows without overwriting existing ones or duplicating them.

因此查询的意图类似于:

INSERT INTO groupdentlink (f_dent_id, f_group_id, f_schedule_id)
VALUES ('$_POST[id]', '$groupid', '$scheduleid')
WHERE NOT EXISTS ('$_POST[id]', '$groupid')

而且groupdentlink表中没有任何主键.

And I don't have any primary keys in the groupdentlink table.

提前谢谢！

推荐答案

如果您真的想编写自己的(有效的)查询..

---

INSERT INTO groupdentlink (
  f_dent_id, f_group_id, f_schedule_id
) SELECT 
    '$_POST[id]'  f_dent_id, 
    '$groupid'    f_group_id,
    '$scheduleid' f_schedule_id
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1
  FROM `groupdentlink`
  WHERE 
    f_dent_id = '$_POST[id]' AND f_group_id = '$groupid'
  LIMIT 1 -- will stop mysql to stop searching after first match
)

...但是MySQL可以为您处理所有这一切！

---

您不需要主键来使MySQL为您处理此问题，您应该在两列的组合集上添加UNIQUE键约束.

... but MySQL can handle all this for you!

---

You don't need primary keys to make MySQL handle this for you, you should add a UNIQUE key constraint on the combined set of the two columns.

查询将唯一键dent_group_uniq_key添加到groupdentlink.

ALTER TABLE groupdentlink ADD UNIQUE KEY `dent_group_uniq_key` (
  f_dent_id, f_group_id
);

然后在查询中使用INSERT IGNORE:

INSERT IGNORE INTO groupdentlink (
  f_dent_id, f_group_id, f_schedule_id
) VALUES (
  '$_POST[id]', '$groupid', '$scheduleid'
)

INSERT IGNORE将尝试在表中插入一行，如果由于键约束而失败，它将像什么都没有发生一样.

INSERT IGNORE will try to insert a row to your table, if it fails due to a key constraint it will act like nothing happen.

这篇关于在没有主键的地方插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！