mysql 去除重复数据只保留最新/最大id的一条

数据如下

目的：去重name值并且保留ID最大的记录

//查询 查看是否是需要删除的记录
SELECT *
FROM  test  where id not in (
	SELECT id from (SELECT MAX(id) id FROM test GROUP BY name) a
);

//删除对应的记录
DELETE 
FROM  test  where id not in (
	SELECT id from (SELECT MAX(id) id FROM test GROUP BY name) a
);

如果mysql大于5.7版本
可能会报错

SELECT id from (SELECT name,id FROM test GROUP BY name HAVING count(1)>1) a
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.test.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

需要修改my.ini 去掉sql_model 中 only_full_group_by