旅行メディアシステム部のサエキと申します。本記事はMySQLのVIEWに助けられた体験の紹介とVIEWに対する各種操作の可否をまとめたものです。

MySQLのVIEWに助けられた話

先日担当しているサービスのMySQLのDB群を5.7から8.0へアップグレードし、一部をInnoDB クラスターに切り替えました。

メインシステムのDB（以下メインDB）から一部テーブルをレプリケーションフィルターを使ってサブシステムのDB（以下サブDB）に連携していたのですが、その対象テーブルはテーブル名が大文字でした。ただDBアップグレード作業でメインDBをInnoDB クラスターに切り替える計画でした。

InnoDB クラスターは Group Replication, MySQL router, MySQL Shell を組み合わせた高可用性ソリューションです。こちらの Group Replication の要件から一部を引用します。

小文字のテーブル名. すべてのグループメンバーで --lower-case-table-names を同じ値に設定します。グループレプリケーションに必要な InnoDB ストレージエンジンを使用するには、1 の設定が適切です。この設定は、すべてのプラットフォームでデフォルトであるわけではありません。

新メインDBではテーブル名が小文字に切り替わります。よってサブDBのレプリケーションソースを新メインDBに切り替える際には、サブDBのレプリケーションフィルターとテーブル定義のテーブル名を小文字化するレプリケーション再構築が必要になります。

メインDBでは lower_case_table_names = 1 のため大文字テーブル名で参照しても問題ありませんが、サブDBでは同設定ができない状況でした。このままではサブDBの小文字になってしまったテーブルを参照する処理をすべて書き換える必要があり、テーブル名変更と参照アプリケーションの修正リリースがきっかり同時にできることもなく多少のエラーを引き起こしてしまいます。

こういったテーブル名変更とアプリ側の切り替わりを同タイミングでできない場合には、VIEWを使って旧テーブル名への参照を新テーブルにつなげることで既存アプリケーションを修正せずに済ませることができます。

before_table を after_table に切り替えるフローを以下に示します。

create table after_table like before_table;
create view void_view as select * from after_table;
drop table after_table; -- 次のrenameまで void_view への参照は ERROR 1356 を起こします
rename table before_table to after_table, void_view to before_table;

過去にもVIEWでエイリアスを作るような同様の対処はしていましたが、Case Insensitiveな人間(やWindows)からすると同名の大文字のエイリアスを作ることになったのはちょっと面白い体験でした。

エイリアスのようなVIEWでできること、できないこと

今回のケースではレプリカ側の参照なので更新することはないのですが、一部のVIEWは更新もできます。エイリアスのようなVIEWに対して、TABLE同様の操作は可能なのか、できないときはどのようなメッセージが出るのかいくつかの操作で確認してみました。検証バージョンは8.0.28です。

select

できます。むしろできないと困りますね。select自体が失敗する場合は参照オブジェクトが存在するか、必要な権限があるかを確認しましょう。

insert(replace) delete update

できます。 VIEWの定義によってはDMLを実行できません。集約、集計、抽出を目的として作られる一般的なVIEWだと以下のようなエラーメッセージが出ます。

ERROR 1471 (HY000): The target table view_name of the INSERT is not insertable-into
ERROR 1288 (HY000): The target table view_name of the UPDATE is not updatable
ERROR 1288 (HY000): The target table view_name of the DELETE is not updatable

load data into table

できます。

lock tables

できます。

truncate table

できません。

mysql> truncate table view_name;
ERROR 1146 (42S02): Table 'sample.view_name' doesn't exist

analyze optimize

できません。

mysql> optimize table view_name;
+------------------+----------+----------+--------------------------------------+
| Table            | Op       | Msg_type | Msg_text                             |
+------------------+----------+----------+--------------------------------------+
| sample.view_name | optimize | Error    | 'sample.view_name' is not BASE TABLE |
| sample.view_name | optimize | status   | Operation failed                     |
+------------------+----------+----------+--------------------------------------+
2 rows in set (0.01 sec)

alter table

できません。

mysql> alter table view_name add key value_idx(value);
ERROR 1347 (HY000): 'sample.view_name' is not BASE TABLE

rename table

できます。

mysql> rename table view_name to view_name_after;
Query OK, 0 rows affected (0.01 sec)

alter table 構文ではできません。

mysql> alter table view_name rename to view_name_after;
ERROR 1347 (HY000): 'sample.view_name' is not BASE TABLE

show tables

TABLEと同様に出力されます。

mysql> show tables like '%_name';
+---------------------------+
| Tables_in_sample (%_name) |
+---------------------------+
| table_name                |
| view_name                 |
+---------------------------+
2 rows in set (0.00 sec)

TABLEのみ、VIEWのみを取得したい場合はinformation_schemaから取得しましょう。

show create table

できます。ただしVIEWに対する show create table は show create view を実行します。

mysql> show create table view_name \G
*************************** 1. row ***************************
                View: view_name
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_name` AS select `hoge`.`id` AS `id`,`hoge`.`value` AS `value` from `hoge`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

逆にTABLEに対して show create view を実行するとちゃんとエラーになります。

mysql> show create view table_name;
ERROR 1347 (HY000): 'sample.table_name' is not VIEW

drop table

できません。

mysql> drop table view_name;
ERROR 1051 (42S02): Unknown table 'sample.view_name'

create table like

できません。

mysql> create table view_copy_table like view_name;
ERROR 1347 (HY000): 'sample.view_name' is not BASE TABLE

まとめ

エイリアスのようなVIEWでできること・できないことをまとめたものが以下です。

statement	executable
select	yes
insert	yes
replace	yes
update	yes
delete	yes
load data into	yes
lock	yes
truncate	no
analyze	no
optimize	no
alter	no
rename	yes
show tables	yes
show create	yes
drop	no
create table like	no