mysql - 如何将字符串列的串联作为 MySQL 中的唯一约束

Question

我有一个表说 employee employee 表有几个字段，例如

`salary`
`name`
`age`
`designation`

其中只有designation 可以为空。我无法将其设为 NOT NULL，因为现有代码正在使用它。

是否可以为上述所有列创建组合唯一约束？？

当我尝试创建新的唯一约束时。

ALTER TABLE `employee` ADD CONSTRAINT `employee_constraint` 
UNIQUE key (`salary`,`name`, `age`, `designation`);

它成功创建了约束，但是当我尝试插入这些记录的重复组合时，它插入成功。有没有更好的方法来创建约束并丢弃这些列的重复组合？

Answer 1

通常 MySQL 允许在 UNIQUE 约束中使用多个 NULL。更多信息:Does MySQL ignore null values on unique constraints?

您可以使用生成的列只允许一个 NULL 值:

CREATE TABLE employee(salary INT, name VARCHAR(100)
                     ,age INT, designation VARCHAR(100)
                ,designation_virtual VARCHAR(100) AS (COALESCE(designation, '^'))
);

ALTER TABLE employee ADD CONSTRAINT employee_constraint 
UNIQUE key (salary,name, age, designation_virtual) ;

INSERT INTO employee(salary, name, age, designation)
VALUES(1000, 'xyz', 10, NULL);

INSERT INTO employee(salary, name, age, designation) 
VALUES(1000, 'xyz', 10, NULL);
-- Duplicate entry '1000-xyz-10-^' for key 'employee_constraint'

SELECT * FROM employee;

DBFiddle Demo

---

如果您使用的是 MariaDB 10.3.3，您可以将虚拟列标记为 INVISIBLE .

Columns can be given an INVISIBLE attribute. These columns will then not be listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement, unless INSERT explicitly mentions them by name.

ALTER TABLE employee MODIFY designation_virtual INVISIBLE;