去除表deny_mail中email,type重复的字段
设计deny_mail表时未考虑到唯一性,导致插入了许多重复数据,现编写数据库语句修正之。
步骤1:建立临时表tmp存储
冗余
键信息
1)create table tmp as select min(id),type,mail from deny_mail having count(type,email)>1 group by type,email;
步骤2:根据临时表删除冗余信息
2)delete from deny_mail where id not in (select id from tmp) having count(type,email)>1 group by type,email;
步骤3:删除临时表
步骤3:删除临时表
3)drop table tmp;
步骤4:给表添加唯一键
4)alter table deny_mail add constraint job_unique unique (type,email);
mysql> desc deny_mail;
+-------------+------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------------+----------------+
| email | varchar(255) | NO | MUL | NULL | |
| type | int(10) unsigned | NO | | 0 | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| reason | varchar(255) | YES | | NULL | |
| otherReason | varchar(255) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------------+------------------+------+-----+-------------------+----------------+
mysql> show create table deny_mail;
| Table | Create Table
CREATE TABLE `deny_mail` (
`email` varchar(255) NOT NULL,
`type` int(10) unsigned NOT NULL default '0',
`create_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
`reason` varchar(255) default NULL,
`otherReason` varchar(255) default NULL,
`id` int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`,`type`)
) ENGINE=InnoDB AUTO_INCREMENT=1698 DEFAULT CHARSET=latin1
数据库基础太TM重要了。