我在MySQL数据库中有一个表,如果实例还不存在,则需要更新该表。我尝试过以下方法,但没有更接近于解决方案:
SET @last_message_id = (
SELECT id FROM mailbox_last_message WHERE conversationId = '13'
);
BEGIN TRANSACTION;
UPDATE mailbox_last_message SET conversationId = '13', initiatorMessageId = '20', interlocutorMessageId = '10'
WHERE id = @last_message_id
IF (@@ROWCOUNT = 0)
INSERT INTO mailbox_last_message ( id , conversationId , initiatorMessageId , interlocutorMessageId )
VALUES ('' , '13', '20', '0' );
COMMIT;而这个:
SET @last_message_id = (
SELECT `id` FROM mailbox_last_message WHERE `conversationId` = 13);
INSERT INTO mailbox_last_message ( id , conversationId , initiatorMessageId , interlocutorMessageId )
VALUES ( '', '13', '20', '0' ) ON DUPLICATE
KEY UPDATE id = @last_message_id这个解决方案用conversationId 13创建了一个新实例,这不是我想要的。我试图在网上搜索文档和各种教程/示例,但找不到合适的解决方案。任何帮助都将不胜感激。
发布于 2013-10-04 16:01:40
如果要保持唯一的字段上有唯一索引,则可以使用INSERT IGNORE语法。
SET @last_message_id = (
SELECT id FROM mailbox_last_message WHERE conversationId = '13'
);
INSERT IGNORE INTO mailbox_last_message ( id , conversationId , initiatorMessageId , interlocutorMessageId )
VALUES ('' , '13', '20', '0' ) ON DUPLICATE KEY UPDATE id = @last_message_id;https://stackoverflow.com/questions/19185115
复制相似问题