删除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重要了。