MySQL:如果外键存在,则插入 [英] MySQL: Insert if foreign key exists
问题描述
问题是,我想插入2.000行的表格到我的数据库中。有一个引用另一个表中的外键的列。不幸的是,很多查询失败,因为提供的外键值不存在。
我知道我可以忽略外键检查,但这不是我想要的。我不想忽略外键检查,我只是希望执行不好的查询。
示例:
INSERT INTO test(id,value)VALUES(10,20);
INSERT INTO test(id,value)VALUES(20,20);
第一个查询失败,因为 TEST.id
引用 foobar.id
并且没有 foobar.id = 10。
但是,第二个查询可以工作, foobar.id = 20
存在,但是第二个查询不会被执行,因为第一个查询已经失败。
有什么办法可以避免第一个查询出错,而其他查询仍然会被执行?
我可以编写一个php脚本,但是我更喜欢这里的一个MySQL解决方案。
插入select的结果,如下所示:
INSERT INTO test(id,value)
SELECT foobar。 id,20
从foobar WHERE id = 10;
I have an Excel sheet with around 2.000 rows that I want to insert into my database.
The problem is that the table I want to insert the 2.000 rows into has a column that references to a foreign key in another table. Unfortunately a lot of queries fail, since the provided foreign key value does NOT exist.
I know that I can ignore foreign key checks, but this is not what I want. I don't want to ignore foreign key checks, I just want bad queries not to be executed.
Example:
INSERT INTO test (id, value) VALUES (10, 20);
INSERT INTO test (id, value) VALUES (20, 20);
The first query fails, since TEST.id
references foobar.id
and there is no foobar.id = 10.
However, the second query would work, since foobar.id = 20
exists, but the second query won't be executed, because the first one already failed.
Is there any way I don't get an error on the first query and my other queries will still be executed?
I could write a php script, but I'd prefer a MySQL solution here.
You could change to insert the result of a select, so something like:
INSERT INTO test (id, value)
SELECT foobar.id, 20
FROM foobar WHERE id = 10;
这篇关于MySQL:如果外键存在,则插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!