测试环境

MySQL 5.7.25

测试

1. 非分区表

1.1 测试表

CREATE TABLE `yquk` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `a` varchar(10) DEFAULT NULL COMMENT 'a',
  `b` varchar(8) NULL COMMENT 'day',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分区表';

1.2 测试写入

测试对唯一字段 b 写入重复的值测试

-- 写入不为 null 的值，报唯一键冲突
mysql> insert into yquk(a,b) values('1','1'),('2','1');
ERROR 1062 (23000): Duplicate entry '1' for key 'uk_a'

-- 写入值为 '' ，报唯一键冲突
mysql> insert into yquk(a,b) values('5',''),('6','');
ERROR 1062 (23000): Duplicate entry '' for key 'uk_a'

-- 写入值为 null，正常插入
mysql> insert into yquk(a,b) values('3',null),('4',null);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 查看结果集
mysql> select * from yquk;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  5 | 3    | NULL |
|  6 | 4    | NULL |
+----+------+------+
2 rows in set (0.00 sec)

2. 分区表

2.1 测试表

CREATE TABLE `yquk2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `a` varchar(10) DEFAULT NULL COMMENT 'a',
  `b` varchar(8) not NULL COMMENT 'day',
  `c` varchar(10) DEFAULT NULL COMMENT 'a',
  PRIMARY KEY (`id`,b),
  UNIQUE KEY `uk_a` (b,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分区表'
PARTITION BY LIST  COLUMNS(b)
(PARTITION p1 VALUES IN ('1') ENGINE = InnoDB,
 PARTITION p2 VALUES IN ('2') ENGINE = InnoDB,
 PARTITION p3 VALUES IN ('3') ENGINE = InnoDB,
 PARTITION p4 VALUES IN ('4') ENGINE = InnoDB)

2.2 写入测试

-- 1. 同一分区，重复的值  唯一键冲突
mysql> insert into yquk2(b,c) values('1','a'),('1','a');
ERROR 1062 (23000): Duplicate entry '1-a' for key 'uk_a'

-- 2. 不同分区，重复的值  成功写入
mysql> insert into yquk2(b,c) values('1','a'),('2','a');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

-- 3. null 值
mysql> insert into yquk2(b,c) values('1',null),('1',null);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into yquk2(b,c) values('1',null),('2',null);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

3. 结论

• unique 约束不会影响 null 值
• 不同分区内的相同值是允许的