Database charset conversion

Question

Hi there.

I've moved database from one host to another. I've used PMA to export and bigdump to import. The whole database have latin2 charset set everywhere where it's possible. However in database, special chars (polish ąęłó, etc.) are broken. When I used SELECT i see "bushes" - "Ä�" insetad of "ą". Then I've set document encoding to utf-8... And the characters are good. How to fix this? Can it be done using CONVERT in query? I don't want to export/import database again, because it has over 200MB. What's wrong?

Every PHP/MySQL query solution will save me.

Sorry if you can't understand this, because I'm still learning english though.

Answer 1

If a table contains the wrong kind of charset (let's say utf-8 has slipped into latin1 column varhcar(255)):

ALTER TABLE tablename MODIFY colummname BINARY(255);
ALTER TABLE tablename MODIFY colummname VARCHAR(255) CHARSET utf8;
ALTER TABLE tablename MODIFY colummname VARCHAR(255) CHARSET latin1;

See also: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html

However, it is more likely you just have a wrong character set in your default connection. What does a SET NAMES latin1; before selecting result in?