是否有解决方法来定义将NULL值视为非唯一的唯一约束? [英] Is there a workaround for defining a unique constraint that treats NULL values as non-distinct?

问题描述

我正在尝试在MySQL中定义一个唯一约束，该约束将把NULL视为彼此没有区别.

I'm trying to define a unique constraint in MySQL that will treat NULLs as non-distinct to each other.

即
如果我有这样一个表:

I.e.
If I have a table like this:

column1 int(11) NOT NULL,
column2 int(11) DEFAULT NULL

其值由AUTO INCREMENT INT填充，然后定义唯一约束，如下所示:

whose values are populated by AUTO INCREMENT INT, and then define a unique constraint as follows:

UNIQUE KEY uniq1 (column1, column2),

由于MySQL的行为，我可以多次插入(1，NULL)，以将NULL视为在唯一约束中彼此不同.但是，如果我实际上想防止这样的重复插入，除了分配魔术值之外还有其他方法吗?

I can insert (1, NULL) multiple times because of MySQLs behaviour to treat NULLs as distinct to each other in a unique constraint. If however I actually wanted to prevent such duplicate insertion, is there a way other than assigning a magic value?

推荐答案

如 CREATE INDEX语法:

对于所有引擎，UNIQUE索引允许包含NULL的列具有多个NULL值.

For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

您不能在表达式上定义索引(如您的问题中给出的那样)，因此MySQL的UNIQUE索引无法如您所愿地强制执行约束.

You cannot define an index over an expression (such as given in your question), therefore MySQL's UNIQUE indexes cannot enforce the constraint as you wish.

相反，您可以创建引发错误的BEFORE INSERT 触发如果已经存在匹配的记录:

Instead, you can create a BEFORE INSERT trigger that raises an error if a matching record already exists:

DELIMITER ;;

CREATE TRIGGER uniq1 BEFORE INSERT ON my_table FOR EACH ROW
  IF EXISTS(
    SELECT *
    FROM   my_table
    WHERE  column1 <=> NEW.column1 AND column2 <=> NEW.column2
    LIMIT  1
  ) THEN
    SIGNAL
      SQLSTATE '23000'
      SET MESSAGE_TEXT = 'Duplicate entry for key uniq1';
  END IF;;

为防止UPDATE引起类似的问题，您可能还希望创建类似的BEFORE UPDATE触发器.

To prevent UPDATEs from causing a similar problem, you will probably want to create a similar BEFORE UPDATE trigger too.

这篇关于是否有解决方法来定义将NULL值视为非唯一的唯一约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！