MySQLでキャラクタセットを変更する

キャラクタセットはカラムレベルで持っている

Oracleの場合はデータベースレベルで持っているキャラクタセット定義ですが、MySQLの場合、キャラクタセットはカラムレベルで持っています。

ですので、同じデータベース、テーブルの中で、カラムによって別々のキャラクタセットを持つこともMySQLでは可能です。（誰がそんな使い方するんだよ）

そして、データベース、テーブルレベルで「デフォルトの」キャラクタセットは定義できますが、あくまでカラム定義時に明示指定しなかった時の為のデフォルト値でしかありません。

キャラクタセットは「変更」できるか

Oracleの場合、10gから「ALTER DATABASE CHARACTER SET」文は廃止されているようですね。

MySQL的には、「ALTER DATABASE CHARACTER SET」で「デフォルトキャラクタセット」が変更できますが、この文では、カラムレベルの定義までは変更されませんし、当然格納されているデータにも波及しません。

MySQLで、実際にキャラクタセット定義を持っている「カラムレベル」での変更を行うには、以下のDDLで対応しています。

ALTER TABLE CHANGE 旧カラム名 新カラム名 新データ型 CHARACTER SET 新キャラクタセット名

ただし、データが格納されている場合、格納されているデータが変更後のキャラクタセットと互換性がないと、エラーがになります。

実験

以下、utf8mb4で作成したカラムに、日本語の文字を格納して、latin1への変更を試みた結果です。

mysql> CREATE DATABASE test_char_change DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE test_char_change.tbl_a (
    ->   col1 int(11) NOT NULL DEFAULT '0' PRIMARY KEY,
    ->   col2 varchar(10) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW CREATE TABLE test_char_change.tbl_a\G
*************************** 1. row ***************************
       Table: tbl_a
Create Table: CREATE TABLE `tbl_a` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> SELECT table_schema,table_name,column_name,column_type,character_set_name,collation_name FROM information_schema.columns WHERE table_schema = 'test_char_change';
+------------------+------------+-------------+-------------+--------------------+----------------+
| table_schema     | table_name | column_name | column_type | character_set_name | collation_name |
+------------------+------------+-------------+-------------+--------------------+----------------+
| test_char_change | tbl_a      | col1        | int(11)     | NULL               | NULL           |
| test_char_change | tbl_a      | col2        | varchar(10) | utf8mb4            | utf8mb4_bin    |
+------------------+------------+-------------+-------------+--------------------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test_char_change.tbl_a (col1,col2) VALUES (1,'abcde');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test_char_change.tbl_a (col1,col2) VALUES (2,'あいうえお');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test_char_change.tbl_a;
+------+-----------------+
| col1 | col2            |
+------+-----------------+
|    1 | abcde           |
|    2 | あいうえお      |
+------+-----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE test_char_change.tbl_a CHANGE col2 col2 varchar(10) CHARACTER SET latin1;
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82\xE3\x81\x84...' for column 'col2' at row 2
mysql> DELETE FROM test_char_change.tbl_a WHERE col1 = 2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test_char_change.tbl_a;
+------+-------+
| col1 | col2  |
+------+-------+
|    1 | abcde |
+------+-------+
1 row in set (0.00 sec)

mysql> ALTER TABLE test_char_change.tbl_a CHANGE col2 col2 varchar(10) CHARACTER SET latin1;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT table_schema,table_name,column_name,column_type,character_set_name,collation_name FROM information_schema.columns WHERE table_schema = 'test_char_change';
+------------------+------------+-------------+-------------+--------------------+-------------------+
| table_schema     | table_name | column_name | column_type | character_set_name | collation_name    |
+------------------+------------+-------------+-------------+--------------------+-------------------+
| test_char_change | tbl_a      | col1        | int(11)     | NULL               | NULL              |
| test_char_change | tbl_a      | col2        | varchar(10) | latin1             | latin1_swedish_ci |
+------------------+------------+-------------+-------------+--------------------+-------------------+
2 rows in set (0.00 sec)