MySQL5.7からMySQL8.0へのアップグレード対応時のメモ

他のインスタンスも既に、アップグレードしましたが、UTF8MB4以外の文字コードを利用していたり、パーティションエンジンを利用していたので、
少々手間取ってしまいました。こちらの、アップグレード例はもともと、MySQL5.7で初期インストールして利用していたデータベースでUTF8MB4を利用して、
パーティションもInnoDB Native Partitionを利用していたので直ぐにアップグレードする事が出来ました。ただし、いくつか設定を変更しました。

アップグレード手順
1: mysqlsh (Ver 8.0.11) にてアップグレード事前チェックを行う
2: MySQL8.0のバリナリーダウンロード (Generic Tarを利用しました)
3: 既存のサービスを停止して、シンボリックリンクの張り直し
4: 必要なファイルやフォルダーをコピー(直ぐに、ロールバック出来るようにオリジナルフォルダーは残しています)
5: my.cnfに事前に必要な設定を入れて、MySQL8.0に無いパラメータを削除しておく
6: MySQLサービスを開始して、mysql_upgradeを実行
7: 必要に応じて、SchemaやTableのCOLLATEを変更
8: アプリケーションの接続やStrictモードを必要に応じて変更
9: 動作確認して終了

STEP1: mysqlshによるアップグレード対象インスタンスの互換性の確認
色々なアドバイスが出てくるので、必要に応じて適宜対応してください。(例:文字コード変換、パーティションストレージエンジンをInnoDBに変換等)


[root@AP01 bin]# ./mysqlsh root:password@localhost:3306 -e "util.checkForServerUpgrade();"
mysqlsh: [Warning] Using a password on the command line interface can be insecure.
The MySQL server at localhost:3306 will now be checked for compatibility issues for upgrade to MySQL 8.0...
MySQL version: 5.7.21-log - MySQL Community Server (GPL)

1) Usage of db objects with names conflicting with reserved keywords in 8.0
  No issues found

2) Usage of utf8mb3 charset
  No issues found

3) Usage of use ZEROFILL/display length type attributes
  Notice: The following table columns specify a ZEROFILL/display length attributes. Please be aware that they will be ignored in MySQL 8.0

  APP.T_Laravel.age - int(4)

4) Issues reported by 'check table x for upgrade' command
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Usage of old temporal type
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Usage of removed functions
  No issues found

No fatal errors were found that would prevent a MySQL 8 upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading.
[root@AP01 bin]# 

■ 8.0のバイナリーをダウンロードして展開

[root@AP01 local]# tar zxvf mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz 
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_config_editor
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_secure_installation
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_ssl_rsa_setup
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_tzinfo_to_sql
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysql_upgrade
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqladmin
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlbinlog
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlcheck
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqldump
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mysqlimport
mysql-8.0.11-linux-glibc2.12-x86_64/bin/mys

■既存のサービスを停止して、シンボリックリンクの張り直し

