なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」

MySQL

RENAME TABLEなんてコマンド、知らなかったよ。。。

テーブル名の変更といえば、「ALTER TABLE old_table_name RENAME TO new_table_name」だと思っていたOracle厨でしたが、MySQLの案件に絡んでいたら「RENAME TABLE old_table_name TO new_table_name」なんてものがあると知りました。


調べていくと、どうやら「テーブル名の変更」については、ANSI/ISO SQL　Standardの中で定義されていなくて、各RDBMSの実装に委ねられているみたいです。

処理は一瞬

MySQL 5.6.31で実験してみましたが、データ量に関係なく、処理は一瞬でした。

mysql> select count(*) from test_touch_x;
+----------+
| count(*) |
+----------+
| 16777216 |
+----------+
1 row in set (3 min 7.98 sec)

mysql> ALTER TABLE test_touch_x  RENAME TO test_touch_y;
Query OK, 0 rows affected (0.12 sec)

mysql> RENAME TABLE test_touch_y TO test_touch_z;
Query OK, 0 rows affected (0.12 sec)

わざわざ1600万件、ibdファイルのサイズで9.3GBに相当するテーブルを作ってから試したのですが、名前変更にはデータサイズは何の影響もありませんでした。

違いは何？

MySQL固有の話、バージョンにも依存するのだと思いますが、違いは、構文および指定可能なオプションの違いに着目すると見えてきます。


RENAME TABLEの場合

構文は以下のとおり。

RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


一文で複数の処理ができ、先に書いた処理が先行する、というルールがあって、以下のように、テーブル名の入れ替えを1文で実行できます。

このステートメントで複数のテーブルの名前を変更する場合、名前の変更操作は左から右に実行されます。2 つのテーブル名をスワップする場合は、次のように実行できます (tmp_table はまだ存在していないと仮定します)。
RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


そして、複数同時に処理させている途中にエラーが起きた時、全部巻き戻されます。

複数テーブルの名前変更で何らかのエラーが発生した場合、MySQL はすべてをその元の状態に戻すために、名前変更されたすべてのテーブルに対して逆方向の名前変更を実行します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.32 RENAME TABLE 構文


なお、VIEWに対しては実行できる一方、TEMPORARYテーブルには実行できない、という制約があります。



ここ一連のエントリで紹介している、
MariaDB&MySQL全機能バイブル
には

なお、RENAME TABLE文の実行中はテーブルがロックされるので、他のセッションからはアクセスできません。

という一文があるのですが、とにかく普通に名前変更するだけだと、前述の通り処理が速いので、ほとんど気にならないと思います。


同じ本のなかでは更に

データベースを跨いで、テーブルを移動することも可能です。ただし、テーブルにトリガが設定されている場合は移動することができません。

という記述があります。


これは「RENAME TO」の解説の中で記述があるのですが、だけではなく、「RENAME TO」だけでなく「ALTER TABLE RENAME」でも対応できます。


そして、処理自体は、やはり一瞬で終わります。


ここまでずっと「INNODBストレージエンジン、innodb_file_per_table=1（テーブル毎にデータファイルを分ける）」という前提で勝手に語ってましたが、この設定ではディレクトリ構造上、データベース名と同じディレクトリの下に、「テーブル名.frm」「テーブル名.ibd」が存在するわけで、リネーム処理の背後では、この２つのファイルに対して、LinuxのOSコマンドでいう「mv old_name new_name」を実行しているようです。


確かに、ディレクトリを跨ぐmvコマンド発行しても、ファイルサイズで処理時間が変わるという記憶ないな。。。

ALTER TABLE RENAMEの場合

ALTER TABLEのターゲットは１つしか指定できないので、RENAME TOによる名前変更自体は１つずつしかできませんが、代わりに、ADD COLUMNとか「他のALTER TABLE操作」と同時に実行が可能です。

一部の操作では、一時テーブルを必要としないインプレース ALTER TABLE が可能です。


ALTER TABLE tbl_name RENAME TO new_tbl_name をほかのオプションなしで実行すると、MySQL は単純に、コピーを作成することなく、テーブル tbl_name に対応するすべてのファイルの名前を変更します。(RENAME TABLE ステートメントを使用してテーブルの名前を変更することもできます。セクション13.1.32「RENAME TABLE 構文」を参照してください。)名前変更されたテーブル専用に付与された権限は、どれも新しい名前には移行されません。それらは、手動で変更する必要があります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.7 ALTER TABLE 構文

ALTER TABLEとALGORITHMオプションの話に入ると深みにハマるのですが、テーブルの再構成が不要な操作についてはコピーを生成せずに済ませてくれるINPLACEモードで処理してくれます。


RENAME TO単独でもCPOYモード指定できますが無意味ですね。それよりも、RENAME TOと同時にADD COLUMNなど再構成が必要な他の操作を組み合わせると、COPYモード発動で時間がかかるようになるので要注意です。



同様に、LOCKのオプションも指定可能です。




その辺の話も、ここで散々引用している公式ドキュメントのページに書いてあります。


まとめ

  • テーブル名の変更は、SQL標準にないので、RDBMS次第。
  • MySQLの場合、「ALTER TABLE RENAME」と「RENAME TABLE」どちらも使えて、処理速度に大差なし。
  • RENAME TABLEの場合、複数テーブルの変更が1文で可能。複数指定時の処理順序も先に書いた方からで一定。
  • ALTER TABLE RENAMEは、１テーブルずつしか名前変更できないが、同じ対象テーブルに対する他のALTER TABLE操作を1文の中で同時実行可能。


MariaDB&MySQL全機能バイブル

MariaDB&MySQL全機能バイブル