MySQL8.0における文字セットの変更：
デフォルトの文字セットがlatin1からutf8mb4に変更されます。 これらのシステム変数は次のような影響を受けます。character_set_serverおよびcharacter_set_databaseシステム変数のデフォルト値は、latin1からutf8mb4に変更。collation_serverおよびcollation_databaseシステム変数のデフォルト値は、latin1_swedish_ciからutf8mb4_0900_ai_ciに変更。
MySQL8.0では、Unicode9までアップグレードされています、またutf8mb4(4byte)では絵文字を含む文字も処理する事が出来る為、バージョンアップと共に文字コードや照合順序を変更される事もあるかと思います。簡単ですが、手順を再確認して見ました。
MySQL8.0にアップグレードする前に,mysqlsh8.xのアップグレード確認ユーティリティでチェックするのもお勧めです。文字セットも確認してくれます。
MySQL8.0における、基本的な機能の変更点
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
■ スキーマの文字セット変換
mysql -uroot -p information_schema -e "select SCHEMA_NAME from SCHEMATA where DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' and SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')" --batch --skip-column-names | xargs -I{} echo 'alter SCHEMA `'{}'` DEFAULT CHARSET utf8mb4;' > alter_schema_default_change.sql
■ テーブル文字セット若しくは、テーブルと列の文字セット変換
※ 状況に応じて使い分けて下さい。
mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = '<DATABASE>'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
or
mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = '<DATABASE>'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql
■ 変換後の確認
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.COLUMNS where CHARACTER_SET_NAME <> 'utf8mb4';
■ Schemaの文字セット（変更前）
[information_schema]> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA where SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys');
+--------------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| AdventureWorks2012 | utf8 |
| BENCHMARK | utf8 |
| GIS | utf8mb4 |
| IOT | utf8mb4 |
| MYDB | utf8mb4 |
| OU | utf8mb4 |
| PrivDB | utf8 |
| RewriteA | utf8 |
| audit_information | utf8mb4 |
| info | utf8mb4 |
| jposm | utf8mb4 |
| myosm | utf8mb4 |
| partitioning | utf8mb4 |
| test | latin1 |
| world | utf8 |
+--------------------+----------------------------+
15 rows in set (0.00 sec)
[root@GA01 convert]# mysql -uroot -p information_schema -e "select SCHEMA_NAME from SCHEMATA where DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' and SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys')" --batch --skip-column-names | xargs -I{} echo 'alter SCHEMA `'{}'` DEFAULT CHARSET utf8mb4;' > alter_schema_default_change.sql
Enter password:
[root@GA01 convert]# cat alter_schema_default_change.sql
alter SCHEMA `AdventureWorks2012` DEFAULT CHARSET utf8mb4;
alter SCHEMA `BENCHMARK` DEFAULT CHARSET utf8mb4;
alter SCHEMA `PrivDB` DEFAULT CHARSET utf8mb4;
alter SCHEMA `RewriteA` DEFAULT CHARSET utf8mb4;
alter SCHEMA `test` DEFAULT CHARSET utf8mb4;
alter SCHEMA `world` DEFAULT CHARSET utf8mb4;
[root@GA01 convert]# mysql -u root -p ＜ alter_schema_default_change.sql
Enter password:
[root@GA01 convert]#
スキーマの文字セットが変換された事を確認
[information_schema]> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA where SCHEMA_NAME NOT IN('mysql','information_schema','performance_schema','sys');
+--------------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| AdventureWorks2012 | utf8mb4 |
| BENCHMARK | utf8mb4 |
| GIS | utf8mb4 |
| IOT | utf8mb4 |
| MYDB | utf8mb4 |
| OU | utf8mb4 |
| PrivDB | utf8mb4 |
| RewriteA | utf8mb4 |
| audit_information | utf8mb4 |
| info | utf8mb4 |
| jposm | utf8mb4 |
| myosm | utf8mb4 |
| partitioning | utf8mb4 |
| test | utf8mb4 |
| world | utf8mb4 |
+--------------------+----------------------------+
15 rows in set (0.00 sec)
utf8mb4以外のスキーマ(データベース)の変換スクリプト作成と実行
■ Tableの変換(テーブルのDefault文字セットのみ)
SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name
FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C
WHERE C.collation_name = T.table_collation AND T.table_schema = "<DATABASE>";
[information_schema]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` C WHERE C.collation_name = T.table_collation AND T.table_schema = "AdventureWorks2012" limit 5;
+--------------------+-----------------+-----------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | character_set_name |
+--------------------+-----------------+-----------------+--------------------+
| AdventureWorks2012 | AWBuildVersion | utf8_general_ci | utf8 |
| AdventureWorks2012 | Address | utf8_general_ci | utf8 |
| AdventureWorks2012 | AddressType | utf8_general_ci | utf8 |
| AdventureWorks2012 | BillOfMaterials | utf8_general_ci | utf8 |
| AdventureWorks2012 | BusinessEntity | utf8_general_ci | utf8 |
+--------------------+-----------------+-----------------+--------------------+
5 rows in set (0.01 sec)
テーブルの文字セット変換スクリプト作成と実行
[root@GA01 convert]# mysql -uroot -p AdventureWorks2012 -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
Enter password:
[root@GA01 convert]# cat alter_tables_default_change.sql
alter table `AWBuildVersion` DEFAULT CHARSET utf8mb4;
<SNIP>
alter table `WorkOrderRouting` DEFAULT CHARSET utf8mb4;
[root@GA01 convert]# mysql -u root -p AdventureWorks2012 ＜ alter_tables_default_change.sql
Enter password:
[root@GA01 convert]#
変換結果としては、問題無くテーブルのDefault文字セットはutf8mb4に変換されている。
[information_schema]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` C WHERE C.collation_name = T.table_collation AND T.table_schema = "AdventureWorks2012" limit 5;
+--------------------+-----------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | character_set_name |
+--------------------+-----------------+--------------------+--------------------+
| AdventureWorks2012 | AWBuildVersion | utf8mb4_general_ci | utf8mb4 |
| AdventureWorks2012 | Address | utf8mb4_general_ci | utf8mb4 |
| AdventureWorks2012 | AddressType | utf8mb4_general_ci | utf8mb4 |
| AdventureWorks2012 | BillOfMaterials | utf8mb4_general_ci | utf8mb4 |
| AdventureWorks2012 | BusinessEntity | utf8mb4_general_ci | utf8mb4 |
+--------------------+-----------------+--------------------+--------------------+
5 rows in set (0.01 sec)
但し、上記のようにテーブルのDefault文字セットを変換しただけの場合、以下の様にテーブルのカラムなどの文字コードは変換されていない。
[AdventureWorks2012]> show create table Store\G
*************************** 1. row ***************************
Table: Store
Create Table: CREATE TABLE `Store` (
`BusinessEntityID` int(11) NOT NULL COMMENT 'Primary key. Foreign key to Customer.BusinessEntityID.',
`Name` varchar(100) NOT NULL COMMENT 'Name of the store.',
`SalesPersonID` int(11) DEFAULT NULL COMMENT 'ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.',
`Demographics` text CHARACTER SET utf8 COMMENT 'Demographic informationg about the store such as the number of employees, annual sales and store type.',
`rowguid` varchar(64) CHARACTER SET utf8 NOT NULL COMMENT 'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.',
`ModifiedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated.',
PRIMARY KEY (`BusinessEntityID`),
UNIQUE KEY `rowguid` (`rowguid`),
UNIQUE KEY `AK_Store_rowguid` (`rowguid`),
KEY `IX_Store_SalesPersonID` (`SalesPersonID`),
KEY `PXML_Store_Demographics` (`Demographics`(255)),
CONSTRAINT `FK_Store_BusinessEntity_BusinessEntityID` FOREIGN KEY (`BusinessEntityID`) REFERENCES `BusinessEntity` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Store_SalesPerson_SalesPersonID` FOREIGN KEY (`SalesPersonID`) REFERENCES `SalesPerson` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customers (resellers) of Adventure Works products.'
1 row in set (0.00 sec)
■ テーブルのデフォルトの文字セットおよびすべての文字カラム (CHAR、VARCHAR、TEXT) を新しい文字セットに変更するには、次のようなステートメントを使用。
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
注意：このステートメントでは、すべての文字カラムの照合順序も変更されます。使用する照合順序を示す COLLATE 句を指定しない場合、このステートメントは、その文字セットのデフォルトの照合順序を使用します。この照合順序が目的とするテーブル使用に適していない (たとえば、大文字と小文字が区別される照合順序から大文字と小文字が区別されない照合順序に変更されてしまう) 場合は、照合順序を明示的に指定します。
[root@GA01 convert]# mysql -uroot -p AdventureWorks2012 -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql
Enter password:
[root@GA01 convert]# cat alter_tables_convert.sql
alter table `AWBuildVersion` convert to character set utf8mb4;
alter table `Address` convert to character set utf8mb4;
<SNIP>
alter table `WorkOrderRouting` convert to character set utf8mb4;
[root@GA01 convert]#
以下のテーブルは、文字セットと照合順序変更に伴いFK制約に引っかかってしまったので、マニュアルで対応する。
[root@GA01 convert]# mysql -u root -p AdventureWorks2012 ＜ alter_tables_convert.sql
Enter password:
ERROR 1832 (HY000) at line 34: Cannot change column ‘DocumentNode’: used in a foreign key constraint ‘FK_ProductDocument_Document_DocumentNode’
[root@GA01 convert]#
FKを一次的にOFFにして、対象テーブルをマニュアル変換
[AdventureWorks2012]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)
[AdventureWorks2012]> alter table `ProductDocument` convert to character set utf8mb4;alter table `Product` convert to character set utf8mb4;
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[AdventureWorks2012]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
該当テーブルを変換後は問題無く、スクリプトでまとめて変更する事が出来た。全て、UTF8MB4に統一された事も確認
[root@GA01 convert]# mysql -u root -p AdventureWorks2012 ＜ alter_tables_convert.sql
Enter password:
[root@GA01 convert]#
root@localhost [AdventureWorks2012]> show create table Store\G
*************************** 1. row ***************************
Table: Store
Create Table: CREATE TABLE `Store` (
`BusinessEntityID` int(11) NOT NULL COMMENT 'Primary key. Foreign key to Customer.BusinessEntityID.',
`Name` varchar(100) NOT NULL COMMENT 'Name of the store.',
`SalesPersonID` int(11) DEFAULT NULL COMMENT 'ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID.',
`Demographics` mediumtext COMMENT 'Demographic informationg about the store such as the number of employees, annual sales and store type.',
`rowguid` varchar(64) NOT NULL COMMENT 'ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.',
`ModifiedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date and time the record was last updated.',
PRIMARY KEY (`BusinessEntityID`),
UNIQUE KEY `rowguid` (`rowguid`),
UNIQUE KEY `AK_Store_rowguid` (`rowguid`),
KEY `IX_Store_SalesPersonID` (`SalesPersonID`),
KEY `PXML_Store_Demographics` (`Demographics`(255)),
CONSTRAINT `FK_Store_BusinessEntity_BusinessEntityID` FOREIGN KEY (`BusinessEntityID`) REFERENCES `BusinessEntity` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_Store_SalesPerson_SalesPersonID` FOREIGN KEY (`SalesPersonID`) REFERENCES `SalesPerson` (`BusinessEntityID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customers (resellers) of Adventure Works products.'
1 row in set (0.00 sec)
■■■ 変換前と後のデータ確認 ■■■
変換前
[world]> select name,CountryCode,hex(weight_string(name)),hex(weight_string(CountryCode)) from City3 where CountryCode = '日本' limit 3;
+--------+-------------+--------------------------+---------------------------------+
| name | CountryCode | hex(weight_string(name)) | hex(weight_string(CountryCode)) |
+--------+-------------+--------------------------+---------------------------------+
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
+--------+-------------+--------------------------+---------------------------------+
3 rows in set (0.00 sec)
テーブルの文字コード変換スクリプト作成と実行、TABLEの照合順序がCity2だけutf8mb4_binのまま残っている。
[root@GA01 admin]# mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND character_set_name <> 'utf8mb4' AND T.table_schema = 'world'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` DEFAULT CHARSET utf8mb4;' > alter_tables_default_change.sql
Enter password:
[root@GA01 admin]# cat alter_tables_default_change.sql
alter table `City` DEFAULT CHARSET utf8mb4;
alter table `Country` DEFAULT CHARSET utf8mb4;
alter table `CountryLanguage` DEFAULT CHARSET utf8mb4;
alter table `Demo_City` DEFAULT CHARSET utf8mb4;
alter table `Demo_Country` DEFAULT CHARSET utf8mb4;
alter table `Demo_CountryLanguage` DEFAULT CHARSET utf8mb4;
alter table `Wingarc` DEFAULT CHARSET utf8mb4;
alter table `成績表` DEFAULT CHARSET utf8mb4;
alter table `City3` DEFAULT CHARSET utf8mb4;
alter table `City4` DEFAULT CHARSET utf8mb4;
alter table `filler` DEFAULT CHARSET utf8mb4;
[root@GA01 admin]# mysql -u root -p world ＜ alter_tables_default_change.sql
Enter password:
root@localhost [world]>SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world';
+--------------+----------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | character_set_name |
+--------------+----------------------+--------------------+--------------------+
| world | 成績表 | utf8mb4_general_ci | utf8mb4 |
| world | City | utf8mb4_general_ci | utf8mb4 |
| world | City3 | utf8mb4_general_ci | utf8mb4 |
| world | City4 | utf8mb4_general_ci | utf8mb4 |
| world | Country | utf8mb4_general_ci | utf8mb4 |
| world | CountryLanguage | utf8mb4_general_ci | utf8mb4 |
| world | Demo_City | utf8mb4_general_ci | utf8mb4 |
| world | Demo_Country | utf8mb4_general_ci | utf8mb4 |
| world | Demo_CountryLanguage | utf8mb4_general_ci | utf8mb4 |
| world | Wingarc | utf8mb4_general_ci | utf8mb4 |
| world | filler | utf8mb4_general_ci | utf8mb4 |
| world | City2 | utf8mb4_bin | utf8mb4 |
+--------------+----------------------+--------------------+--------------------+
12 rows in set (0.01 sec)
一度、テーブルのcharacter_setを上記のステップでutf8mb4に変換しているのでここでは外して、テーブルと列の文字セット変換スクリプトを実行したが、FKのエラーになったので、FKを無効にして関連テーブルをマニュアルで変換しておく。
※テーブルだけでは無く、列の文字セットや照合順序も変わるので大文字小文字の区別などが変わると困るテーブルなどは個別にマニュアル対応して下さい。
[root@GA01 admin]# mysql -uroot -p information_schema -e "select T.TABLE_NAME from information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world'" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;' > alter_tables_convert.sql
Enter password:
[root@GA01 admin]# cat alter_tables_convert.sql
alter table `成績表` convert to character set utf8mb4;
alter table `City` convert to character set utf8mb4;
alter table `City3` convert to character set utf8mb4;
alter table `City4` convert to character set utf8mb4;
alter table `Country` convert to character set utf8mb4;
alter table `CountryLanguage` convert to character set utf8mb4;
alter table `Demo_City` convert to character set utf8mb4;
alter table `Demo_Country` convert to character set utf8mb4;
alter table `Demo_CountryLanguage` convert to character set utf8mb4;
alter table `Wingarc` convert to character set utf8mb4;
alter table `filler` convert to character set utf8mb4;
alter table `City2` convert to character set utf8mb4;
[root@GA01 admin]# mysql -u root -p world ＜ alter_tables_convert.sql
Enter password:
ERROR 1832 (HY000) at line 2: Cannot change column 'CountryCode': used in a foreign key constraint 'city_ibfk_1'
[root@GA01 admin]#
制約でエラーになったテーブルは、個別に対応してエラーを回避
root@localhost [world]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)
root@localhost [world]> alter table `City` convert to character set utf8mb4;
Query OK, 4079 rows affected (0.27 sec)
Records: 4079 Duplicates: 0 Warnings: 0
root@localhost [world]> alter table `Country` convert to character set utf8mb4;
Query OK, 239 rows affected (0.15 sec)
Records: 239 Duplicates: 0 Warnings: 0
root@localhost [world]> alter table `CountryLanguage` convert to character set utf8mb4;
Query OK, 984 rows affected (0.21 sec)
Records: 984 Duplicates: 0 Warnings: 0
root@localhost [world]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)
対象テーブルの制約は対応済みなので、問題無く変換が終了。
[root@GA01 admin]# mysql -u root -p world ＜ alter_tables_convert.sql
Enter password:
[root@GA01 admin]#
全てのテーブルの文字コードも照合順序も適切に変換された事を確認。
root@localhost [world]> SELECT T.TABLE_SCHEMA,T.TABLE_NAME,T.TABLE_COLLATION,C.character_set_name FROM information_schema.TABLES as T,information_schema.COLLATION_CHARACTER_SET_APPLICABILITY as C WHERE C.collation_name = T.table_collation AND T.table_schema = 'world';
+--------------+----------------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION | character_set_name |
+--------------+----------------------+--------------------+--------------------+
| world | 成績表 | utf8mb4_general_ci | utf8mb4 |
| world | City | utf8mb4_general_ci | utf8mb4 |
| world | City2 | utf8mb4_general_ci | utf8mb4 |
| world | City3 | utf8mb4_general_ci | utf8mb4 |
| world | City4 | utf8mb4_general_ci | utf8mb4 |
| world | Country | utf8mb4_general_ci | utf8mb4 |
| world | CountryLanguage | utf8mb4_general_ci | utf8mb4 |
| world | Demo_City | utf8mb4_general_ci | utf8mb4 |
| world | Demo_Country | utf8mb4_general_ci | utf8mb4 |
| world | Demo_CountryLanguage | utf8mb4_general_ci | utf8mb4 |
| world | Wingarc | utf8mb4_general_ci | utf8mb4 |
| world | filler | utf8mb4_general_ci | utf8mb4 |
+--------------+----------------------+--------------------+--------------------+
12 rows in set (0.01 sec)
変換後もutf8からutf8mb4への変換なので特にデータに問題は無い。絵文字もutf8mb4なので問題無くINSERT出来る事を確認。
root@localhost [world]> select name,CountryCode,hex(weight_string(name)),hex(weight_string(CountryCode)) from City3 where CountryCode = '日本' limit 3;
+--------+-------------+--------------------------+---------------------------------+
| name | CountryCode | hex(weight_string(name)) | hex(weight_string(CountryCode)) |
+--------+-------------+--------------------------+---------------------------------+
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
| Nagoya | 日本 | 004E00410047004F00590041 | 65E5672C |
+--------+-------------+--------------------------+---------------------------------+
3 rows in set (0.00 sec)
root@localhost [world]> INSERT INTO City3(Name,CountryCode,District,Population) VALUES ('Emoji','日本','\U+1F363',1780000);
Query OK, 1 row affected (0.01 sec)
root@localhost [world]>
文字関連のデータ型に関しては、キャラクターセットも照合順序も問題無い事が確認出来る。
root@localhost [world]> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME from information_schema.COLUMNS WHERE TABLE_SCHEMA = 'world' and TABLE_NAME = 'City3';
+--------------+------------+-------------+--------------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME |
+--------------+------------+-------------+--------------------+--------------------+
| world | City3 | ID | NULL | NULL |
| world | City3 | Name | utf8mb4 | utf8mb4_general_ci |
| world | City3 | CountryCode | utf8mb4 | utf8mb4_general_ci |
| world | City3 | District | utf8mb4 | utf8mb4_general_ci |
| world | City3 | Population | NULL | NULL |
+--------------+------------+-------------+--------------------+--------------------+
5 rows in set (0.00 sec)
root@localhost [world]> show create table City3\G
*************************** 1. row ***************************
Table: City3
Create Table: CREATE TABLE `City3` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `idx_City3` (`CountryCode`,`District`)
) ENGINE=InnoDB AUTO_INCREMENT=225200 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
詳細：
Collation変更にあたり、色々と考慮しなければいけない事がまとめられていて参考になります。
https://mysqlserverteam.com/mysql-8-0-collations-migrating-from-older-collations/
特定の文字セットを使用するように、バイナリ文字列または非バイナリ文字列カラムを変換する為の注意点が書いてあります。
抜粋：正しく変換が行われるには、次の条件のいずれかを適用する必要があります。
1)カラムにバイナリデータ型 (BINARY、VARBINARY、BLOB) がある場合、含まれるすべての値は、単一の文字セット (カラムの変換後の文字セット) を使用してエンコードされる必要があります。
バイナリカラムを使用して複数の文字セットで情報を格納する場合、MySQL はどの値がどの文字セットを使用するかを認識できず、データを正確に変換できません。
2)カラムに非バイナリデータ型 (CHAR、VARCHAR、TEXT) がある場合、その内容は、カラムの文字セットでエンコードする必要があり、ほかの文字セットは使用できません。
内容が別の文字セットでエンコードされている場合、最初にバイナリデータ型を使用するようにカラムを変換してから、使用する文字セットで非バイナリカラムに変換できます。
https://dev.mysql.com/doc/refman/5.6/ja/charset-conversion.html
ALTER TABLE を使用して、ある文字セットから別の文字セットにカラムを変換する場合、MySQL はデータ値をマップしようとしますが、文字セットに互換性がない場合、データの損失が生じる可能性があります。
https://dev.mysql.com/doc/refman/5.6/ja/charset-column.html
CONVERT TO CHARACTER SETを利用する場合の注意に関しては、此方も合わせて確認して下さい。
https://dev.mysql.com/doc/refman/5.6/ja/alter-table.html
■■■■ 文字セットや照合順の互換性確認 ■■■■
■ 照合順の互換性
root@localhost [information_Schema]> SELECT _utf8 'x' COLLATE utf8_general_ci;
+-----------------------------------+
| _utf8 'x' COLLATE utf8_general_ci |
+-----------------------------------+
| x |
+-----------------------------------+
1 row in set (0.00 sec)
root@localhost [information_Schema]> SELECT _utf8 'x' COLLATE utf8mb4_general_ci;
ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'
root@localhost [information_Schema]> SELECT _utf8mb4 'x' COLLATE utf8mb4_general_ci;
+-----------------------------------------+
| _utf8mb4 'x' COLLATE utf8mb4_general_ci |
+-----------------------------------------+
| x |
+-----------------------------------------+
1 row in set (0.00 sec)
root@localhost [information_Schema]> SELECT _utf8mb4 'x' COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'
root@localhost [information_Schema]>
参照：10.8.3 Character Set and Collation Compatibility
https://dev.mysql.com/doc/refman/5.7/en/charset-collation-compatibility.html
■ 文字セットの互換性の確認
root@localhost [OU]> CREATE TABLE `T_Character_Compare` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `c_utf8` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
-> `c_utf8mb4` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
-> `c_latin2` char(1) CHARACTER SET latin2 COLLATE latin2_general_ci DEFAULT NULL,
-> `c_cp932` char(1) CHARACTER SET cp932 COLLATE cp932_japanese_ci DEFAULT NULL,
-> `c_eucjpms` char(1) CHARACTER SET eucjpms COLLATE eucjpms_japanese_ci DEFAULT NULL,
-> `c_utf8_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_utf8`))) VIRTUAL,
-> `c_utf8mb4_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_utf8mb4`))) VIRTUAL,
-> `c_latin2_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_latin2`))) VIRTUAL,
-> `c_cp932_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_cp932`))) VIRTUAL,
-> `c_eucjpms_string` char(8) GENERATED ALWAYS AS (hex(weight_string(`c_eucjpms`))) VIRTUAL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.10 sec)
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('A','A','A','A','A');
Query OK, 1 row affected (0.01 sec)
root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| 1 | A | A | A | A | A | 0041 | 0041 | 41 | 41 | 41 |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
1 row in set (0.00 sec)
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('a','a','a','a','a');
Query OK, 1 row affected (0.02 sec)
root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| 1 | A | A | A | A | A | 0041 | 0041 | 41 | 41 | 41 |
| 2 | a | a | a | a | a | 0041 | 0041 | 41 | 41 | 41 |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
2 rows in set (0.00 sec)
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('あ','あ','あ','あ','あ');
ERROR 1366 (HY000): Incorrect string value: '\xE3\x81\x82' for column 'c_latin2' at row 1
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('あ','あ','-','あ','あ');
Query OK, 1 row affected (0.01 sec)
root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| 1 | A | A | A | A | A | 0041 | 0041 | 41 | 41 | 41 |
| 2 | a | a | a | a | a | 0041 | 0041 | 41 | 41 | 41 |
| 3 | あ | あ | - | あ | あ | 3042 | 3042 | 2D | 82A0 | A4A2 |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
3 rows in set (0.01 sec)
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('ア','ア','-','ア','ア');
Query OK, 1 row affected (0.00 sec)
root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| 1 | A | A | A | A | A | 0041 | 0041 | 41 | 41 | 41 |
| 2 | a | a | a | a | a | 0041 | 0041 | 41 | 41 | 41 |
| 3 | あ | あ | - | あ | あ | 3042 | 3042 | 2D | 82A0 | A4A2 |
| 4 | ア | ア | - | ア | ア | 30A2 | 30A2 | 2D | 8341 | A5A2 |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
4 rows in set (0.00 sec)
root@localhost [OU]> insert into T_Character_Compare(c_utf8,c_utf8mb4,c_latin2,c_cp932,c_eucjpms) values('1','1','1','1','1');
Query OK, 1 row affected (0.00 sec)
root@localhost [OU]> select * from T_Character_Compare;
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| id | c_utf8 | c_utf8mb4 | c_latin2 | c_cp932 | c_eucjpms | c_utf8_string | c_utf8mb4_string | c_latin2_string | c_cp932_string | c_eucjpms_string |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
| 1 | A | A | A | A | A | 0041 | 0041 | 41 | 41 | 41 |
| 2 | a | a | a | a | a | 0041 | 0041 | 41 | 41 | 41 |
| 3 | あ | あ | - | あ | あ | 3042 | 3042 | 2D | 82A0 | A4A2 |
| 4 | ア | ア | - | ア | ア | 30A2 | 30A2 | 2D | 8341 | A5A2 |
| 5 | 1 | 1 | 1 | 1 | 1 | 0031 | 0031 | 31 | 31 | 31 |
+----+--------+-----------+----------+---------+-----------+---------------+------------------+-----------------+----------------+------------------+
5 rows in set (0.00 sec)
root@localhost [OU]>
その他：パラメータに関しての留意
character_set_server
default_character_set
innodb_large_prefix
innodb_file_format
innodb_file_format_max
※ MySQL8.0にした場合は特に指定しなければDefaultで基本的には問題無い設定が適用されるので、マニュアルで指定している場合やカスタマイズしている場合は確認して下さい。
![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