[root@AP01 local]# ls -l 
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  2月 19 17:04 mysql -> mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS! 
[root@AP01 local]# rm mysql
rm: シンボリックリンク `mysql' を削除しますか? y
[root@AP01 local]# ln -s mysql-8.0.11-linux-glibc2.12-x86_64 mysql
[root@AP01 local]# ls -l
合計 588896
drwxr-xr-x.  2 root root          6  2月 19 12:04 bin
drwxr-xr-x.  2 root root          6  6月 10  2014 etc
drwxr-xr-x.  2 root root          6  6月 10  2014 games
drwxr-xr-x.  2 root root          6  6月 10  2014 include
drwxr-xr-x.  2 root root          6  6月 10  2014 lib
drwxr-xr-x.  2 root root          6  6月 10  2014 lib64
drwxr-xr-x.  2 root root          6  6月 10  2014 libexec
lrwxrwxrwx.  1 root root         35  4月 23 14:05 mysql -> mysql-8.0.11-linux-glibc2.12-x86_64
drwxr-xr-x. 12 root root       4096  2月 19 17:14 mysql-5.7.21-linux-glibc2.12-x86_64
drwxr-xr-x.  9 root root       4096  4月 23 14:05 mysql-8.0.11-linux-glibc2.12-x86_64
-rw-r--r--.  1 root root  603019898  4月  8 15:30 mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
drwxr-xr-x.  5 7161 31415        38  4月  9 11:49 mysql-shell-8.0.11-linux-glibc2.12-x86-64bit
lrwxrwxrwx.  1 root root         45  4月 23 14:04 mysqlsh -> mysql-shell-8.0.11-linux-glibc2.12-x86-64bit/
drwxr-xr-x.  2 root root          6  6月 10  2014 sbin
drwxr-xr-x.  5 root root         46 11月 21  2014 share
drwxr-xr-x.  2 root root          6 11月 15 20:51 src
[root@AP01 local]# 

■必要なファイルやフォルダーをコピー

[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# ls -l
合計 44
-rw-r--r--.  1  7161 31415 17987 12月 28 12:46 COPYING
-rw-r--r--.  1  7161 31415  2478 12月 28 12:46 README
drwxr-xr-x.  2 root  root   4096  2月 19 17:03 bin
drwxr-x---.  6 mysql mysql  4096  4月 23 14:06 data
drwxr-xr-x.  2 root  root     52  2月 19 17:03 docs
drwxr-xr-x.  3 root  root   4096  2月 19 17:03 include
drwxr-xr-x.  5 root  root   4096  2月 19 17:03 lib
drwxr-xr-x.  2 mysql mysql    60  2月 19 17:14 logs
drwxr-xr-x.  4 root  root     28  2月 19 17:03 man
drwxr-x---.  2 mysql mysql     6  2月 19 17:05 mysql-files
drwxr-xr-x. 28 root  root   4096  2月 19 17:03 share
drwxr-xr-x.  2 root  root     86  2月 19 17:03 support-files
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp data /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# cp -rp mysql-files /usr/local/mysql
[root@AP01 mysql-5.7.21-linux-glibc2.12-x86_64]# 

■ my.cnfに以下の設定を入れておく。

default_authentication_plugin=mysql_native_password

また、mysql8.0に無いパラメータは、削除しておかないとエラーになるので削除しておきましょう。
例)2018-04-23T05:39:57.859413Z 0 [ERROR] [MY-011071] [Server] unknown variable ‘query_cache_type=0’

パラメータのチェックに関しては、こちらのページが便利です。
https://tmtm.github.io/mysql-params/?vers=5.7.22,8.0.11&diff=true

■ MySQLの起動とUpgrade


[root@AP01 data]# /etc/init.d/mysql.server start
Starting MySQL... SUCCESS! 
[root@AP01 data]#

[root@AP01 mysql]# mysql_upgrade -u root -p
Enter password: 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
APP.T_ChangeLog                                    OK
APP.T_Laravel                                      OK
APP.T_business                                     OK
APP.T_databases                                    OK
APP.T_others                                       OK
APP.T_scripts                                      OK
APP.cars                                           OK
APP.migrations                                     OK
APP.password_resets                                OK
APP.users                                          OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.
[root@AP01 mysql]# 

■ ファイルが自動的にアップグレードされ、メタデータ関連ファイルが無くなっている事が確認できる。

[root@AP01 mysql]# ls -l
合計 10732
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 columns_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 columns_priv.MYI
-rw-r-----. 1 mysql mysql    7763  4月 23 14:36 columns_priv_83.sdi
-rw-r-----. 1 mysql mysql    1464  2月 19 17:28 db.MYD
-rw-r-----. 1 mysql mysql    5120  2月 19 17:28 db.MYI
-rw-r-----. 1 mysql mysql   19285  4月 23 14:36 db_84.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 engine_cost.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 func.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:27 func.MYI
-rw-r-----. 1 mysql mysql    4819  4月 23 14:36 func_87.sdi
-rw-r-----. 1 mysql mysql      35  4月 23 14:31 general_log.CSM
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 general_log.CSV
-rw-r-----. 1 mysql mysql    5520  4月 23 14:36 general_log_88.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 gtid_executed.ibd
-rw-r-----. 1 mysql mysql  131072  4月 23 14:36 help_category.ibd
-rw-r-----. 1 mysql mysql  262144  4月 23 14:36 help_keyword.ibd
-rw-r-----. 1 mysql mysql  163840  4月 23 14:36 help_relation.ibd
-rw-r-----. 1 mysql mysql 8388608  4月 23 14:36 help_topic.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:05 ndb_binlog_index.MYD
-rw-r-----. 1 mysql mysql    1024  2月 19 17:05 ndb_binlog_index.MYI
-rw-r-----. 1 mysql mysql   10729  4月 23 14:36 ndb_binlog_index_96.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 plugin.ibd
-rw-r-----. 1 mysql mysql       0  2月 19 17:27 procs_priv.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:27 procs_priv.MYI
-rw-r-----. 1 mysql mysql    8960  4月 23 14:36 procs_priv_98.sdi
-rw-r-----. 1 mysql mysql     837  2月 19 17:05 proxies_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:05 proxies_priv.MYI
-rw-r-----. 1 mysql mysql    7813  4月 23 14:36 proxies_priv_99.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 server_cost.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 servers.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_master_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_relay_log_info.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 slave_worker_info.ibd
-rw-r-----. 1 mysql mysql      35  4月 23 14:32 slow_log.CSM
-rw-r-----. 1 mysql mysql  104677  4月 23 14:31 slow_log.CSV
-rw-r-----. 1 mysql mysql   11741  4月 23 14:36 slow_log_105.sdi
-rw-r-----. 1 mysql mysql    1894  2月 19 17:27 tables_priv.MYD
-rw-r-----. 1 mysql mysql    9216  2月 19 17:50 tables_priv.MYI
-rw-r-----. 1 mysql mysql    9379  4月 23 14:36 tables_priv_106.sdi
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_leap_second.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_name.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition.ibd
-rw-r-----. 1 mysql mysql  114688  4月 23 14:36 time_zone_transition_type.ibd
-rw-r-----. 1 mysql mysql     504  2月 19 17:27 user.MYD
-rw-r-----. 1 mysql mysql    4096  2月 19 17:50 user.MYI
-rw-r-----. 1 mysql mysql   36754  4月 23 14:36 user_112.sdi
[root@AP01 mysql]# 


[root@AP01 mysql]# ls -l
合計 444
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 general_log.CSM
-rw-r-----. 1 mysql mysql      0  4月 23 14:43 general_log.CSV
-rw-r-----. 1 mysql mysql   5520  4月 23 14:43 general_log_365.sdi
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_index_stats_backup57.ibd
-rw-r-----. 1 mysql mysql 114688  4月 23 14:36 innodb_table_stats_backup57.ibd
-rw-r-----. 1 mysql mysql     35  4月 23 14:43 slow_log.CSM
-rw-r-----. 1 mysql mysql 109876  4月 23 14:43 slow_log.CSV
-rw-r-----. 1 mysql mysql  11741  4月 23 14:43 slow_log_367.sdi
[root@AP01 mysql]# 

必要に応じて、既存データベースやテーブルのCOLLATION(照合順序)を変更しておく

root@localhost [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.11    |
+-----------+
1 row in set (0.00 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */
1 row in set (0.00 sec)

root@localhost [APP]> alter schema APP DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 1 row affected (0.08 sec)

root@localhost [APP]> show create database APP\G
*************************** 1. row ***************************
       Database: APP
Create Database: CREATE DATABASE `APP` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */
1 row in set (0.00 sec)

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `comment` varchar(2048) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)

root@localhost [APP]> alter table `T_ChangeLog` convert to character set utf8mb4;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

root@localhost [APP]> show create table T_ChangeLog\G
*************************** 1. row ***************************
       Table: T_ChangeLog
Create Table: CREATE TABLE `T_ChangeLog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `comment` varchar(2048) DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

