3

I have a MySQL database set to utf8. My charset/collation variables are:

Variable_name | Value

character_set_client | utf8

character_set_connection | utf8

character_set_database | utf8

character_set_filesystem | binary

character_set_results | utf8

character_set_server | latin1

character_set_system | utf8

collation_connection | utf8_general_ci

collation_database | utf8_general_ci

collation_server | latin1_swedish_ci

I have a web page that displays Chinese characters and Pinyin from our MySQL DB. The Chinese characters display fine, but the Pinyin is garbled. For instance,

displays: NánjÄ«ng correct: Nánjīng

Now, I check page encoding and it is set to UTF8. I echoed out Nánjīng in PHP and it displayed fine. I checked out the data in command line and it is correct in the database. However, whenever it is coming through a query, it garbles the pinyin, but not the Chinese characters. Anyone know why this could be happening?

Makoto
  • 104,088
  • 27
  • 192
  • 230
Beaker
  • 1,633
  • 13
  • 22
  • Is it possible the Han characters are in UTF-8 but pinyin isn't? You need to verify the character set per table. – Steve-o Aug 28 '11 at 05:18
  • Related issue, [fixing column encoding in MySQL](http://www.mysqlperformanceblog.com/2007/12/18/fixing-column-encoding-mess-in-mysql/) – Steve-o Aug 28 '11 at 05:22
  • @Steve-o Thanks for the link! That led me down the right path. I posted what I did to fix it. – Beaker Aug 28 '11 at 12:03
  • `NánjÄ«ng` is Mojibake; see https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for likely causes. – Rick James Jan 08 '19 at 00:45

2 Answers2

2

if you want to store pinyin in mysql database ,you have to encode it by base64_encode(); and at the time of display you have to encode it by base64_decode(); and one of most important ,you have to use header('Content-Type: text/html; charset=utf8 general ci'); at the top of page

Govinda Yadav
  • 539
  • 4
  • 14
2

I figured it out. It was a collating issue. I modified the cnf to set the collation_server variable to utf8_general_ci, then reimported my data and it works fine... I don't know why I didn't think of that earlier.

Beaker
  • 1,633
  • 13
  • 22