mysql - Old entries containing UTF8 characters saved incorrectly in UTF8 database -

ok, i've ensured mysql (5.1.61) database utf8, table utf8, field utf8, , mysql client's charset set utf8. can store , retrieve utf8 entries successfully. i've ensured terminal's encoding set utf8.

create table `cities` (   `name` varchar(255) default null ) engine=myisam default charset=utf8; 

the issue when comes 200,000 entries exist in database. appears people inherited project messed lot of encoding, saving string hörby hörby Ã , ¶ valid utf8 characters. is, mysql receiving utf8 string of hörby , storing such. here example first entry 1 of old entries, , second inserting "hörby" database set utf8:

mysql> insert cities set name = 'hörby'; query ok, 1 row affected (0.00 sec)  mysql> select * cities; +----------+ | name     | +----------+ | hörby   | <--- old entry | hörby    | <--- new entry +----------+ 

what can convert squished characters once were? we're pretty ready @ point, re-typing 200,000 records not feasible.

it looks had stored utf8 encoded strings in latin1 column, converted column utf8. fix that:

1. convert data latin1:

   alter table cities modify name varchar(255) character set latin1; 

2. change column type utf-8 without altering data (going via binary):

   alter table cities modify name varchar(255) character set binary; alter table cities modify name varchar(255) character set utf8;