root@localhost [APP]> 

■アプリケーションの接続や必要に応じてStrictモードを変更

            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_0900_ai_ci',
            'prefix' => '',
            /* 'strict' => true, */
            'strict' => false,

NO_AUTO_CREATE_USER等の、SQLモードが削除されているのでフレームワークによっては、StrictモードがTrueのままだと、
アプリケーションがエラーになる場合があります。

参照:アカウント管理に関連する次の機能は削除されました。
GRANTを使用してユーザーを作成する代わりに、CREATE USERを使用するようになっています。 “NO_AUTO_CREATE_USER” SQLモードはGRANT文にとって重要ではない為、削除されました。
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

その他、参考:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html


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


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/


デモの準備をしていて、MySQL5.7で動いていたページが以下のようにエラーになったので、今後の為にメモ。既にマニュアルやブログで確認してはいて、情報としては認識していたのですがMySQL8.0.4以降の変更点なので忘れてました。

■ 認証プラグインの変更について
MySQL 8.0では、mysql_native_passwordではなくcaching_sha2_passwordがデフォルトの認証プラグインです。
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

■ 変更理由はセキュリティ強化とパフォーマンス
caching_sha2_passwordおよびsha256_password認証プラグインは、mysql_native_passwordプラグインよりも安全なパスワード暗号化を提供し、caching_sha2_passwordはsha256_passwordよりも優れたパフォーマンスを提供します。
caching_sha2_passwordのこれらの優れたセキュリティとパフォーマンスの特性のため、これはMySQL 8.0.4以降の認証プラグインであり、mysql_native_passwordではなくデフォルトの認証プラグインでもあります。
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password

現状では、コネクターが対応していない為、アカウント認証をMySQL8.0.4以前の認証方法に戻します。


