Mysql character set decoding from utf8_unicode_ci to shift jis

Question

i have tried many approach to convert utf8_unicode_ci value in MySQL column from utf8_unicode_ci to shift_jis but it not getting fixed.

The original column value was in shift_jis and it got convert when new hosting used. now the value seems garbage but it is showing good at website front-end.

The table column value are: ƒEƒCƒ“ƒhEƒŠƒ“ƒO table column and the character set are character set.

i have tried this mb_convert_encoding($str, "SJIS", "UTF-8"); and many other but it not getting convert back. any valuable solution?

Answer 1

First, determine what is really in the table by doing

SELECT col, HEX(col) ...

If it is utf8, then the hex should be 3-byte sets like E38xyy for Katakana and Hiragana characters. If not, then you possibly already have SJIS there. (or something else)

Assuming that the table column says

col VARCHAR(...) ... CHARACTER SET utf8  -- (or utf8mb4)

then you can get a SELECT to convert to SJIS by doing

mysqli_charset('sjis');
mysqli_query( ... )

If, instead, you need to convert the table from utf8 to sjis, do something like

ALTER TABLE tbl CONVERT TO CHARACTER SET sjis;

That will convert all the columns in tbl, plus all the data in those columns to the specified charset.

Do not use mb_* or other conversion functions; it just adds to the confusion.

ƒEƒCƒ“ƒhEƒŠƒ“ƒO, if interpreted as latin1, is hex 834583438393836845838A8393834F, which, if interpreted as sjis (or cp932) is ウインドEリング -- Is that what you expected?

That is, ƒEƒCƒ“ƒhEƒŠƒ“ƒO is Mojibake for sjis ウインドEリング. Mojibake is usually fixed by doing the mysqli_charset, as above.