单个查询“如果找不到列值，则INSERT INTO表”

How can I use a single query for inserting table when a column value is not found. eg/ i want to insert new user only when this username not found

如果找不到列值,如何使用单个查询插入表。例如/我想只在未找到此用户名时插入新用户

what i doing now is issue 1 query to check for existing, then another query if no existing found. total 2 query

我现在正在做的是问题1查询检查现有,然后另一个查询,如果没有找到现有。共2个查询

 INSERT INTO friends (memberID) SELECT 1 WHERE NOT EXISTS (SELECT memberID FROM friends WHERE memberID =  1)

3 个解决方案

#1

You just need to add FROM DUAL

您只需要添加FROM DUAL

INSERT INTO friends 
            (memberid) 
SELECT 1 
FROM   dual 
WHERE  NOT EXISTS (SELECT memberid 
                   FROM   friends 
                   WHERE  memberid = 1)

sql fiddle

#2

How about this:

这个怎么样:

INSERT INTO YourTable (UserName)
SELECT x
FROM (SELECT 'New User Name' AS x) a
      WHERE x NOT IN(SELECT UserName
                     FROM YourTable)

#3

Since you only want one row with a given value you should enforce that with a UNIQUE constraint on the table, for example:

由于您只需要一个具有给定值的行,因此您应该在表上使用UNIQUE约束强制执行该操作,例如:

ALTER TABLE friends ADD UNIQUE (memberID);

After you do that, you can simply add the IGNORE keyword to your insert statements and it won't report an error and it won't insert a duplicate row if it already exists.

执行此操作后,您只需将IGNORE关键字添加到insert语句中,它就不会报告错误,如果已存在,则不会插入重复的行。

INSERT IGNORE INTO friends(memberID) VALUES(1);

#1