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で基本的には問題無い設定が適用されるので、マニュアルで指定している場合やカスタマイズしている場合は確認して下さい。


MySQL8.0のエラーログの設定オプションが変わっているので、Fredのブログを確認しながら検証環境で確認しました。

新しいコンポーネントベースのエラーログには次の機能があり、自分の環境にあった設定を選択する事が可能になっています。

1) ログイベントは、フィルタコンポーネントによってフィルタリングして、書き込みに使用できる情報に影響を与えることができます。
2) ログイベントは、sink (writer)コンポーネントによって出力されます。 複数のシンクコンポーネントを有効にして、エラーログの出力を複数の宛先に書き込むことができます。
3) フィルタとライターの組み込みコンポーネントが組み合わされて、デフォルトのエラーログフォーマットが実装されています。
4) ロード可能なライターを使用すると、システムログにロギングできます。
5) ロード可能なライターを使用すると、JSON形式でのログ記録が可能になります。
6) システム変数は、有効にするログコンポーネントとログイベントをフィルタリングするルールを制御します。

log_error_servicesシステム変数は、エラーログに有効にするログコンポーネントを制御します。 その値は、セミコロンで区切られたコンポーネントのリストです。 スペースは重要ではありませんが、リストされた順序でコンポーネントを実行するため、コンポーネントの順序は重要です。

設定変更と動作確認


root@localhost [performance_schema]> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

root@localhost [performance_schema]>  select * from global_variables where VARIABLE_NAME like 'log_error_%';
+---------------------+----------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                         |
+---------------------+----------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal |
| log_error_verbosity | 2                                      |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> show variables like '%plugin%';
+-------------------------------+------------------------------+
| Variable_name                 | Value                        |
+-------------------------------+------------------------------+
| default_authentication_plugin | mysql_native_password        |
| plugin_dir                    | /usr/local/mysql/lib/plugin/ |
+-------------------------------+------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> system ls /usr/local/mysql/lib/plugin/component_log*
/usr/local/mysql/lib/plugin/component_log_filter_dragnet.so
/usr/local/mysql/lib/plugin/component_log_sink_json.so
/usr/local/mysql/lib/plugin/component_log_sink_syseventlog.so
/usr/local/mysql/lib/plugin/component_log_sink_test.so
root@localhost [performance_schema]> 

JSONフォーマットのログを利用したいので、JSON用のコンポーネントを追加しています。
メモ:log_errorがstderrの場合、JSONライターはコンソールにログを書き込みます。

root@localhost [performance_schema]> INSTALL COMPONENT 'file://component_log_sink_json';
Query OK, 0 rows affected (0.32 sec)

root@localhost [performance_schema]> SET PERSIST log_error_services = 'log_filter_internal; log_sink_internal; log_sink_json';
Query OK, 0 rows affected (0.00 sec)

root@localhost [performance_schema]> select * from global_variables where VARIABLE_NAME like 'log_error_%';
+---------------------+-------------------------------------------------------+
| VARIABLE_NAME       | VARIABLE_VALUE                                        |
+---------------------+-------------------------------------------------------+
| log_error_services  | log_filter_internal; log_sink_internal; log_sink_json |
| log_error_verbosity | 2                                                     |
+---------------------+-------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [performance_schema]> select * from global_variables where VARIABLE_NAME like 'log_error';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| log_error     | ./GA02.err     |
+---------------+----------------+
1 row in set (0.00 sec)

root@localhost [performance_schema]> restart;
Query OK, 0 rows affected (0.01 sec)


MySQLを再起動して、JSONフォーマットのエラーログが追加されているか確認。
log_sink_internalとlog_sink_jsonの2つが順に列挙されている為、エラーログは通常のエラーとJSONフォーマットのエラーログの2つが出力されています。

エラーログファイルの確認


root@localhost [performance_schema]> system ls /usr/local/mysql/data/GA02*
/usr/local/mysql/data/GA02.err  /usr/local/mysql/data/GA02.err.00.json  /usr/local/mysql/data/GA02.pid
root@localhost [performance_schema]> 

通常のエラーログ

[root@GA02 admin]# head /usr/local/mysql/data/GA02.err
2018-02-23T03:21:22.117236Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 3664 ...
2018-02-23T03:21:23.232801Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T03:21:23.357209Z 0 [System] [MY-010931] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).
2018-02-23T03:25:49.322260Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.
2018-02-23T03:25:55.462916Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 3841 ...
2018-02-23T03:25:56.179429Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.
2018-02-23T03:25:56.195172Z 0 [System] [MY-010931] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).
2018-02-23T04:11:46.558480Z 0 [System] [MY-010910] /usr/local/mysql/bin/mysqld: Shutdown complete.
2018-02-23T04:11:47.891177Z 0 [System] [MY-010116] /usr/local/mysql/bin/mysqld (mysqld 8.0.4-rc-log) starting as process 4606 ...
2018-02-23T04:11:48.552514Z 0 [Warning] [MY-010068] CA certificate ca.pem is self signed.

