MySQL:唯一，但默认为NULL-创建表允许.允许插入多个NULL.为什么? [英] MySQL: UNIQUE, but DEFAULT NULL - allowed by creating of table. More than 1 NULL is allowed to insert. Why?

问题描述

我刚刚检查了一下，虽然它同时是UNIQUE KEY，但允许创建一个默认列为NULL的表:

I've just checked and it's allowed to create a table with a column that is NULL by default, although it's a UNIQUE KEY at the same time:

CREATE TABLE IF NOT EXISTS `u789` (
`column1` varchar(10) DEFAULT NULL,
UNIQUE KEY (column1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

据我了解，它看起来很奇怪并且没有太多意义.我希望第二次插入

As I understand, it looks odd and has not much sense. I expected the second insert of

INSERT INTO u789 VALUE (NULL);

将失败.

但是，它插入第一个，第二个，第三个NULL值没有任何问题.谁能解释一下如果表中已经有NULL的原因，为什么它会插入第二列和第三列?

But, it inserts first, second, third NULL value without any problems. Who can explain me why it iserts second and third columns if NULL is already in the table?

这是一个理论上的问题(因为我知道在大多数情况下没有人会在同一列上使用DEFAULT NULL + UNIQUE KEY)，但是我想理解为什么一旦该列中已经有一个NULL却不抛出错误.我在声明唯一列时做错什么了吗?

This is a theoretical question (as I understand nobody uses DEFAULT NULL + UNIQUE KEY for the same column in most situations), but I want to understand why it doesn't throw an error once one NULL is already in the column. Am I doing something wrong with declaring a unique column?

谢谢.

推荐答案

根据SQL 92规范(以及您的阅读方式)，唯一约束用于表示候选键，因此不应允许重复的值 nor NULL值. DB2以这种方式实现其唯一约束.多家数据库供应商(包括MySQL)将规范视为忽略NULL值，就像Group By子句忽略NULL值一样，因此它们实现了唯一的约束，因此仅适用于非NULL值.还有一些人将NULL当作自己的特殊值，并且只允许输入一个为NULL的条目. Microsoft SQL Server通过这种方式实现了唯一的约束.所有供应商在唯一性约束方面唯一一致的方面是非NULL值必须唯一.

According to the SQL 92 specification (and how you read it) unique constraints are meant to denote candidate keys and therefore should not allow duplicate values nor NULL values. DB2 implements their unique constraints in this manner. More than a few database vendors (including MySQL) read the specification as ignoring NULL values much like the Group By clause ignores NULL values and thus they implement unique constraints such that it only applies to non-NULL values. Still others, treat the NULL as its own special value and only allow one entry which is NULL. Microsoft SQL Server implements unique constraints this way. The only aspect that is consistent amongst all vendors with respect to unique constraints is that non-NULL values must be unique.

这篇关于MySQL:唯一，但默认为NULL-创建表允许.允许插入多个NULL.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！