php 有条件的 mySQL 语句。如果为真更新，如果为假插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4940283/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Conditional mySQL statement. If true UPDATE, if false INSERT
提问by Vitaliy Isikov
I'm trying to create more robust MySQLQueries and learn in the process. Currently I'm having a hard time trying to grasp the ON DUPLICATE KEYsyntax and possible uses.
我正在尝试创建更强大的MySQL查询并在此过程中学习。目前我很难掌握ON DUPLICATE KEY语法和可能的用途。
I have an INSERTQuery that I want to INSERTonly if there is no record with the same IDand name, otherwise UPDATE. IDand name are not UNIQUEbut IDis indexed.IDisn't UNIQUEbecause it references another record from another table and I want to have multiple records in this table that reference that one specific record from the other table.
我有一个INSERT查询，INSERT只有在没有相同ID和名称的记录时才想要查询，否则UPDATE. ID和名称不是UNIQUE但ID被索引。ID不是UNIQUE因为它引用了另一个表中的另一条记录，我希望该表中有多个记录引用另一个表中的一个特定记录。
How can I use ON DUPLICATE KEYto INSERTonly if there is no record with that IDand name already set else UPDATEthat record?
如何使用ON DUPLICATE KEY来INSERT仅当有与没有记录ID和名称已经设置别的UPDATE这个纪录？
I can easily achieve this with a couple of QUERIESand then have PHPdo the IFELSEpart, but I want to know how to LIMITthe amount of QUERIESI send to MySQL.
我可以很容易地与一对夫妇的做到这一点QUERIES，然后有PHP做的IFELSE一部分，但我想知道如何LIMIT量QUERIES我送MySQL。
回答by
UPDATE:Note you need to use IF EXISTSinstead of IS NULLas indicated in the original answer.
更新：请注意，您需要使用IF EXISTS而不是IS NULL原始答案中的指示。
Code to create stored procedure to encapsulate all logic and check if Flavours exist:
创建存储过程以封装所有逻辑并检查 Flavors 是否存在的代码：
DELIMITER //
DROP PROCEDURE `GetFlavour`//
CREATE PROCEDURE `GetFlavour`(`FlavourID` INT, `FlavourName` VARCHAR(20))
BEGIN
IF EXISTS (SELECT * FROM Flavours WHERE ID = FlavourID) THEN
UPDATE Flavours SET ID = FlavourID;
ELSE
INSERT INTO Flavours (ID, Name) VALUES (FlavourID, FlavourName);
END IF;
END //
DELIMITER ;
ORIGINAL:
原来的：
You could use this code. It will check for the existence of a particular record, and if the recordset is NULL, then it will go through and insert the new record for you.
您可以使用此代码。它将检查特定记录是否存在，如果记录集为 NULL，则它将通过并为您插入新记录。
IF (SELECT * FROM `TableName` WHERE `ID` = 2342 AND `Name` = 'abc') IS NULL THEN
INSERT INTO `TableName` (`ID`, `Name`) VALUES ('2342', 'abc');
ELSE UPDATE `TableName` SET `Name` = 'xyz' WHERE `ID` = '2342';
END IF;
I'm a little rusty on my MySQL syntax, but that code should at least get you most of the way there, rather than using ON DUPLICATE KEY.
我对我的 MySQL 语法有点生疏，但是该代码至少应该可以让您完成大部分工作，而不是使用 ON DUPLICATE KEY。
回答by mluebke
id and name are not unique but id is indexed. id isn't unique
How can I use ON DUPLICATE KEY to INSERT only if there is no record with that id and name already set else UPDATE that record?
id 和 name 不是唯一的，但 id 被索引。id 不是唯一的
仅当没有具有该 id 和名称的记录时，我如何才能使用 ON DUPLICATE KEY 插入，否则更新该记录？
You can't. ON DUPLICATE KEY UPDATE needs a unique or primary key to determine which row to update. You are better off having PHP do the IF ELSE part.
你不能。ON DUPLICATE KEY UPDATE 需要一个唯一键或主键来确定要更新的行。您最好让 PHP 执行 IF ELSE 部分。
edit:
编辑：
If the combination of name and id IS supposed to be unique, you can create a multi-column UNIQUE index. From there you can use ON DUPLICATE KEY UPDATE.
如果 name 和 id 的组合应该是唯一的，则可以创建多列 UNIQUE 索引。从那里您可以使用 ON DUPLICATE KEY UPDATE。
回答by dcp
Why not just use a stored procedure, then you can embed all the logic there are plus you have a reusable piece of code (e.g. the stored proc) that you can use in other applications. Finally, this only requires one round trip to the server to call the stored proc.
为什么不只使用存储过程，那么您可以嵌入所有逻辑，并且您拥有可在其他应用程序中使用的可重用代码段（例如存储过程）。最后，这只需要到服务器来一次调用存储过程。