JSONフォーマットのエラーログ

[root@GA02 admin]# head /usr/local/mysql/data/GA02.err.00.json 
{ "prio" : 0, "err_code" : 11086, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "sql_restart_server.cc", "function" : "execute", "msg" : "Received RESTART from user root.  Restarting mysqld (Version: 8.0.4-rc-log).", "time" : "2018-03-14T01:08:33.576059Z", "thread" : 7, "err_symbol" : "ER_RESTART_RECEIVED_INFO", "label" : "System" }
{ "prio" : 2, "err_code" : 10909, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "mysqld.cc", "function" : "operator()", "msg" : "/usr/local/mysql/bin/mysqld: Forcing close of thread 7  user: 'root'.", "time" : "2018-03-14T01:08:35.882770Z", "err_symbol" : "ER_FORCE_CLOSE_THREAD", "label" : "Warning" }
{ "prio" : 0, "err_code" : 10910, "subsystem" : "", "SQL_state" : "HY000", "source_file" : "mysqld.cc", "function" : "clean_up", "msg" : "/usr/local/mysql/bin/mysqld: Shutdown complete.", "time" : "2018-03-14T01:08:36.834869Z", "err_symbol" : "ER_SERVER_SHUTDOWN_COMPLETE", "label" : "System" }
{ "log_type" : 1, "prio" : 0, "err_code" : 10931, "msg" : "/usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL).", "time" : "2018-03-14T01:08:39.000803Z", "err_symbol" : "ER_SERVER_STARTUP_MSG", "SQL_state" : "HY000", "label" : "System" }
[root@GA02 admin]# 

以下、マニュアル抜粋
log_filter_internal:優先度に基づくエラーログのフィルタリング
エラーログの冗長性制御は、エラーイベントの優先度に基づいたログフィルタリングの簡単な形式です。 これは、log_filter_internalログフィルタコンポーネントによって実装されます。
log_filter_internalがエラー・ログを対象とするエラー、警告、およびメモイベントを許可または抑制する方法に影響を与えるには、log_error_verbosityシステム変数を設定します。
log_filter_internalはデフォルトで組み込まれ、有効になっていますが、無効にするとlog_error_verbosityの変更は無効です。
許可されるlog_error_verbosityの値は、1(エラーのみ)、2(エラーと警告)、3(エラー、警告、およびメモ)

log_filter_dragnet:ルールベースのエラーログのフィルタリング
log_filter_dragnetログフィルタコンポーネントは、ユーザ定義のルールに基づいてログフィルタリングを有効にします。 適用可能なルールを定義するにはdragnet.log_error_filter_rulesシステム変数を設定します。
詳細:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_dragnet.log_error_filter_rules

log_filter_dragnetフィルタを有効にするには、まずフィルタコンポーネントをロードしてから、log_error_services値を変更します。

log_filter_internal:優先度に基づくエラーログのフィルタリング
エラーログの冗長性制御は、エラーイベントの優先度に基づいたログフィルタリングの簡単な形式です。 これは、log_filter_internalログフィルタコンポーネントによって実装されます。 log_filter_internalがエラー・ログを対象とするエラー、警告、およびメモイベントを許可または抑制する方法に影響を与えるには、log_error_verbosityシステム変数を設定します。 log_filter_internalはデフォルトで組み込まれ、有効になっていますが、無効にするとlog_error_verbosityの変更は無効です。

log_sink_internal、log_sink_test:これらのライターは、file_nameに書き込みます
log_sink_json:log_error_services値で指定されたこのライターの連続インスタンスは、file_nameという名前のファイルと、番号付きの.NN.json接尾辞:file_name.00.json、file_name.01.jsonなどに書き込みます。
詳細:
https://dev.mysql.com/doc/refman/8.0/en/error-log-json.html

log_sink_syseventlog:このライターは、log_error値に関係なく、システムログに書き込みます。
log_sink_internal、log_sink_json、log_sink_test:これらのライターはコンソールに書き込みます。

参照:5.4.2 The Error Log 
https://dev.mysql.com/doc/refman/8.0/en/error-log.html


