MyBBに「文字コードがバラバラだよ！」と叱られて、今頃になってやっと設定。

my.cnfの設定

まずは設定前の状態を確認してみる。

# mysql -u root -p
Enter password:
mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | latin1                           |
| character_set_connection | latin1                           |
| character_set_database   | latin1                           |
| character_set_filesystem | binary                           |
| character_set_results    | latin1                           |
| character_set_server     | latin1                           |
| character_set_system     | latin1                           |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> quit;

my.cnfに次のように文字コードのデフォルト設定を追加。（文字コード設定以外の部分は全部省略。）

[client]
default-character-set = utf8

[mysqld]
character-set-server = utf8

client以外にmysqlやmysqldumpのディレクティブにもデフォルト設定を追加している人がいるが、clientに定義した内容はmysqldを除くすべてのディレクティブに共通の設定となるため、clientにだけ書けば良い。

設定したらMySQLサーバを再起動する。

# sudo service apache22 restart

文字コードのデフォルトがちゃんと変更されているのを確認。

# mysql -u root -p
Enter password:
mysql> show variables like 'char%';
+--------------------------+----------------------------------+
| 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     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
mysql> quit;

既存テーブルの文字コード変更

デフォルトを変更しただけではテーブル内に保存されているデータの文字コードまでは変換されないので、テーブルは別途変換する必要がある。

文字コードの確認
文字コード変換のSQL
全データベースの全テーブルをまとめて変換
全データベースのデフォルト文字コードとエンジン名を表示

文字コードの確認

データベースのデフォルト文字コードは、次のようにして「show create table <データベース名>」で確認できる。

# mysql -u root -p
Enter password:
mysql> show create database pnd;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| pnd      | CREATE DATABASE `pnd` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.01 sec)
mysql>

同様に、テーブルも次のようにして「show create table <テーブル名>」で確認できる。

mysql> show create table pnd_options \G
(略）
) ENGINE=InnoDB AUTO_INCREMENT=2207 DEFAULT CHARSET=utf8

CHARSET= の後ろが文字コード。

文字コード変換のSQL

テーブル名を指定して、ひとつずつ変換する。

mysql> alter table <テーブル名> convert to character set <文字コード>;

でも手入力だと面倒なので、シェルスクリプトでまとめてやりたい。

全データベースの全テーブルをまとめて変換

シェルスクリプトを作成して実行。

#!/usr/local/bin/bash

PASS='rootPassword';

function run_sql() {
  mysql --defaults-file=<(printf '[client]\npassword=%s\n' ${PASS}) \
  --skip-column-names -u root
}

DATABASES=`run_sql <<EOF
show databases;
EOF`

for DATABASE in $DATABASES
do
if ([ $DATABASE = 'mysql' ] ||
        [ $DATABASE = 'performance_schema' ] ||
        [ $DATABASE = 'information_schema' ]); then
  continue;
fi
TABLES=`run_sql <<EOF
use $DATABASE;
show tables;
EOF`

for TABLE in $TABLES
do
run_sql <<EOF
alter table $DATABASE.$TABLE convert to character set utf8;
EOF
done
echo "$DATABASE - $TABLE"
done

run_sql関数は、パスワードを引数として直接指定せずに中間ファイル経由で渡すためのもの。この記述の仕方がbash特有の機能なので、他のシェルでは動かない。

–defaults-fileオプションを最初に指定しないとシェル実行がエラーとなった。理由はわからないけど、他の引数は後ろにつけないとダメみたい。

全データベースのデフォルト文字コードとエンジン名を表示

今回の目的とはちょっと違うけど、全部無条件に変換したら時間がかかりそうな場合、まずどれくらいのテーブルを変換するのか先に確認しておきたいときに使えるスクリプトを作ってみた。

#!/usr/local/bin/bash

PASS='rootPassword';

function run_sql() {
 mysql --defaults-file=<(printf '[client]\npassword=%s\n' ${PASS}) \
 --skip-column-names -u root
}

DATABASES=`run_sql <<EOF
show databases;
EOF`

for DATABASE in $DATABASES
do
if ([ $DATABASE = 'mysql' ] ||
 [ $DATABASE = 'performance_schema' ] ||
 [ $DATABASE = 'information_schema' ]); then
 continue;
fi
TABLES=`run_sql <<EOF
use $DATABASE;
show tables;
:q
71 yuka@secondary> cat test.sh
#!/usr/local/bin/bash

PASS='Ender';

function run_sql() {
 mysql --defaults-file=<(printf '[client]\npassword=%s\n' ${PASS}) \
 --skip-column-names -u root
}

DATABASES=`run_sql <<EOF
show databases;
EOF`

for DATABASE in $DATABASES
do
if ([ $DATABASE = 'mysql' ] ||
 [ $DATABASE = 'performance_schema' ] ||
 [ $DATABASE = 'information_schema' ]); then
 continue;
fi
TABLES=`run_sql <<EOF
use $DATABASE;
show tables;
EOF`

for TABLE in $TABLES
do
echo -n "$DATABASE - $TABLE: "

CREATEOPT=`run_sql <<EOF
show create table ${DATABASE}.${TABLE} \G
EOF`
echo $CREATEOPT | sed -e 's/.*\(ENGINE=[a-zA-Z]*\)[ ].*\(CHARSET=[a-zA-Z0-9]*\) *.*/\1 \2/'

done
done