DATABASEのCOLLATION

作成するときにDBのCOLLATIONを決めるが、既に作成してあるデータベースの
COLLATIONを確認する方法をいかに3つメモしておきます。

—————————————————————————-
1: SHOW CREATE DATABASEで確認
—————————————————————————-


mysql> show create database STUDY;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| STUDY | CREATE DATABASE `STUDY` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

database_collation

—————————————————————————-
2: データベースのフォルダー以下にあるdb.optファイルで確認。
—————————————————————————-

[root@colinux STUDY]# pwd
/usr/local/mysql/data/STUDY
[root@colinux STUDY]# ls -l db.opt
-rw-rw—- 1 mysql mysql 61 2009-07-11 23:06 db.opt
[root@colinux STUDY]# cat db.opt
default-character-set=utf8
default-collation=utf8_general_ci
[root@colinux STUDY]#

opt

—————————————————————————-
3: information_schemaを確認
—————————————————————————-

mysql> desc SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | YES | | NULL | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(64) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql>
mysql> desc SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+---------+-------+
| CATALOG_NAME | varchar(512) | YES | | NULL | |
| SCHEMA_NAME | varchar(64) | NO | | | |
| DEFAULT_CHARACTER_SET_NAME | varchar(64) | NO | | | |
| DEFAULT_COLLATION_NAME | varchar(64) | NO | | | |
| SQL_PATH | varchar(512) | YES | | NULL | |
+----------------------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql>

schema_db

—————————————————————————–
追記: DBの文字コードとCOLLATION変更
—————————————————————————–

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | utf8 | utf8_general_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

mysql> alter database TEST default character set latin1 COLLATE latin1_swedish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME from information_schema.SCHEMATA;
+--------------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+--------------------+----------------------------+------------------------+
| information_schema | utf8 | utf8_general_ci |
| CaseSensitive | utf8 | utf8_general_ci |
| DB01 | utf8 | utf8_general_ci |
| DB02 | utf8 | utf8_general_ci |
| MyAdmin | utf8 | utf8_general_ci |
| STUDY | utf8 | utf8_general_ci |
| TEST | latin1 | latin1_swedish_ci |
| client_test_db | utf8 | utf8_general_ci |
| mysql | utf8 | utf8_general_ci |
+--------------------+----------------------------+------------------------+
9 rows in set (0.01 sec)

db.optも変わっている。

[root@colinux TEST]# pwd
/usr/local/mysql/data/TEST
[root@colinux TEST]# cat db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
[root@colinux TEST]#

alter_database1

Comments are closed.

Post Navigation