mysql - 列匹配特定值时插入或替换(Insert or replace when column matches specific value)

Question

I have the following table:

(我有下表：)

CREATE TABLE IF NOT EXISTS `test` (
  `gid` INT NOT NULL,
  `x` INT NOT NULL,
  `y` INT NOT NULL,
  `z` INT NOT NULL,
  `type` INT NOT NULL,
  PRIMARY KEY ( `gid`, `x`, `y`, `z` )
);

The combination of gid , x , y , z is unique.

(gid ， x ， y ， z是唯一的。)

There can be multiple rows with the same gid and type as long as the x / y / z values are different.

(只要x / y / z值不同，就可以有多个具有相同gid和type行。)

However, I want the combination of gid and a specific type value to be unique.

(但是，我希望gid和特定type值的组合是唯一的。)

For example, there can only be one row per gid = x with type = 1 , but there can be multiple rows with gid = x and type = 2 .

(例如，每个gid = x只能有一个type = 1 ，但是gid = x和type = 2可以有多个行。)

I know that I can declare a unique constraint for gid and type and then use INSERT ... ON DUPLICATE VALUE UPDATE .. , however I only want that to work when type equals 1.

(我知道我可以为gid和type声明一个唯一约束，然后使用INSERT ... ON DUPLICATE VALUE UPDATE .. ，但是我只希望在type等于1时起作用。)

Any solution for this?

(有什么解决办法吗？)

  ask by Priv translate from so

Answer 1

You could achieve it with function index(supported by MySQL 8.0.13 and newer):

(您可以使用函数索引（MySQL 8.0.13和更高版本支持）来实现它：)

CREATE UNIQUE INDEX myIndex ON test(gid,(CASE WHEN `type` = '1' THEN `type` END));

db<>fiddle demo

(db <> fiddle演示)

---

CREATE TABLE IF NOT EXISTS `test` (
  `gid` INT NOT NULL,
  `x` INT NOT NULL,
  `y` INT NOT NULL,
  `z` INT NOT NULL,
  `type` INT NOT NULL,
  PRIMARY KEY ( `gid`, `x`, `y`, `z` )
);

CREATE UNIQUE INDEX myIndex ON test(gid,(CASE WHEN `type` = '1' THEN `type` END));

INSERT INTO `test`(gid, x,y,z,`type`) VALUES (1,1,1,1,1);
INSERT INTO `test`(gid, x,y,z,`type`) VALUES (1,2,2,2,1);
-- Duplicate entry '1-1' for key 'myIndex'

INSERT INTO `test`(gid, x,y,z,`type`) VALUES (3,1,1,1,2);
INSERT INTO `test`(gid, x,y,z,`type`) VALUES (3,1,2,2,2);
SELECT * FROM `test`;

---

Can I add the unique index on table creation?

(我可以在创建表时添加唯一索引吗？)

CREATE TABLE IF NOT EXISTS `test` (
  `gid` INT NOT NULL,
  `x` INT NOT NULL,
  `y` INT NOT NULL,
  `z` INT NOT NULL,
  `type` INT NOT NULL,
  PRIMARY KEY ( `gid`, `x`, `y`, `z` ),
  UNIQUE INDEX myIndex(gid,(CASE WHEN `type` = '1' THEN `type` END))
);

db<>fiddle demo2

(db <> fiddle demo2)