[root@GA02 mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.4-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.4-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> 


root@localhost [mysql]> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | caching_sha2_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> alter user 'admin'@'%' identified WITH mysql_native_password by 'password';
Query OK, 0 rows affected (0.00 sec)

root@localhost [mysql]> SELECT user, host, plugin FROM user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| admin            | %         | mysql_native_password |
| mysql.infoschema | localhost | mysql_native_password |
| mysql.session    | localhost | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

root@localhost [mysql]> 

mysql_native_password認証に戻し、ページが問題無く表示される事を確認

caching_sha2_password互換のクライアントおよびコネクタ
caching_sha2_passwordについて知るように更新されたクライアントまたはコネクターが使用可能な場合は、それを使用すると、MySQL 8.0サーバに接続する際に互換性を保証する最良の方法です、それらはデフォルトの認証プラグインとしてcaching_sha2_passwordを使用して構成されています。これらのクライアントとコネクタは、caching_sha2_passwordをサポートするようにアップグレードされました:

MySQL 8.0.4以降のlibmysqlclientクライアントライブラリ。
mysqlやmysqladminなどの標準的なMySQLクライアントはlibmysqlclientベースである為、互換性があります。

MySQL Connector/J 8.0.9 or higher.
MySQL Connector/Net 8.0.10 or higher (through the classic MySQL protocol).
MySQL Connector/Node.js 8.0.9 or higher.

注意:2018年2月現在、caching_sha2_password互換クライアントはGAになってない為、
   MySQL8.0がGAになるまでしばらく進捗を確認する必要があります。

MySQL8.0.4以前に作成されたアカウントをcaching_sha2_passwordに切り替える必要がある場合
ユーザーはALTER USERステートメントを使用して変更できます。


ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';

重要
MySQL 8.0.4より前のバージョンのクライアントに対応する必要があり、MySQL 8.0.4以降にアップグレードした後で互換性の問題が発生した場合、これらの問題に対処して8.0以前の互換性を復元する最も簡単な方法は、サーバを再設定しプラグイン(mysql_native_password)を利用します。
必要に応じて、オプションファイルに次の行を設定してください。


[mysqld]
default_authentication_plugin=mysql_native_password

MySQL8.0をアプリケーションで利用される場合はこちらを確認ください。
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password


MySQL8.0 RCのDockerイメージがリリースされていたので、今後の検証やデモ用に設定しました。
手軽に検証出来るので、軽く検証するにはお勧めです。

Docker Image: https://github.com/mysql/mysql-docker


[root@DockerHost oracle]# docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
323fb8f65502: Pull complete 
b2a15600aac3: Pull complete 
a1116f4203e9: Pull complete 
8be6f234356c: Pull complete 
a09590e34bdc: Pull complete 
554cdb588e9e: Pull complete 
851fce189663: Pull complete 
ca60670c6cb3: Pull complete 
98a8195f4fc5: Pull complete 
ec8c0ade6c51: Pull complete 
73919c529833: Pull complete 
285b77036a3a: Pull complete 
270395aafb1e: Pull complete 
Digest: sha256:183772d6f5a1decd1eb0252e542d338a5ef8c02fe4cc2cc909b58788f8728c58
Status: Downloaded newer image for mysql/mysql-server:8.0
[root@DockerHost oracle]#

[root@DockerHost oracle]# docker run --name mysql83 -v /docker/docker83:/var/lib/mysql -v /docker/option83:/etc/mysql/conf.d -v /docker/init_script:/docker-entrypoint-initdb.d -e MYSQL_ROOT_PASSWORD=mysql -d mysql/mysql-server:8.0
ecd2156cdd36d735b5d01f6d7b89ea24cc7d499cbc59e1014bc42ba92c764365
[root@DockerHost oracle]# 

[root@DockerHost oracle]# docker exec -it mysql83 mysql --default-character-set=utf8mb4 -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.3-rc-log |
+--------------+
1 row in set (0.00 sec)

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)


メモ:CTEの確認

mysql> WITH RECURSIVE  
    -> emp_ext (id, name, path) AS ( 
    ->    SELECT id, name, CAST(id AS CHAR(200)) 
    ->    FROM employees WHERE manager_id IS NULL 
    ->  UNION ALL 
    ->    SELECT s.id, s.name,CONCAT(m.path, ",", s.id) 
    ->    FROM emp_ext m JOIN  employees s ON m.id=s.manager_id )
    -> SELECT * FROM emp_ext ORDER BY path; 
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

mysql> 

メモ:Windows Functionの確認


mysql> select employee,date,sale,SUM(sale)
    -> OVER (PARTITION BY employee) AS sum FROM sales;
