MySQL: lossless "group by"

Question

I have a table like this:

name
Smith
Smith
Perez
Pérez

I would like to remove duplicates like Smith, but keep both Perez and Perez (e and é). If I use 'group by', I get two rows (Smith and one of two Perez / Pérez), but I would like to get three rows (Smith, Perez, Pérez). The same thing happens with Sjögren and Sjögren, etc. Thanks

Answer 1

1) First check your table if it encodes utf8 encoding with

select table_name,engine 
from information_schema.tables
where table_schema = 'your_database'

2) Secondly, if it is not (otherwise skip the 3rd step), ALTER your table (utf8 character set encoding, so it will support a special character)

ALTER TABLE `name` CHARACTER SET utf8;

3) CHOOSE from your db using utf8 encoding

select * from your_table group by name collate utf8_general_ci

Answer 2

utf8_unicode_ci, utf8_general_ci - .