🛁
Aurora3.0 系でも utf8mb4_general_ci を使い続けたい僕らは ~ 序章 ~
このタイトルを見て気づいた方はいますよね、そうです、@mitan2 さんの 2020-12-07 の「MySQL 8.0 でも utf8mb4_general_ci を使い続けたい僕らは」をもじったものです。
前提条件
-
今回使用するのは「sakila」Database
-
Aurora のバージョン
Aurora の設定値
mysql> SELECT @@aurora_version; +------------------+ | @@aurora_version | +------------------+ | 3.06.0 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT @@version; +-----------+ | @@version | +-----------+ | 8.0.34 | +-----------+ 1 row in set (0.00 sec) -
COLLATION
Aurora の設定値
mysql> SHOW VARIABLES LIKE '%collation%'; +-------------------------------+--------------------+ | Variable_name | Value | +-------------------------------+--------------------+ | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_bin | | collation_server | utf8mb4_bin | | default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci | +-------------------------------+--------------------+ 4 rows in set (0.00 sec) -
sakila DB の collation
sakila DB の collation 一覧
mysql> SELECT information_schema.COLUMNS.TABLE_SCHEMA, information_schema.COLUMNS.TABLE_NAME, information_schema.TABLES.TABLE_TYPE, information_schema.COLUMNS.COLUMN_NAME, information_schema.COLUMNS.COLLATION_NAME FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME != 'utf8mb4_general_ci' AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL; +--------------+----------------------------+------------+---------------------------+--------------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | COLUMN_NAME | COLLATION_NAME | +--------------+----------------------------+------------+---------------------------+--------------------+ | sakila | actor | BASE TABLE | first_name | utf8mb4_0900_ai_ci | | sakila | actor | BASE TABLE | last_name | utf8mb4_0900_ai_ci | | sakila | actor_info | VIEW | film_info | utf8mb4_0900_ai_ci | | sakila | actor_info | VIEW | first_name | utf8mb4_0900_ai_ci | | sakila | actor_info | VIEW | last_name | utf8mb4_0900_ai_ci | | sakila | address | BASE TABLE | address | utf8mb4_0900_ai_ci | | sakila | address | BASE TABLE | address2 | utf8mb4_0900_ai_ci | | sakila | address | BASE TABLE | district | utf8mb4_0900_ai_ci | | sakila | address | BASE TABLE | phone | utf8mb4_0900_ai_ci | | sakila | address | BASE TABLE | postal_code | utf8mb4_0900_ai_ci | | sakila | category | BASE TABLE | name | utf8mb4_0900_ai_ci | | sakila | city | BASE TABLE | city | utf8mb4_0900_ai_ci | | sakila | country | BASE TABLE | country | utf8mb4_0900_ai_ci | | sakila | customer | BASE TABLE | email | utf8mb4_0900_ai_ci | | sakila | customer | BASE TABLE | first_name | utf8mb4_0900_ai_ci | | sakila | customer | BASE TABLE | last_name | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | address | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | city | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | country | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | name | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | notes | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | phone | utf8mb4_0900_ai_ci | | sakila | customer_list | VIEW | zip code | utf8mb4_0900_ai_ci | | sakila | film | BASE TABLE | description | utf8mb4_0900_ai_ci | | sakila | film | BASE TABLE | rating | utf8mb4_0900_ai_ci | | sakila | film | BASE TABLE | special_features | utf8mb4_0900_ai_ci | | sakila | film | BASE TABLE | title | utf8mb4_0900_ai_ci | | sakila | film_list | VIEW | actors | utf8mb4_0900_ai_ci | | sakila | film_list | VIEW | category | utf8mb4_0900_ai_ci | | sakila | film_list | VIEW | description | utf8mb4_0900_ai_ci | | sakila | film_list | VIEW | rating | utf8mb4_0900_ai_ci | | sakila | film_list | VIEW | title | utf8mb4_0900_ai_ci | | sakila | film_text | BASE TABLE | description | utf8mb4_0900_ai_ci | | sakila | film_text | BASE TABLE | title | utf8mb4_0900_ai_ci | | sakila | language | BASE TABLE | name | utf8mb4_0900_ai_ci | | sakila | nicer_but_slower_film_list | VIEW | actors | utf8mb4_0900_ai_ci | | sakila | nicer_but_slower_film_list | VIEW | category | utf8mb4_0900_ai_ci | | sakila | nicer_but_slower_film_list | VIEW | description | utf8mb4_0900_ai_ci | | sakila | nicer_but_slower_film_list | VIEW | rating | utf8mb4_0900_ai_ci | | sakila | nicer_but_slower_film_list | VIEW | title | utf8mb4_0900_ai_ci | | sakila | row_format_compact | BASE TABLE | description | utf8mb4_0900_ai_ci | | sakila | row_format_compact | BASE TABLE | row_format_compact_text | utf8mb4_0900_ai_ci | | sakila | row_format_redundant | BASE TABLE | description | utf8mb4_0900_ai_ci | | sakila | row_format_redundant | BASE TABLE | row_format_redundant_text | utf8mb4_0900_ai_ci | | sakila | sales_by_film_category | VIEW | category | utf8mb4_0900_ai_ci | | sakila | sales_by_store | VIEW | manager | utf8mb4_0900_ai_ci | | sakila | sales_by_store | VIEW | store | utf8mb4_0900_ai_ci | | sakila | staff | BASE TABLE | email | utf8mb4_0900_ai_ci | | sakila | staff | BASE TABLE | first_name | utf8mb4_0900_ai_ci | | sakila | staff | BASE TABLE | last_name | utf8mb4_0900_ai_ci | | sakila | staff | BASE TABLE | password | utf8mb4_bin | | sakila | staff | BASE TABLE | username | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | address | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | city | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | country | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | name | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | phone | utf8mb4_0900_ai_ci | | sakila | staff_list | VIEW | zip code | utf8mb4_0900_ai_ci | +--------------+----------------------------+------------+---------------------------+--------------------+ 58 rows in set (0.01 sec)
これを utf8mb4_general_ci に変更してみる
手順は単純、楽な方法で置換
-
COLLATION を
ALTER TABLE .. CONVERT TO CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';で一気に変換for table in `mysql --defaults-extra-file=/tmp/.aurora3.cnf -N -e "SELECT CONCAT(information_schema.COLUMNS.TABLE_SCHEMA, '.', information_schema.COLUMNS.TABLE_NAME) TARGET_TABLE FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.TABLES.TABLE_TYPE = 'BASE TABLE' AND information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME != 'utf8mb4_general_ci' AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL GROUP BY TARGET_TABLE;"`; do echo "${table}" mysql --defaults-extra-file=/tmp/.aurora3.cnf -vvv -e "ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci';" sleep 1 doneLOG
# for table in `mysql --defaults-extra-file=/tmp/.aurora3.cnf -N -e "SELECT CONCAT(information_schema.COLUMNS.TABLE_SCHEMA, '.', information_schema.COLUMNS.TABLE_NAME) TARGET_TABLE FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.TABLES.TABLE_TYPE = 'BASE TABLE' AND information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME != 'utf8mb4_general_ci' AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL GROUP BY TARGET_TABLE;"`; do > echo "${table}" > mysql --defaults-extra-file=/tmp/.aurora3.cnf -vvv -e "ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci';" > sleep 1 > done sakila.actor -------------- ALTER TABLE sakila.actor CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 200 rows affected (0.15 sec)Records: 200 Duplicates: 0 Warnings: 0 Bye sakila.address -------------- ALTER TABLE sakila.address CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.category -------------- ALTER TABLE sakila.category CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.city -------------- ALTER TABLE sakila.city CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.country -------------- ALTER TABLE sakila.country CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.customer -------------- ALTER TABLE sakila.customer CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 599 rows affected (0.20 sec) Records: 599 Duplicates: 0 Warnings: 0 Bye sakila.film -------------- ALTER TABLE sakila.film CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 1000 rows affected (0.21 sec) Records: 1000 Duplicates: 0 Warnings: 0 Bye sakila.film_text -------------- ALTER TABLE sakila.film_text CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 1000 rows affected (0.55 sec) Records: 1000 Duplicates: 0 Warnings: 0 Bye sakila.language -------------- ALTER TABLE sakila.language CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.row_format_compact -------------- ALTER TABLE sakila.row_format_compact CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.row_format_redundant -------------- ALTER TABLE sakila.row_format_redundant CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye sakila.staff -------------- ALTER TABLE sakila.staff CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci' -------------- Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 Bye ``` -
staff.password は utf8mb4_bin なので個別変更
ALTER TABLE sakila.staff MODIFY COLUMN `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL;LOG
mysql> SHOW CREATE TABLE sakila.staff \G *************************** 1. row *************************** Table: staff Create Table: CREATE TABLE `staff` ( `staff_id` tinyint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL, `last_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL, `address_id` smallint unsigned NOT NULL, `picture` blob, `email` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `store_id` tinyint unsigned NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `username` varchar(16) COLLATE utf8mb4_general_ci NOT NULL, `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`staff_id`), KEY `idx_fk_store_id` (`store_id`), KEY `idx_fk_address_id` (`address_id`), CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.01 sec) mysql> ALTER TABLE sakila.staff MODIFY COLUMN `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE sakila.staff \G *************************** 1. row *************************** Table: staff Create Table: CREATE TABLE `staff` ( `staff_id` tinyint unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL, `last_name` varchar(45) COLLATE utf8mb4_general_ci NOT NULL, `address_id` smallint unsigned NOT NULL, `picture` blob, `email` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL, `store_id` tinyint unsigned NOT NULL, `active` tinyint(1) NOT NULL DEFAULT '1', `username` varchar(16) COLLATE utf8mb4_general_ci NOT NULL, `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`staff_id`), KEY `idx_fk_store_id` (`store_id`), KEY `idx_fk_address_id` (`address_id`), CONSTRAINT `fk_staff_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`address_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_staff_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec)
よし、これで COLLATION が (基本的に) utf8mb4_general_ci に変わっただろうと思ったんだけど。。。
mysql> SELECT information_schema.COLUMNS.TABLE_SCHEMA, information_schema.COLUMNS.TABLE_NAME, information_schema.TABLES.TABLE_TYPE, information_schema.COLUMNS.COLUMN_NAME, information_schema.COLUMNS.COLLATION_NAME FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME != 'utf8mb4_general_ci' AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL;
+--------------+---------------+------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | COLUMN_NAME | COLLATION_NAME |
+--------------+---------------+------------+-------------+--------------------+
| sakila | customer_list | VIEW | notes | utf8mb4_0900_ai_ci |
| sakila | staff | BASE TABLE | password | utf8mb4_bin |
+--------------+---------------+------------+-------------+--------------------+
2 rows in set (0.01 sec)
な。。。なに？？ customer_list.notes が utf8mb4_0900_ai_ci ？！
見てみましょう・・
CREATE ALGORITHM = UNDEFINED DEFINER = `admin` @`%` SQL SECURITY DEFINER VIEW `customer_list` AS
select
`cu`.`customer_id` AS `ID`,
concat(`cu`.`first_name`, _utf8mb4 ' ', `cu`.`last_name`) AS `name`,
`a`.`address` AS `address`,
`a`.`postal_code` AS `zip code`,
`a`.`phone` AS `phone`,
`city`.`city` AS `city`,
`country`.`country` AS `country`,
if(`cu`.`active`, _utf8mb4 'active', _utf8mb4 '') AS `notes`, `cu`.`store_id` AS `SID`
from
(
(
(
`customer` `cu`
join `address` `a` on ((`cu`.`address_id` = `a`.`address_id`))
)
join `city` on ((`a`.`city_id` = `city`.`city_id`))
)
join `country` on ((`city`.`country_id` = `country`.`country_id`))
)
DROP → CREATE VIEW
DROP → CREATE VIEW
mysql> DROP VIEW customer_list;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE ALGORITHM = UNDEFINED DEFINER = `admin` @`%` SQL SECURITY DEFINER VIEW `customer_list` AS
-> select
-> `cu`.`customer_id` AS `ID`,
-> concat(`cu`.`first_name`, _utf8mb4 ' ', `cu`.`last_name`) AS `name`,
-> `a`.`address` AS `address`,
-> `a`.`postal_code` AS `zip code`,
-> `a`.`phone` AS `phone`,
-> `city`.`city` AS `city`,
-> `country`.`country` AS `country`,
-> if(`cu`.`active`, _utf8mb4 'active', _utf8mb4 '') AS `notes`,
-> `cu`.`store_id` AS `SID`
-> from
-> (
-> (
-> (
-> `customer` `cu`
-> join `address` `a` on ((`cu`.`address_id` = `a`.`address_id`))
-> )
-> join `city` on ((`a`.`city_id` = `city`.`city_id`))
-> )
-> join `country` on ((`city`.`country_id` = `country`.`country_id`))
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT information_schema.COLUMNS.TABLE_SCHEMA, information_schema.COLUMNS.TABLE_NAME, information_schema.TABLES.TABLE_TYPE, information_schema.COLUMNS.COLUMN_NAME, information_schema.COLUMNS.COLLATION_NAME FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME != 'utf8mb4_general_ci' AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL;
+--------------+---------------+------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | COLUMN_NAME | COLLATION_NAME |
+--------------+---------------+------------+-------------+--------------------+
| sakila | customer_list | VIEW | notes | utf8mb4_0900_ai_ci |
| sakila | staff | BASE TABLE | password | utf8mb4_bin |
+--------------+---------------+------------+-------------+--------------------+
2 rows in set (0.00 sec)
- 失敗・・・
- https://dev.mysql.com/doc/refman/8.0/ja/charset-collation-coercibility.html
- この辺りが関係あるのかもしれない
- なのでちょっとやり方を変えてみる
defaults_collation_for_utf8mb4 を指定
- 本当はこの設定をグローバル変数で設定できればいいんですが。。Aurora 上ではグローバル変数には設定できないので・・・
SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;で変えないといけないのです。。。無念。。一旦これでやってみます。
SET SESSION default_collation_for_utf8mb4 → DROP → CREATE VIEW
mysql> SET SESSION default_collation_for_utf8mb4=utf8mb4_general_ci;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP VIEW customer_list;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE ALGORITHM = UNDEFINED DEFINER = `admin` @`%` SQL SECURITY DEFINER VIEW `customer_list` AS
-> select
-> `cu`.`customer_id` AS `ID`,
-> concat(`cu`.`first_name`, _utf8mb4 ' ', `cu`.`last_name`) AS `name`,
-> `a`.`address` AS `address`,
-> `a`.`postal_code` AS `zip code`,
-> `a`.`phone` AS `phone`,
-> `city`.`city` AS `city`,
-> `country`.`country` AS `country`,
-> if(`cu`.`active`, _utf8mb4 'active', _utf8mb4 '') AS `notes`, `cu`.`store_id` AS `SID`
-> from
-> (
-> (
-> (
-> `customer` `cu`
-> join `address` `a` on ((`cu`.`address_id` = `a`.`address_id`))
-> )
-> join `city` on ((`a`.`city_id` = `city`.`city_id`))
-> )
-> join `country` on ((`city`.`country_id` = `country`.`country_id`))
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT information_schema.COLUMNS.TABLE_SCHEMA, information_schema.COLUMNS.TABLE_NAME, information_schema.TABLES.TABLE_TYPE, information_schema.COLUMNS.COLUMN_NAME, information_schema.COLUMNS.COLLATION_NAME FROM information_schema.TABLES INNER JOIN information_schema.COLUMNS ON information_schema.TABLES.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA AND information_schema.TABLES.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME WHERE information_schema.COLUMNS.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys') AND information_schema.COLUMNS.COLLATION_NAME NOT IN ('utf8mb4_general_ci', 'utf8mb4_bin') AND information_schema.COLUMNS.COLLATION_NAME IS NOT NULL;
Empty set (0.01 sec)
- 変わった！なるほどこのケースは
default_collation_for_utf8mb4が影響していたということなのか。。。
つまりこうゆうことっぽい。。。
この先にあったものは。。。
さて、問題はここまでで終わりませんでした。。今日は時間切れです。。。
次回は CAST やら CONVERT 周りで起こった Illegal mix of collations for operation についてお話ししたいなと思っています。。
Discussion