+----------+------------+------+------+
| employee | date       | sale | sum  |
+----------+------------+------+------+
| A        | 2017-03-01 |  200 |  900 |
| A        | 2017-04-01 |  300 |  900 |
| A        | 2017-05-01 |  400 |  900 |
| B        | 2017-03-01 |  400 | 1200 |
| B        | 2017-04-01 |  300 | 1200 |
| B        | 2017-05-01 |  500 | 1200 |
| C        | 2017-03-01 |  100 | 1000 |
| C        | 2017-04-01 |  600 | 1000 |
| C        | 2017-05-01 |  300 | 1000 |
+----------+------------+------+------+
9 rows in set (0.00 sec)

mysql> 

MySQL8.0には管理者と開発者にとって使い易い機能や関数も増えているので、
色々な場面で活用する事が出来るかと思います。

詳細情報:
http://mysqlserverteam.com/

バグ報告:
https://bugs.mysql.com/

ブログにはRC1と書いてあったけど、RC2とかもリリース予定なのかな?
Please enjoy it.


MySQL8.0がリリース候補版になりました。

Changes in MySQL 8.0.3 (2017-09-21, Release Candidate)
リリースノート:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html

【主な変更点】
Histograms, Force Index, Hints, Invisible Indexes
Common Table Expressions, Windows Functions, Character Sets,
JSON, GIS, Resource Groups, Performance Schema, Security,
Protocol, Service Infrastructure, X Protocol / X Plugin,
Performance, Tablespaces, DDL, Replication, Group Replication,
Data Dictionary, MTR Tests, Library Upgrade, Changes to Defaults

詳細:http://mysqlserverteam.com/the-mysql-8-0-3-release-candidate-is-available/

全てをカバーしてませんが、先日のセミナー資料で概要を紹介しています。


MySQL5.7で約20程のJSON関数が追加されていましたが、MySQL8.0においても更に追加でJSON関数が加えられているので、基本的な動作のみを確認しています。JSON_ARRAYAGG(), JSON_OBJECTAGG(),JSON_PRETTY()

MySQL5.7 JSON関数マニュアル
https://dev.mysql.com/doc/refman/5.7/en/json-functions.html

MySQL5.7のJSONの概要に関しては、こちらにて資料がダウンロード可能です。https://www.mysql.com/jp/why-mysql/presentations/mysql-json-201701-ja/

まだ、MySQL8.0はDMRなので、これからまだ仕様が変わる部分がある事はご了承ください。

確認バージョン


mysql> select now(),@@version;     
+---------------------+-----------+
| now()               | @@version |
+---------------------+-----------+
| 2017-05-31 19:50:46 | 8.0.1-dmr |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> 

WL#7987 : JSON aggregation functions
https://dev.mysql.com/worklog/task/?id=7987

Add aggregation functions to generate JSON arrays and objects. This makes it possible to combine JSON documents in multiple rows into a JSON array or a JSON object.

