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 に変更してみる

手順は単純、楽な方法で置換

1. 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
   done
   

   LOG

       # 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
       ```
   

2. 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 が影響していたということなのか。。。

つまりこうゆうことっぽい。。。

1. default_collation_for_utf8mb4 とは？
   • MySQL 8.0 では、default_collation_for_utf8mb4 システム変数を使用して、utf8mb4 文字セットのデフォルトの照合順序を指定可能
   • この変数はセッション単位、またはグローバル単位で設定可能ではあるが、Aurora においてはセッション単位でのみ設定可能
   • デフォルトでは、この値は utf8mb4_0900_ai_ci
2. 影響範囲
   • この変数が変更されると、明示的な COLLATE が指定されていない _utf8mb4 プレフィックス付きの文字リテラルに対して、新しいデフォルト照合順序が適用される
   • 具体的には、_utf8mb4 'active' や _utf8mb4 '' といった文字列リテラルが、新しいデフォルトの照合順序（この場合は utf8mb4_general_ci）を持つようになる
3. 今回のケース
   • default_collation_for_utf8mb4 を utf8mb4_general_ci に変更したセッションでビューを再作成したため、IF式内のリテラルに新しいデフォルト照合順序が適用
   • 結果として、notes 列も utf8mb4_general_ci の照合順序を持った

この先にあったものは。。。

さて、問題はここまでで終わりませんでした。。今日は時間切れです。。。
次回は CAST やら CONVERT 周りで起こった Illegal mix of collations for operation についてお話ししたいなと思っています。。