有条件的mySQL语句.如果为真UPDATE，如果为假INSERT [英] Conditional mySQL statement. If true UPDATE, if false INSERT

问题描述

我正在尝试创建更强大的MySQL查询并在此过程中学习.目前，我很难理解ON DUPLICATE KEY语法和可能的用途.

I'm trying to create more robust MySQL Queries and learn in the process. Currently I'm having a hard time trying to grasp the ON DUPLICATE KEY syntax and possible uses.

我有一个INSERT查询，仅当没有与ID和名称相同的记录时才要INSERT，否则为UPDATE. ID和名称不是UNIQUE，但已索引ID.ID不是UNIQUE，因为它引用了另一个表中的另一条记录，并且我希望该表中有多条记录引用该特定记录在另一张桌子上.

I have an INSERT Query that I want to INSERT only if there is no record with the same ID and name, otherwise UPDATE. ID and name are not UNIQUE but ID is indexed.ID isn't UNIQUE because 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.

仅当没有具有ID的记录并且名称已经设置为其他UPDATE记录时，才可以使用ON DUPLICATE KEY到INSERT?

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?

我可以通过几个QUERIES轻松实现此目标，然后让PHP做IF ELSE部分，但是我想知道如何LIMIT我发送到的QUERIES数量MySQL.

I can easily achieve this with a couple of QUERIES and then have PHP do the IF ELSE part, but I want to know how to LIMIT the amount of QUERIES I send to MySQL.

推荐答案

更新:请注意，您需要使用IF EXISTS而不是原始答案中所示的IS NULL.

UPDATE: Note you need to use IF EXISTS instead of IS NULL as indicated in the original answer.

用于创建存储过程以封装所有逻辑并检查Flavors是否存在的代码:

Code to create stored procedure to encapsulate all logic and check if Flavours exist:

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 ;

原始:

您可以使用此代码.它将检查特定记录的存在，如果记录集为NULL，则它将遍历并为您插入新记录.

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.

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;

我对我的MySQL语法有些生疏，但是该代码至少应该让您了解大部分内容，而不是使用ON DUPLICATE KEY.

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语句.如果为真UPDATE，如果为假INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！