mysql - 修改列与删除和添加列 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    |                |
+----------------+--------------+------+-----+---------+---------

目前，加密电子邮件中没有数据。

我想创建一个唯一的 key - (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 的文档alter table对此进行了描述。声明:

Alterations that modify only table metadata and not table data are immediate because the server only needs to alter the table .frm file, not touch table contents. The following changes are made in this way:

◾ ...

◾ Changing the default value of a column (except for NDB tables).

...

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

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