SYSスキーマの説明をする機会があったので、改めてMySQL5.7.21でSYSスキーマに関しての概要をまとめたのでご紹介。
Performance_Schema, Information_Schemaを直接確認しないと取得出来無い情報もまだあるけれど、SYSスキーマを利用すれば簡単にMySQLの状態を確認出来、複雑なクエリーを使わないでもロックの状態、メモリーの状態、未使用のインデックス、起動してからの累積値だけれども遅いクエリー等が確認可能です。まだまだ使われていないユーザーも多いけど、便利なので是非活用下さい。

MySQL5.7.21の時点では以下のオブジェクトが存在します。


root@localhost [sys]> select * from schema_object_overview where db = 'sys';
+-----+---------------+-------+
| db  | object_type   | count |
+-----+---------------+-------+
| sys | TRIGGER       |     2 |
| sys | FUNCTION      |    22 |
| sys | PROCEDURE     |    26 |
| sys | VIEW          |   100 |
| sys | BASE TABLE    |     1 |
| sys | INDEX (BTREE) |     1 |
+-----+---------------+-------+
6 rows in set (0.32 sec)

以下のスライドにまとめたので、詳細をご確認下さい。


MySQLでGeoHashを試してみた。以前、Webサイトを構築したときは、Google Map APIを利用して、住所から緯度経度を逆引きしてvarcharで作った列に情報を格納していましたが、あの時にgeohashを知っていたら色々とサービスで使えたかもしれない。。と思い、少し確認してみた。

MEMO:
Geohashは、任意の精度の緯度と経度座標をテキスト文字列にエンコード
Geohash値は、”0123456789bcdefghjkmnpqrstuvwxyz”から選択された文字のみを含む文字列

Geohash

桁数の誤差による差異

変換テーブル

Wiki参照: https://en.wikipedia.org/wiki/Geohash

変換例: xn76g = 11101 10100 00111 00110 01111
左から数え始め、経度は偶数ビット(1110001101011)、緯度は奇数ビット(101100101011)として表現される。二進数は1ビットずつ、左のビットから順に評価されるて、緯度については、-90から+90が2つの区間に分割され、経度は-180から+180の区間に分割される。

確認で利用したMySQLのバージョン

root@localhost [GIS]> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

MySQL8.0のgeohash関連関数
ST_から始まるものが、最新のGEO関数です。

ST_GeoHash(経度,緯度,最大長)、ST_GeoHash(point,max_length)
接続文字セットと照合順序でgeohash文字列を返します。

ST_LatFromGeoHash(geohash_str)
geohash文字列値から[-90、90]の範囲の倍精度数として緯度を返します。

ST_LongFromGeoHash(geohash_str)
geohash文字列値から[-180,180]の範囲の倍精度数値として経度を返します。

ST_PointFromGeoHash(geohash_str、srid)
geohash文字列値を指定して、解読されたgeohash値を含むPOINT値を返します。

root@localhost [GIS]> SELECT ST_GeoHash(180,0,10), ST_GeoHash(-180,-90,15);
+----------------------+-------------------------+
| ST_GeoHash(180,0,10) | ST_GeoHash(-180,-90,15) |
+----------------------+-------------------------+
| xbpbpbpbpb           | 000000000000000         |
+----------------------+-------------------------+
1 row in set (0.01 sec)

root@localhost [GIS]>  SELECT ST_LatFromGeoHash(ST_GeoHash(45,-20,10));
+------------------------------------------+
| ST_LatFromGeoHash(ST_GeoHash(45,-20,10)) |
+------------------------------------------+
|                                      -20 |
+------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> SELECT ST_LongFromGeoHash(ST_GeoHash(45,-20,10));
+-------------------------------------------+
| ST_LongFromGeoHash(ST_GeoHash(45,-20,10)) |
+-------------------------------------------+
|                                        45 |
+-------------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> SET @gh = ST_GeoHash(45,-20,10);
Query OK, 0 rows affected (0.00 sec)

root@localhost [GIS]> SELECT ST_AsText(ST_PointFromGeoHash(@gh,0));
+---------------------------------------+
| ST_AsText(ST_PointFromGeoHash(@gh,0)) |
+---------------------------------------+
| POINT(45 -20)                         |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost [GIS]> 

実際にGoogle Mapを利用して確認
オラクル青山オフィスのgeohash(5桁:xn76g)を利用してレストランを検索。
データをImport後に、緯度-経度を読み込んで確認してみる。

検証用のデータはこちらからダウンロード可能です。
Download OpenStreetMap data for this region:Japan
http://download.geofabrik.de/asia/japan.html
Download OpenStreetMap data for this region:Asia
http://download.geofabrik.de/asia.html