mysql> select body from T_JSON_DOC where id in (1,2);              
+---------------------------------------------------------------------------------+
| body                                                                            |
+---------------------------------------------------------------------------------+
| {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
| {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+---------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> select JSON_ARRAYAGG(body) from T_JSON_DOC where id in(1,2);
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_ARRAYAGG(body)                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> select id,body from T_JSON_DOC where id in(1,2);            
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
+----+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC where id in(1,2);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECTAGG(id,body)                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select id,body from T_JSON_DOC;                                                
+----+---------------------------------------------------------------------------------+
| id | body                                                                            |
+----+---------------------------------------------------------------------------------+
|  1 | {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]} |
|  2 | {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}         |
|  3 | {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}              |
|  4 | {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}             |
|  5 | {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}   |
+----+---------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[0]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price
": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "pri
ce": 25000, "Conditions": ["NEW", 2015, "January"]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
2 rows in set (0.01 sec)

mysql> mysql> select JSON_OBJECTAGG(id,body) from T_JSON_DOC group by body->"$.Conditions[1]"\G
*************************** 1. row ***************************
JSON_OBJECTAGG(id,body): {"2": {"id": 2, "name": "", "price": 30000, "Conditions": ["USED", 2013, ""]}}
*************************** 2. row ***************************
JSON_OBJECTAGG(id,body): {"1": {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}, "3": {"id": 3, "name": "", "price": 18198, "Conditions": ["NEW", 2015]}, "4": {"id": 4, "name": "", "price": 500000, "Conditions": ["NEW", 2015]}, "5": {"id": 5, "name": "", "price": 25000, "Conditions": ["NEW", 2015, "January"]}}
2 rows in set (0.00 sec)

mysql>
 

WL#9191: JSON_PRETTY function
https://dev.mysql.com/worklog/task/?id=9191

User Feedback from presenting JSON features has suggested that we are missing a function to format JSON in a human-readable way (with new lines and indentation).

This functionality is available in both PHP and PostgreSQL under the name “pretty”:


mysql> select body from T_JSON_DOC where id = 1\G             
*************************** 1. row ***************************
body: {"id": 1, "name": "", "price": 10000, "Conditions": ["NEW", 2015, "Excellent"]}
1 row in set (0.00 sec)

mysql> select json_pretty(body) from T_JSON_DOC where id = 1\G
*************************** 1. row ***************************
json_pretty(body): {
  "id": 1,
  "name": "",
  "price": 10000,
  "Conditions": [
    "NEW",
    2015,
    "Excellent"
  ]
}
1 row in set (0.00 sec)

mysql> 

その他のJSON関数(運用向け)

JSON_STORAGE_FREE(json_val)
For a JSON column value, this function shows how much storage space was freed in its binary representation after it was updated in place using JSON_SET() or JSON_REPLACE(). The argument can also be a valid JSON document or a string which can be parsed as one—either as a literal value or as the value of a user variable—in which case the function returns 0.

※Updating the column without using JSON_SET() (or JSON_REPLACE()) means that the optimizer cannot perform the update in place; in this case, JSON_STORAGE_FREE() returns 0.

JSON_STORAGE_SIZE(json_val)
This function returns the number of bytes used to store the binary representation of a JSON document.


mysql> SELECT 
    ->     jcol, 
    ->     JSON_STORAGE_SIZE(jcol) AS Size, 
    ->     JSON_STORAGE_FREE(jcol) AS Free 
    -> FROM jtable;

JSON_STORAGE_SIZE(): Return value (bytes)
JSON_STORAGE_FREE(): If no updates have yet been performed, this is 0, as expected.


SELECT
    ->     JSON_STORAGE_SIZE('[100, "sakila", [1, 3, 5], 425.05]') AS A,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "a", "c": "[1, 3, 5, 7]"}') AS B,
    ->     JSON_STORAGE_SIZE('{"a": 1000, "b": "wxyz", "c": "[1, 3, 5, 7]"}') AS C, 
    ->     JSON_STORAGE_SIZE('[100, "json", [[10, 20, 30], 3, 5], 425.05]') AS D;


MySQL8.0から、Descending Indexがサポートされる。
これによりMySQL5.7と比較して、大きなテーブルでの降順(DESC)のデータ参照処理を、高速に実行する事が出来るようになる。小さいテーブルに関しては、既存のMySQL5.7でもインデックスが利用出来るので、それ程差は出ないかと思います。

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXはASCで作成されている)


mysql> select @@version;                                              
+-----------+
| @@version |
+-----------+
| 8.0.1-dmr |
+-----------+
1 row in set (0.00 sec)

mysql> show create table rental\G                                     
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3; 
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.00 sec)

mysql> 

MySQL8.0.1で、Sakila Sampleデータベースのrentalテーブルを参照した場合 (INDEXをDESCで作成し直した場合)
インデックスを降順に作成したので、先程とはEXTRAが反対になっている事が確認出来る。

mysql> alter table rental add unique key rental_date_desc (`rental_date` desc,`inventory_id` desc,`customer_id` desc);
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from rental order by rental_date asc limit 3; 
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;                                                
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra               |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | Backward index scan |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+---------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;                                               
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date_desc | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> 

詳細は、以下のMySQL Server Teamのブログを確認下さい。
MySQL 8.0 Labs – Descending Indexes in MySQL
http://mysqlserverteam.com/mysql-8-0-labs-descending-indexes-in-mysql/

MySQL5.5 ~ MySQL5.7までは昇順(ASC)で格納されたインデックスデータを利用してASC, DESC共に参照処理を行う
MySQL 5.7までは、インデックスのデータはASCで格納されています。こちらは、DESCで格納したインデックスと比べて、降順のデータの参照パフォーマンスは遅いが、
クエリーにDESCオプションを付けて、直近のデータを参照するとASCで作成したINDEXを利用してデータを参照する為、インデックスを利用した後方参照処理になる為、
インデックスが無い場合と比べても高速なレスポンスで後方参照処理を行う事が出来る。以下のマニュアルは、若干分かり難いがASC、DESCを付けてインデックスを作成しても、
MySQL5.5以降のMySQLであれば昇順(ASC)で作成されたINDEXでASCもDESCも処理出来る為、高速に参照する事が可能です。MySQL8.0で処理がより高速になる。

5.7 英語マニュアル
https://dev.mysql.com/doc/refman/5.7/en/create-index.html
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

