Remove Email,type duplicate fields from table Deny_mailThe design of the Deny_mail table does not take into account uniqueness, resulting in the insertion of a lot of duplicate data, is now written database statement correction.
Step 1: Establish the TEMP table TMP store
Redundancy
Key Information
1) CREATE TABLE tmp as SELECT min (id), type,mail from Deny_mail have count (type,email) >1 GROUP by Type,email;
Step 2: Delete redundant information based on temporary tables2) Delete from Deny_mail where ID not in (SELECT ID from TMP) has count (type,email) >1 GROUP by Type,email;
Step 3: Delete the staging table3) DROP table tmp;
Step 4: Add a unique key to the table4) 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 (Ten) unsigned | NO | | 0 | | | Create_time | Timestamp | NO | | Current_timestamp | | | Reason | varchar (255) | YES | | NULL | | | Otherreason | varchar (255) | YES | | NULL | | | ID | Int (Ten) 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 (ten) 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 (ten) unsigned not NULL auto_increment, PRIMARY key (' id '), unique key ' email ' (' email ', ' type ') Engine=innodb auto_increment=1698 DEFAULT charset=latin1 Database Base too TM important.
Remove redundant fields from MySQL
