標籤：des strong ar 資料 div amp sp on ad
去除表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重要了。
刪除MySQL中冗餘欄位
