MySQLの初期設定(1)
MySQLの初期設定(2)
の続き。
現在の文字コード設定を確認する
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 | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.01 sec)
デフォルトのlatin1だと日本語が文字化けしてしまう。
設定ファイルでデフォルトの文字コードを変更する
mysql> exit Bye # vi /etc/my.cnf
修正前
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
修正後
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 default-character-set=utf8 skip-character-set-client-handshake [client] default-character-set=utf8 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
MySQLを再起動する
# service mysqld restart MySQL を停止中: [ OK ] MySQL を起動中: [ OK ]
設定の確認をする
# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
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/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
既に作成済みのデータベース・テーブルは個別に修正する
データベースの場合
mysql> show create database mydb; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter database mydb default character set utf8; Query OK, 1 row affected (0.00 sec) mysql> show create database mydb; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | mydb | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
テーブルの場合
mysql> show create table mytable; +---------+---------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+---------------------------------------------------------------------------------------------+ | mytable | CREATE TABLE `mytable` ( `id` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +---------+---------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table mytable default character set utf8; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table mytable; +---------+-------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------+ | mytable | CREATE TABLE `mytable` ( `id` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---------+-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
