Mysql – character encoding issues in thesql

MySQL

I have some legacy mysql files of the format frm, MYI and MYD that i wish to export to csv. Using sequel pro on OSX, i created a new database and copied those files over.

All the data can be accessed fine but i have major issues with the query results due to unknown encoding. I have no idea what format the previous programmer used.

This database contain English, Chinese, Japanese and Korean song titles but as i have no idea what format these names are encoded in, so i get weird characters using utf8 format:

ÄÐÉù
ÂèÂèºÃ+Éµ´ó½ã+ÆÃË®¸è
Àä¿ÕÆøµÄ¶À°×
À¶É«µÄÃÎ

Most of them begins with A with an accent though, i wonder if they do provide any clue as to which encoding format the programmer used.

I've tried putting these text onto safari/chrome browser and ran through the various encoding options but to no avail.

Will anyone please help me?

Best Answer

Is your client talking utf8? Run

SET NAMES utf8;

and see if your selects output correctly?

Related Solutions

Character Set Encoding in MySQL Tables

Here is the list of collations under UTF-8

mysql> select * from information_schema.collations where CHARACTER_SET_NAME = 'utf8';
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci    | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci    | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci      | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci  | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci    | utf8               | 211 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.03 sec)

latin1_swedish_ci belongs to latin1

mysql> select * from information_schema.collations where COLLATION_NAME = 'latin1_swedish_ci';
+-------------------+--------------------+----+------------+-------------+---------+
| COLLATION_NAME    | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-------------------+--------------------+----+------------+-------------+---------+
| latin1_swedish_ci | latin1             |  8 | Yes        | Yes         |       1 |
+-------------------+--------------------+----+------------+-------------+---------+
1 row in set (0.03 sec)

The closest utf8 for your collation is ID 200

mysql> select * from information_schema.collations where ID = 200;
+-----------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME  | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+-----------------+--------------------+-----+------------+-------------+---------+
| utf8_swedish_ci | utf8               | 200 |            | Yes         |       8 |
+-----------------+--------------------+-----+------------+-------------+---------+
1 row in set (0.00 sec)

CAVEAT

You may have to experiment with latin1, change the character set and collation of the individual column in the table, or possibly both. At the very least, use latin1 for display in phpmyadmin.

You could tweek and experiment with making the entire database a specific character set and collation using ALTER DATABASE.

ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

Make sure you make a backup of the database and load it into a dev/staging DB and then run ALTER DATABASE against dev/staging.

MYSQL dump and problems character encoding

Assuming your source data contained only English and Greek characters, and was mis-exported with an ISO-8859-1 to UTF-8 conversion (rather than an ISO-8859-7 to UTF-8 conversion), you can get your data back by first repeating the missed conversion the other way around, and then doing the right one.

You could use iconv for this (available on pretty much all Linux distributions, and on Windows via gnuwin32).

You'd do something like:

$ iconv -f UTF-8 -t ISO-8859-1 < bad_dump_file.sql | \
  iconv -f ISO-8859-7 -t UTF-8 > good_dump_file.sql

As an example:

$ echo -n $'\xC3'$'\x90' | iconv -f UTF-8 -t ISO-8859-1 | \
   iconv -f ISO-8859-7 -t UTF-8 | hexdump -C
00000000  ce a0                                             |Î |
00000002

And 0xCE 0xA0 is the right UTF-8 encoding for Π.

Best Answer

Related Solutions

Character Set Encoding in MySQL Tables

MYSQL dump and problems character encoding

Related Question