MySQL5と文字コード

（自分の中で）諸説入り乱れているので実験したメモです。
/etc/my.confを書き換える
書き換える前に

show variables like '%char%';

をやった結果

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/


1. [client]セクションで
default-character-set=utf8

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/
変化なし

2. [mysqld]セクションで
default-character-set=utf8

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/


3.[mtsqld]セクションで
character-set-server = utf8

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/
変化なし

4.[mysqld]セクションで
init-connect = SET NAMES utf8

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/
変化なし

5.[mysqldump]セクションで
default-character-set=utf8
だったのをコメントアウト

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/
変化なし

6.[mysql]セクションで
default-character-set=utf8

character_set_client latin1
character_set_connection latin1
character_set_database ujis
character_set_filesystem binary
character_set_results latin1
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/
変化なし

my.confでは「server」と「system」しか変わらないご様子。

7.MySQLクライアントSequel Proの接続設定を変更（「環境設定」→「General」→「Default encoding」をutf8に

character_set_client utf8
character_set_connection utf8
character_set_database latin1（なんで？）
character_set_filesystem binary
character_set_results utf8
character_set_server latin1（なんで？）
character_set_system utf8
character_sets_dir /usr/local/mysql-5.1.45-osx10.6-x86_64/share/charsets/


ということは、文字化けするのはクライアント側の設定か。。

今回悩んでいた（懐かしの）XOOPSのコンテンツが「????」で表示されない件は
/class/database/mysqldatabase.php
の最初の関数connectに文字セットの設定の関数 mysql_set_charsetを追加（ただしPHP 5 >= 5.2.3)の模様）。

/**
* connect to the database
*
* @param bool $selectdb select the database now?
* @return bool successful?
*/
function connect($selectdb = true)
{
if (XOOPS_DB_PCONNECT == 1) {
$this->conn = @mysql_pconnect(XOOPS_DB_HOST, XOOPS_DB_USER, XOOPS_DB_PASS);
} else {
$this->conn = @mysql_connect(XOOPS_DB_HOST, XOOPS_DB_USER, XOOPS_DB_PASS);
}
if (!$this->conn) {
$this->logger->addQuery('', $this->error(), $this->errno());
return false;
}
if($selectdb != false){
if (!mysql_select_db(XOOPS_DB_NAME)) {
$this->logger->addQuery('', $this->error(), $this->errno());
return false;
}
}
if(!mysql_set_charset('ujis', $this->conn)){
$this->logger->addQuery('', $this->error(), $this->errno());
return false;
}
return true;
}


Tweet