5.6 日本語マニュアル
https://dev.mysql.com/doc/refman/5.6/ja/create-index.html
index_col_name の指定を ASC または DESC で終了させることができます。これらのキーワードは、インデックス値の昇順または降順での格納を指定する将来の拡張のために許可されています。現在、これらは解析されますが、無視されます。インデックス値は、常に昇順で格納されます。

MySQL5.7.18の実行プランは以下のような感じです。インデックスを利用して降順(DESC)参照が行われている事が確認出来る。
MySQL8.0RCがリリースされた頃に、大きなテーブルでレスポンスの差を確認して見たいと思います。


mysql> select @@version;
+-------------------------------------------+
| @@version                                 |
+-------------------------------------------+
| 5.7.18-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> select * from rental limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental limit 3;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16005 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` limit 3

mysql> select * from rental order by rental_date asc limit 3;
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date         | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
|         1 | 2005-05-24 22:53:30 |          367 |         130 | 2005-05-26 22:04:30 |        1 | 2006-02-15 21:30:53 |
|         2 | 2005-05-24 22:54:33 |         1525 |         459 | 2005-05-28 19:40:33 |        1 | 2006-02-15 21:30:53 |
|         3 | 2005-05-24 23:03:39 |         1711 |         408 | 2005-06-01 22:12:39 |        1 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+---------------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date asc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` limit 3

mysql> select * from rental order by rental_date desc limit 3;
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
| rental_id | rental_date         | inventory_id | customer_id | return_date | staff_id | last_update         |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
|     11739 | 2006-02-14 15:16:03 |         4568 |         373 | NULL        |        2 | 2006-02-15 21:30:53 |
|     14616 | 2006-02-14 15:16:03 |         4537 |         532 | NULL        |        1 | 2006-02-15 21:30:53 |
|     11676 | 2006-02-14 15:16:03 |         4496 |         216 | NULL        |        2 | 2006-02-15 21:30:53 |
+-----------+---------------------+--------------+-------------+-------------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> explain select * from rental order by rental_date desc limit 3;
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | rental | NULL       | index | NULL          | rental_date | 10      | NULL |    3 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Note (Code 1003): /* select#1 */ select `sakila`.`rental`.`rental_id` AS `rental_id`,`sakila`.`rental`.`rental_date` AS `rental_date`,`sakila`.`rental`.`inventory_id` AS `inventory_id`,`sakila`.`rental`.`customer_id` AS `customer_id`,`sakila`.`rental`.`return_date` AS `return_date`,`sakila`.`rental`.`staff_id` AS `staff_id`,`sakila`.`rental`.`last_update` AS `last_update` from `sakila`.`rental` order by `sakila`.`rental`.`rental_date` desc limit 3
mysql>  


MySQL8.0におけるROLEによるユーザー権限管理についての確認。
MySQL5.7からProxy Userを利用する事で、ROLEと同じように権限をまとめて管理する事が出来るようになりましたが、MySQL8.0からは正式にROLEが追加されて、複数ROLEの切り替えやROLEのネストが出来るようになります。これにより、多くのユーザーを管理するような環境では、権限管理工数やミスが削減出来る事になるかと思います。

7.3.4 Using Roles
https://dev.mysql.com/doc/refman/8.0/en/roles.html

ROLEの作成とROLEへの権限付与

mysql> CREATE ROLE role80;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL on DEMO.* TO role80;
Query OK, 0 rows affected (0.05 sec)

mysql> create user user01 identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> select host, user, authentication_string from mysql.user;
+-----------+-----------+-------------------------------------------+
| host      | user      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| %         | role80    |                                           |
| %         | root      | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| %         | user01    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+
4 rows in set (0.02 sec)

USER01にrole80の権限を付与
ROLE権限を付与する前は、何も権限を付与していない為、information_schemaのみ確認可能。

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> grant role80 to user01;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
1 row in set (0.06 sec)

mysql> select * from mysql.default_roles;
Empty set (0.00 sec)

mysql> 

ユーザーのDEFAULT ROLEを設定しログイン時に反映させる


mysql> alter user user01 default role role80;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.default_roles;    
+------+--------+-------------------+-------------------+
| HOST | USER   | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+--------+-------------------+-------------------+
| %    | user01 | %                 | role80            |
+------+--------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

権限が継承されているかログインして確認してみる


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user(),current_role(); 
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+--------------------------------------------------+
| Grants for user01@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for user01; 
+------------------------------------+
| Grants for user01@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%` |
+------------------------------------+
2 rows in set (0.01 sec)

mysql> show grants for user01 using role80;
+--------------------------------------------------+
| Grants for user01@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%` |
| GRANT `role80`@`%` TO `user01`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql>  

