一般的,去处重复数据有两个意义,一是完全重复的记录,也即所有字段均都重复,二是部分字段重复的记录。对于第一种重复,比较容易解决,只需在查询语句中使用distinct关键字去重,几乎所有数据库系统都支持distinct操作。发生这种重复的原因主要是表设计不周,通过给表增加主键或唯一索引列即可避免。
select distinct * from table;
对于第二类重复问题,通常要求查询出重复记录中的任一条记录。假设表t有id,name,address三个字段,id是主键,有重复的字段为name,address,要求得到这两个字段唯一的结果集。
select * from t t1
where t1.id =
(select min(t2.id)
from t t2
where t1.name = t2.name and t1.address = t2.address);
除此之外,提供三种在mysql中避免重复插入记录方法,主要有ignore,Replace,ON DUPLICATE KEY UPDATE三种方法,根据实际情况修改使用。
方案一:使用ignore关键字
如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用:
INSERT IGNORE INTO `table_name` (`id`, `name`, `adress`) VALUES ('22, 'cj', '8999');
这样当有重复记录就会忽略,执行后返回数字0
还有个应用就是复制表,避免重复记录:
INSERT IGNORE INTO `table_1` (`name`) SELECT `name` FROM `table_2`;
方案二:使用Replace
REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);
REPLACE INTO `table_name` (`col_name`, ...) SELECT ...;
REPLACE INTO `table_name` SET `col_name`='value',
REPLACE的运行与INSERT很相像,但是如果旧记录与新记录有相同的值,则在新记录被插入之前,旧记录被删除.
if not exists (select name from table where name= 'cj') insert into table(name, update_time) values('1', getdate())
else update table set update_time = getdate() where name= 'cj'
方案三:ON DUPLICATE KEY UPDATE
可以在INSERT INTO……后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则执行旧行UPDATE。
例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果:
INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1;
UPDATE `table` SET `c`=`c`+1 WHERE `a`=1;
如果行作为新记录被插入,则受影响行的值为1;如果原有的记录被更新,则受影响行的值为2。