I have an Excel sheet with around 2.000 rows that I want to insert into my database.
我有一个Excel工作表,大约有2.000行,我想插入到我的数据库中。
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.
问题是我要插入2.000行的表有一个引用另一个表中的外键的列。不幸的是,很多查询都失败了,因为提供的外键值不存在。
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.
第一个查询失败,因为TEST.id引用foobar.id并且没有foobar.id = 10.但是,第二个查询将起作用,因为foobar.id = 20存在,但第二个查询将不会执行,因为第一个已经失败了。
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.
我可以写一个PHP脚本,但我更喜欢这里的MySQL解决方案。
1 个解决方案
#1
4
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;
#1
4
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;