烟台住房和城乡建设厅网站,厚街找人做网站,用服务器ip做网站页面,网站开发工程师课程【Mysql】group语句删除重复数据只保留一条 【一】案例分析
假如在数据初始化的时候#xff0c;insert脚本执行了两次#xff0c;导致表里的数据都是重复的#xff08;没有设置唯一键#xff09;。这个时候再加上mybatis-plus的selectOne方法#xff0c;就会出现报错。因… 【Mysql】group语句删除重复数据只保留一条 【一】案例分析
假如在数据初始化的时候insert脚本执行了两次导致表里的数据都是重复的没有设置唯一键。这个时候再加上mybatis-plus的selectOne方法就会出现报错。因为selectOne方法要求查询结果必须唯一如果出现多条数据就会报错。
所以需求就是根据某个条件字段查询出所有这个字段存在重复的数据然后删除掉重复的数据保证条件查询的时候只能查出来1条数据。
以这张表为例
CREATE TABLE test (id varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 注解id,name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 名字,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci ROW_FORMAT Compact;INSERT INTO test (id,name) VALUES (replace(uuid(),-,),张三),(replace(uuid(),-,),张三);表里有两条数据然后名字是相同的但是id是不同的现在要求是只留一条数据
【二】步骤一查询name值重复的数据 现实开发当中可能一个字段无法锁定重复值可以采取group by多个值利用多个值来锁定重复的行数据
SELECTname
FROMtest
GROUP BY name
HAVINGcount( name ) 1【三】步骤二查询重复数据里面每个最小的id
SELECTmin( id ) AS id
FROMtest
GROUP BY name
HAVINGcount( name ) 1【四】步骤三查询去掉重复数据最小id的其他数据也就是要删除的数据
SELECT*
FROMtest
WHEREname IN ( SELECT name FROM test GROUP BY name HAVING count( name ) 1 ) AND id NOT IN (SELECTmin( id ) FROMtest GROUP BY name
HAVINGcount( NAME ) 1)【五】步骤四删除去掉重复数据最小id的其他数据 有了查询直接改成delete不就可以了真的是这样吗其实不是的如下运行报错 首先明确一点这个错误只会发生在delete语句或者update语句拿update来举例 : update A表 set A列 (select B列 from A表) 这种写法就会报这个错误原因你又要修改A表然后又要从A表查数据而且还是同层级。Mysql就会认为是语法错误
嵌套一层就可以解决update A表 set A列 (select a.B列 from (select * from A表) a); 当然这个只是个示例这个示例也存在一定的问题比如(select a.B列 from (select * from A表) a)他会查出来多条然后赋值的时候会报 1242 - Subquery returns more than 1 row。
嵌套一层他就可以和update撇清关系会优先查括号里面的内容查询结果出来过后会给存起来类似临时表可能有的人该好奇了update A表 set A列 (select B列 from A表) 我明明加括号了呀难道不算嵌套吗当然不算那个括号根本没有解决他们之间的层次关系
【六】正确的写法 首先先把表备份好删错了也还能快速恢复。直接拷贝的一个新的表改名字就行
1方式一
DELETE FROM test
WHERE
name IN ( select a.name from (SELECT name FROM test GROUP BY name HAVING count( name ) 1) a)
AND
id NOT IN (select a.id from (SELECT min(id) as id FROM test GROUP BY name HAVING count( name ) 1) a)
注意删除之前一定要先查询然后再删除否则一旦语法有问题导致删了不想删除的数据想要恢复很麻烦或者删除前备份好数据不要嫌麻烦一旦出问题才是真正的大麻烦
2方法二
DELETE FROM test
WHEREid NOT IN (SELECTt.id
FROM( SELECT MIN(id) as id FROM test GROUP BY NAME ) t)
【七】错误的写法 千万千万不能这么搞下面这个语法相当于是先按name分组然后查出来大于1的这时候假如大于1的有很多然后外面嵌套的那一层只取了最小的一条数据然后再加上使用的是NOT IN最终会导致数据全部被删除 执行前有四条数据实际上我们要的是张三留下来一条然后李四留下来一条 执行结果只留下了一条
