问题描述
我正在尝试创建更强大的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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!