问题描述
我有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.
这篇关于在没有主键的地方插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!