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 )