補足:Default Roleを設定しない場合
確認用アカウントuser02を作成


mysql> create user user02 identified by 'password';                                                              
Query OK, 0 rows affected (0.07 sec)

mysql> grant role80 to user02;
Query OK, 0 rows affected (0.01 sec)

mysql> 

Deault Roleが無い場合は、ログイン後にSETコマンドでROLEを選択する


[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | NONE           |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> set role role80;
Query OK, 0 rows affected (0.00 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;                             
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

再起動するとSETコマンドで設定した値は消えるので、必要に応じて”set default role”コマンドでDefault Roleを設定する

mysql> set default role role80 to user02;
Query OK, 0 rows affected (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | NONE           |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> exit
Bye
[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser02 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user02@localhost | user02@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

その他参考:roleはpassword_expireとaccount_lockedで設定されている
通常のアカウント同様に利用したい場合、必要であればexpiredとlockedを解除してあげる。

mysql> select host, user, authentication_string,password_expired,account_locked  from mysql.user;
+-----------+-----------+-------------------------------------------+------------------+----------------+
| host      | user      | authentication_string                     | password_expired | account_locked |
+-----------+-----------+-------------------------------------------+------------------+----------------+
| %         | role80    |                                           | Y                | Y              |
| %         | root      | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | N                | N              |
| %         | user01    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N                | N              |
| %         | user02    | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N                | N              |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | Y              |
+-----------+-----------+-------------------------------------------+------------------+----------------+
5 rows in set (0.01 sec)

mysql> 

ユーザーに複数のROLEの権限を付与してみる
先ずは、ROLEを追加作成してsakilaデータベースに権限付与してみる


mysql> CREATE ROLE role81;    
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL on sakila.* TO role81;
Query OK, 0 rows affected (0.01 sec)

mysql> grant role81 to user01;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
| %         | role80    | %       | user02  | N                 |
| %         | role81    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
3 rows in set (0.00 sec)

mysql> 

Default Roleはrole80になっているので、DEMOデータベースのみにアクセス出来るが、SETコマンドでroleを切り替える事が出来る。

[root@DockerHost oracle]# docker exec -it mysql8 mysql -uuser01 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
+--------------------+
2 rows in set (0.01 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role80`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> set role role81;          
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;                             
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sakila             |
+--------------------+
2 rows in set (0.00 sec)

mysql> select user(),current_user(),current_role();
+------------------+----------------+----------------+
| user()           | current_user() | current_role() |
+------------------+----------------+----------------+
| user01@localhost | user01@%       | `role81`@`%`   |
+------------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> 

ROLEにROLE権限をネストして付与してみる


mysql> grant role81 to role80;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from mysql.role_edges;
+-----------+-----------+---------+---------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+---------+---------+-------------------+
| %         | role80    | %       | user01  | N                 |
| %         | role80    | %       | user02  | N                 |
| %         | role81    | %       | role80  | N                 |
| %         | role81    | %       | user01  | N                 |
+-----------+-----------+---------+---------+-------------------+
4 rows in set (0.01 sec)

mysql> show grants for role80@'%';
+--------------------------------------------------+
| Grants for role80@%                              |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `role80`@`%`               |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `role80`@`%` |
| GRANT `role81`@`%` TO `role80`@`%`               |
+--------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show grants for role81@'%';
+----------------------------------------------------+
| Grants for role81@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `role81`@`%`                 |
| GRANT ALL PRIVILEGES ON `sakila`.* TO `role81`@`%` |
+----------------------------------------------------+
2 rows in set (0.00 sec)

role80のメンバーのuser01でログインして、付与された権限を確認してみる。
role80には、role81の権限が付与されているのでDEMOとsakilaデータベース共にアクセス可能になっている。


mysql> select user(),current_role(); 
+------------------+----------------+
| user()           | current_role() |
+------------------+----------------+
| user01@localhost | `role80`@`%`   |
+------------------+----------------+
1 row in set (0.00 sec)

mysql> show grants;
+----------------------------------------------------+
| Grants for user01@%                                |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `user01`@`%`                 |
| GRANT ALL PRIVILEGES ON `DEMO`.* TO `user01`@`%`   |
| GRANT ALL PRIVILEGES ON `sakila`.* TO `user01`@`%` |
| GRANT `role80`@`%`,`role81`@`%` TO `user01`@`%`    |
+----------------------------------------------------+
4 rows in set (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| DEMO               |
| information_schema |
| sakila             |
+--------------------+
3 rows in set (0.00 sec)

mysql>