MySQL批量删除所有重复数据只保留一条

-- 1.创建数据库及t_user表
CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8;

USE `test`;

DROP TABLE IF EXISTS `t_user`;

CREATE TABLE `t_user`  (
  `id` bigint NOT NULL DEFAULT 0 COMMENT '主键，用户唯一id',
  `user_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
  `phone_number` varchar(16) NOT NULL DEFAULT '' COMMENT '电话号码',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '用户账号创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '上次更新记录时间',
  `last_login_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '上次登录时间',
  `status` int(2) NOT NULL DEFAULT 0 COMMENT '用户状态 0-正常 1-封禁',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '用户信息表';

-- 2.创建存储过程
CREATE PROCEDURE `insert_t_user_test`(IN `loop_times` int)
BEGIN
	    DECLARE var INT DEFAULT 0;
    WHILE var < loop_times DO
        SET var = var + 1;
    	  INSERT INTO `t_user` VALUES (var, CONCAT('admin-', var), '888', 'admin@163.com', '15251831704', 
				CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
    END WHILE;
		COMMIT;
END

-- 3.查询及删除多条重复数据，只保留一条
-- 往t_user表里面插入数据
call insert_t_user_test(10)

-- 查询t_user表
select * from t_user

-- 方法一、通过分组过滤查询数据，删除重复数据只保留一条
delete from t_user where id not in (select t.id from (select min(id) AS id from t_user group by pwd)t )

-- 方法二、查询出大于或等于一条的数据，删除其它重复数据，只保留一条不重复数据和一条重复数据
delete from t_user where id not in (select t.id from (select max(id) as id from t_user group by pwd having count(pwd) >= 1)t )