修改列vs删除并添加列Mysql

Question

表结构-

| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| candidateId    | bigint(20)   | NO   |     | NULL    |                |
| profileId      | bigint(20)   | NO   | MUL | NULL    |                |
| clientId       | bigint(20)   | NO   |     | NULL    |                |
| email          | varchar(128) | NO   |     | NULL    |                |
| verified       | tinyint(1)   | YES  |     | 0       |                |
| isPrimary      | tinyint(1)   | YES  |     | 0       |                |
| createdOn      | datetime     | NO   |     | NULL    |                |
| createdBy      | bigint(20)   | NO   |     | NULL    |                |
| encryptedEmail | varchar(255) | NO   |     | NULL    |                |
+----------------+--------------+------+-----+---------+---------

目前，在加密中没有数据。

我想要创建一个独特的密钥- (clientId, profileId, encryptedEmail)

因此，我尝试将加密的电子邮件转换为默认的null，因为有记录

clientId and profileId were duplicate.

我用的查询-

 ALTER TABLE AlternateEmails MODIFY encryptedEmail varchar(255) NULL, 
 add constraint `profileId_3` 
 unique(`encryptedEmail`,`clientId`,`profileId`);

它仍然显示重复的记录错误。

然后我用-

 alter table AlternateEmails drop column encryptedEmail,
 add column encryptedEmail varchar(255) default NULL, add constraint 
 `profileId_3` unique(`encryptedEmail`,`clientId`,`profileId`);

效果很好。有人能解释为什么简单地修改列不起作用吗？

Answer 1

更改现有字段上的默认值与使用该新默认值添加字段之间有明显的区别:如果更改现有字段的默认值，则而不是修改具有旧默认值的现有数据。

新的默认值将继续应用。

在MySQL关于变更表语句的文档中描述了这一点：

修改只修改表元数据而不修改表数据的更改是即时的，因为服务器只需要更改表.frm文件，而不需要更改表内容。通过这种方式进行了下列更改： ◾... ◾更改列的默认值( NDB表除外)。 ..。

因此，如果encryptedEmail字段的默认值为null，那么它将一个空字符串(或您指定的任何其他内容)作为默认值，并且所有已经存在的记录都填充了这个默认值。将默认值更改为null不影响已经存在的记录，因此encryptedEmail字段值将彼此重复。

但是，当您删除并重新创建以null作为默认值的encryptedEmail字段时，MySQL使用null值填充了现有记录，这些值在MySQL中不被认为是重复的。