MySQL插入：避免重复多个cols

I basically want to insert two Values ('Val', 1) into the columns (cls, jg) of the table t1. Although cls is set to be unique, jg has to appear multiple times with the same value, so INSERT IGNORE wouldn't work. Thorefore, I have to check if the pair ('Val', 1) already exists, and if not so, I have to insert these values.

我基本上想要将两个值('Val',1)插入到表t1的列(cls,jg)中。尽管cls设置为唯一,但jg必须以相同的值出现多次,因此INSERT IGNORE不起作用。因此,我必须检查对('Val',1)是否已经存在,如果不存在,我必须插入这些值。

I tried it this way:

我这样试过:

INSERT INTO t1 (`cls`,`jg`)
SELECT 'Val',1 FROM cls WHERE NOT EXISTS(
    SELECT 1 FROM cls WHERE `cls`='Val' AND `jg`=1)
LIMIT 1;

But it doesn't work when the table is empty, because then the outer select statement doesn't contain any entries…

但是当表为空时它不起作用,因为外部select语句不包含任何条目......

How should I proceed? I have no idea how to handle this a different way…

我该怎么办?我不知道如何以不同的方式处理这个......

2 个解决方案

#1

Use an unique composite index on cls, jg.

在cls,jg上使用唯一的复合索引。

ALTER IGNORE TABLE t1 ADD UNIQUE  INDEX(cls(200), jg)

Then use

INSERT.. ON DUPLICATE KEY UPDATE

#2

You are mistaken. If you have a unique constraint (like a primary key) on cls that doesn't in any way interfere with the values of jg.

你误会了。如果cls上有一个唯一的约束(如主键),它不会以任何方式干扰jg的值。

sql fiddle

#1