インポート方法はこちらが参考になります。
MySQL 5.7 and GIS, an Example
https://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

検証データと結果の確認

root@localhost [GIS]> select nodetags.v as 'name',st_latfromgeohash(st_geohash((nodes.geom),8)) as 'lat', st_longfromgeohash(st_geohash((nodes.geom),8)) as 'lng','restaurant' as 'type'  FROM nodes,nodetags WHERE nodes.id = nodetags.id and match(tags)  against ('+restaurant' IN BOOLEAN MODE) and nodetags.k='name' and nodes.GeoHash5 = 'xn76g' limit 10;
+--------------------------------------------+---------+----------+------------+
| name                                       | lat     | lng      | type       |
+--------------------------------------------+---------+----------+------------+
| 雅山 (Gazan)                               | 35.6603 | 139.7397 | restaurant |
| とんかつ まい泉                            |  35.668 | 139.7116 | restaurant |
| 粥 「喜喜」                                | 35.6727 | 139.7126 | restaurant |
| ヘンドリックス・カリー・バー               |  35.675 | 139.7126 | restaurant |
| えさき (Esaki)                             | 35.6717 | 139.7133 | restaurant |
| Cardenas/charcoal grill                    | 35.6478 | 139.7075 | restaurant |
| ROYAL PALACE                               | 35.6475 |  139.707 | restaurant |
| 長崎ちゃんぽんリンガーハット               | 35.6478 |  139.746 | restaurant |
| 増田屋 (Masudaya)                          | 35.6705 | 139.7136 | restaurant |
| Las Chicas                                 | 35.6634 |  139.708 | restaurant |
+--------------------------------------------+---------+----------+------------+
10 rows in set (0.04 sec)

PHPにSQLをコピーしてGoogle Map APIに渡して、地図を確認してみると、問題なく、同じGeohashにあるレストランが表示されているようです。
インデックスを併用すれば、結構レスポンスの良い空間データベースになりそうです。

上記処理を実行した時の、MySQLで実行プランは以下のような感じです

以上で確認は終了ですが、ついでにSRIDを指定してテーブルを作成する事が出来るようになっていたので確認してみた。
緯度-経度の順番でINSERTする場合は、列にSRIDを指定して作成した方が良いようです。
反対に経度-緯度の場合はDefaultの0のままで良い。データINSERT時に緯度と経度の順番のバリデーションが無いのであった方がよさそうです。

root@localhost [GIS]> CREATE TABLE geom (
    ->     p POINT NOT NULL SRID 0,
    ->     g GEOMETRY NOT NULL SRID 4326,
    ->     GeoHash5 varchar(5) GENERATED ALWAYS AS (st_geohash(`g`,5)) VIRTUAL,
    ->     GeoHash8 varchar(8) GENERATED ALWAYS AS (st_geohash(`g`,8)) VIRTUAL
    -> );

root@localhost [GIS]> INSERT INTO geom(p,g) VALUES(ST_GeomFromText('POINT(139.718695 35.671482)'),ST_GeomFromText('POINT(35.671482 139.718695)',4326));
Query OK, 1 row affected (0.00 sec)

root@localhost [GIS]> select * from geom;
+---------------------------+---------------------------+----------+----------+
| p                         | g                         | GeoHash5 | GeoHash8 |
+-------- @$----------------+ @$------------------------+----------+----------+
|           A@        |         A@         | xn76g    | xn76gmu1 |
+---------------------------+---------------------------+----------+----------+
1 row in set (0.00 sec)

root@localhost [GIS]> select st_geohash(p,8),st_geohash(g,8),GeoHash5,GeoHash8 from geom;
+-----------------+-----------------+----------+----------+
| st_geohash(p,8) | st_geohash(g,8) | GeoHash5 | GeoHash8 |
+-----------------+-----------------+----------+----------+
| xn76gmu1        | xn76gmu1        | xn76g    | xn76gmu1 |
+-----------------+-----------------+----------+----------+
1 row in set (0.00 sec)

root@localhost [GIS]> select 'xn76g' as 'geohash',st_latfromgeohash('xn76g') as '緯度',st_longfromgeohash('xn76g') as '経度';
+---------+--------+--------+
| geohash | 緯度   | 経度   |
+---------+--------+--------+
| xn76g   |  35.66 | 139.72 |
+---------+--------+--------+
1 row in set (0.01 sec)


参照:
12.15.10 Spatial Geohash Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geohash-functions.html

geohash.org
http://geohash.org/