mysql查询插入其中不存在 [英] mysql query insert where not exists
本文介绍了mysql查询插入其中不存在的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我搜索一种在数据库(MySQL)中输入数据的方法,如果它们在DB中不存在:例如:
INSERT INTO TABLE(id,name,surname)VALUES('','name1','surname1')
pre>
WHERE NOT EXISTS(SELECT * FROM TABLE)
这个查询有什么问题?
解决方案对于上面的查询,可以使用
NOT EXISTS
,可以使用子查询。这里有一种方法使用LEFT JOIN
和一个子查询:INSERT INTO yourtable(name,surname)
SELECT a.name,a.surname
FROM
(SELECT'name1'name,'surname1'surname)a
LEFT b ON
a.name = b.name AND a.surname = b.surname
WHERE b.name IS NULL;
而
NOT EXISTS
方法:INSERT INTO yourtable(name,surname)
SELECT a.name,a.surname
FROM
(SELECT'name1'name,'surname1'surname)a
WHERE NOT EXISTS(
SELECT 1
FROM yourtable
WHERE name ='name1'and surname ='surname1')
I search a way to enter datas in database(MySQL),if they dont exist yet in DB:something like:
INSERT INTO TABLE(id,name,surname) VALUES('','name1','surname1') WHERE NOT EXISTS (SELECT * FROM TABLE)
What wrong with this query ?
解决方案While you can't use
NOT EXISTS
with your above query, you can with a subquery. Here's one way to do it using aLEFT JOIN
and a subquery:INSERT INTO yourtable (name, surname) SELECT a.name, a.surname FROM (SELECT 'name1' name,'surname1' surname) a LEFT JOIN yourtable b ON a.name = b.name AND a.surname = b.surname WHERE b.name IS NULL;
And the
NOT EXISTS
approach:INSERT INTO yourtable (name, surname) SELECT a.name, a.surname FROM (SELECT 'name1' name,'surname1' surname) a WHERE NOT EXISTS ( SELECT 1 FROM yourtable WHERE name = 'name1' and surname = 'surname1')
这篇关于mysql查询插入其中不存在的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文