How MySQL converts latin1 encoding to UTF-8 encoding

Many people may have this experience: clearly all systems are set with UTF-8, but with phpMyAdmin to see the database, found that the text is still garbled.

If this happens, because although mysql itself and the webpage are both UTF-8, xoops uses latin1 to transmit data to mysql. Therefore, UTF-8 data is stored in the database in latin1 format. In fact, there is no problem in xoops, but when phpMyAdmin is used, it is garbled and difficult to manage.

The following is the environment:
Mysql: 5.1.22
Mysql text encoding: UTF-8 unicode
Mysql connection Proofreader: utf-8_unicode_ci
All databases are created with UTF-8.
PhpMyAdmin: 2.11.3 (select Chinese-Chinese Traditional for Language 」)
Xoops 2.0.17.1

For this situation, the solution is as follows:

1. mysql settings:
/Etc/my. cnf (or my. ini in Windows) must have the following settings:

[Client]
Default-character-set = utf8

[Mysqld]
Default-character-set = utf8
Init_connect = 'set NAMES utf8'

2. On the xoops side, previous predecessors in the http://xoops.org.cn to tell you
Add xoops/class/database/mysqldatabase. php to the following line:
@ Mysql_query ("set names 'utf8'", $ this-> conn );
(Add this row before $ result = mysql_query ($ SQL, $ this-> conn)
You can force utf8 transmission.

3. mySQL database:
But what if there is an old database? After the above method is changed, all the text in the previous step becomes garbled, and the text entered or re-entered will be displayed normally.

It doesn't matter. You can perform the following steps to transcode the previous database:

First, use the following method to dump the database:
Mysqldump -- all-databases -- default-character-set = latin1-u root-p> dump. SQL

Modify the dump. SQL file homepage:
/*! 40101 set names latin1 */;
Become
/*! 40101 set names utf8 */;

Then import the database back:
Mysql-u root-p <dump. SQL

Open xoops again, and everything in Chinese is OK. Go to phpMyAdmin and check whether the code is garbled.

Have a nice day!