MySQL INSERT(如果不存在)(不使用主键) [英] MySQL INSERT if not exist (not using primary key)

问题描述

如果对不存在，如何插入行?
*注意这些不是主键，我的主键设置为自动递增

尝试插入忽略，但不起作用

how can i insert into a row if the pair does not exist?
* NOTE these are not primary keys, my primary KEY is set to auto increment

tried insert ignore but did not work

INSERT IGNORE INTO mytable (`myid`, `theirid`) VALUES ('5', '1')
ON DUPLICATE KEY <DO NOTHING>

表如下:

CREATE TABLE `mytable` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `myid` bigint(20) NOT NULL,
    `theirid` bigint(20) NOT NULL,
    `activated` tinyint(1) NOT NULL DEFAULT '0',
    `dateStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

推荐答案

1)可以在(myid, theirid)上添加UNIQUE约束吗?如果是，请添加此约束并使用:

1) Can you add a UNIQUE constraint on (myid, theirid)? If yes, add this constraint and use:

INSERT INTO mytable (myid, theirid) 
  VALUES (5, 1) ;

并忽略产生警告(或将上面的内容替换为INSERT IGNORE)

and ignore the produce warnings (or replace the above with INSERT IGNORE)

2)如果您不能添加此类约束(例如，有时您希望允许此类重复项，而其他时候则不允许)，则可以使用以下方法:

2) If you can't add such a constraint (e.g. you sometimes want to allow such duplicates and other times you don't), you can use this:

INSERT INTO mytable (myid, theirid) 
  SELECT 5, 1 
  FROM dual 
  WHERE NOT EXISTS
        ( SELECT *
          FROM mytable
          WHERE myid = 5
            AND theirid = 1
        ) ; 

这篇关于MySQL INSERT(如果不存在)(不使用主键)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！