Convert a latin1 db imported in utf8...

[Will Kopp]

Ok, I have seen many problems regardin convertion and here is another one.
I think I am close to a solution but I miss a little something.

I have a db that is setup in utf8.
The db contains data that has been imported from latin1.
However, the data has not been converted thus french accents such as 'à' are stored as 'Ã' in utf8.

For some reason, I cannot reimport the db, I need to fix the data.

I created a test table with data such as this :
txt1 = équipe fière Bienvenue à tous

my goal is to modify this entry by
txt1 = équipe fière Bienvenue à tous

So I tried the following thing :
mysql> select txt1, replace(txt1, "Ã", "à")as test from junk1 where id=4\G
*************************** 1. row ***************************
txt1: équipe fière Bienvenue à tous
test: à©quipe fià¨re Bienvenue à tous
1 row in set (0.00 sec)

You can see that 'Ã' has been properly converted to 'à' but 'é' has been changed as weell, this is my first problem.
The following does not work :
mysql> select txt1, replace(txt1, "Ã ", "à ")as test from junk1 where id=4\G
*************************** 1. row ***************************
txt1: équipe fière Bienvenue à tous
test: équipe fière Bienvenue à tous
1 row in set (0.00 sec)

Second problem, the query :
mysql> update junk1 set txt1= replace (txt1,'Ã', 'à') where id =2 limit 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

gives :
mysql> select txt1 from junk1 where id=2\G
*************************** 1. row ***************************
txt1:
1 row in set (0.00 sec)


I could probably write a little thingy in php whith a loop but it seems I could do it in a smart way in MySQL.

Can anyone help or point out what is wrong in what I do ? I and probably other french folks would probably appreciate a lot ;-p


Oh, and I tried also :
mysql> select _latin1'à';
+----+
| Ã |
+----+
| Ã |
+----+
1 row in set (0.00 sec)


thus though the following statement would work :
mysql> select txt1, replace(txt1, _latin1'à', _utf8'à')as test from junk1 where id=4\G
*************************** 1. row ***************************
txt1: équipe fière Bienvenue à tous
test: équipe fière Bienvenue à tous
1 row in set (0.00 sec)


BUMMER !
Any help greatly appreciated.

Regards,
Will