删除MySQL中冗余字段

去除表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)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重要了。

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。