首页 > 代码库 > 删除MySQL中冗余字段

删除MySQL中冗余字段

2024-07-19 13:27:04   222人阅读

去除表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中冗余字段


联系
我们