MySQL允许在唯一索引字段中添加多个NULL值

今天正在吃饭,一个朋友提出了一个他面试中遇到的问题,MySQL允许在唯一索引字段中添加多个NULL值。

这个问题对于我一个非专业DBA来说,也没特地去验证过,所以正好借此机会验证一下,做个记录:

测试环境:

数据库:MySQL5.7.25
数据库引擎:InnoDB
连接工具:Navicat Premium

首先,创建数据库,并创建测试表 test:

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL COMMENT 'ID',
  `name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

然后,我们给name字段添加一个唯一索引:

ALTER TABLE `test` ADD UNIQUE ( `name`);

最后,我们尝试插入两条name字段为null的数据:

INSERT INTO `test` VALUES (1, NULL);
INSERT INTO `test` VALUES (2, NULL);

并没有报错,说明MySQL允许在唯一索引字段中添加多个NULL值

数据表如下:

MySQL的官方文档给出的解释为:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL.

我们可以看出,此约束不适用于除BDB存储引擎之外的空值。对于其他引擎,唯一索引允许包含空值的列有多个空值。

 

网友给出的解释为:

在sql server中,唯一索引字段不能出现多个null值

在mysql 的innodb引擎中,是允许在唯一索引的字段中出现多个null值的。

根据NULL的定义,NULL表示的是未知,因此两个NULL比较的结果既不相等,也不不等,结果仍然是未知。根据这个定义,多个NULL值的存在应该不违反唯一约束,所以是合理的,在oracel也是如此。

这个解释很形象,既不相等,也不不等,所以结果未知。

至于,sqlserver和MySQL其他引擎还有待验证。

  • 3
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
MySQL,可以使用ALTER TABLE语句来为表字段添加索引。具体的语法如下: ALTER TABLE <表名> ADD INDEX (<字段>); 例如,如果我们要为test表的t_name字段添加一个索引,可以执行以下语句: ALTER TABLE test ADD INDEX (t_name); 执行成功后,可以使用DESCRIBE语句来查看表的结构,可以看到t_name字段的Key一栏由原来的空白变成了MUL。MUL表示该列是一个非唯一索引的前导列或者是一个唯一性索引的组成部分但是可以含有空值NULL。[1] 此外,MySQL还支持其他类型的索引,包括主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)和全文索引(FULLTEXT)。可以使用类似的语法来添加这些索引。例如,添加主键索引可以使用以下语句: ALTER TABLE table_name ADD PRIMARY KEY (column); 添加唯一索引可以使用以下语句: ALTER TABLE table_name ADD UNIQUE (column); 添加普通索引可以使用以下语句: ALTER TABLE table_name ADD INDEX index_name (column); 添加全文索引可以使用以下语句: ALTER TABLE table_name ADD FULLTEXT (column); 还可以添加多列索引,语法类似: ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3);[2] 需要注意的是,索引的选择应该根据具体的需求和查询场景来进行,不同类型的索引有不同的适用场景。唯一索引主要用于保证数据记录的唯一性,而普通索引和全文索引则可以用于快查询速度。[3]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一只蚊子啊

感谢您的喜欢

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值