(構造編)phpMyAdminを利用したエクスポートオプションと出力の関係

2013/09/28 2014/11/22

phpMyAdminを利用してSQLのエクスポートを行う際に、
どのような設定でエクスポートすれば取り込みの際に、
安定して取り込むことができるでしょうか。
各設定の選択と出力されるSQLの各オプション設定をご紹介します。

はじめに

皆さんはphpMyAdminからエクスポートしたSQLソースで、
いざ取り込みを行った際に、エラーが出て取り込めなかった経験はないでしょうか。

新規のデータベースに取り込みを行う際の出力であったり、
既存のデータベース向けに出力する際では出力の方法が異なります。

しかし、日々のバックアップとバックアップデータの確認、
いざという時のリストア作業で異なるエクスポートファイルを使うのでは、
あまり効率的ではありません。

どのような設定でエクスポートすれば、
より汎用性の高いSQL文が生成されるのかを実際に、
各オプションでエクスポートして検証しましたのでご紹介します。

phpMyAdminで構造の出力を選択し、
表示される出力オプションは以下のようなものがあります。

構造エクスポートオプション

この各オプションをそれぞれONにした際に、どのようなSQL文が生成されるのかを検証します。
※上記で[構造]を選択しているのは検証用のパターンとしての選択です。
　ご利用のデータベースのでは通常通り[構造とデータ]を選択してください。
　移行先のデータベースにデータが移行されません。

各オプションがON状態を検証ケースに分けて以下のように分類し明記します。
それぞれのエクスポート結果と、インポートの結果を比較していきます。

構造エクスポートの設定と出力
CASE	ON	備考
C01	DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT コマンドの追加
C02	CREATE PROCEDURE / FUNCTION / EVENT コマンドの追加
C03	CREATE TABLE オプション
C04	IF NOT EXISTS	C03はOFF
C05	AUTO_INCREMENT	C03はOFF
C06	C-01とC-02をともにON

上記の条件で各ケースを出力した結果、以下のようにSQL文が生成されます。

構造エクスポートの設定と出力
対象	処理	Option	C01	C02	C03	C04	C05	C06	備考
TABLE	DROP	IF EXISTS	●					●
〃	CREATE	－	●	●	●		●	●
〃	〃	IF NOT EXISTS				●
VIEW	DROP TABLE	IF EXISTS	●	●	●	●	●	●	代替構造
〃	CREATE TABLE	－	●	●	●		●	●	代替構造
〃	〃	IF NOT EXISTS				●			代替構造
〃	DROP VIEW	IF EXISTS	●					●
〃	CREATE VIEW	－	●	●	●	●	●	●
PROCEDURE	DROP	IF EXISTS						●
〃	CREATE	－		●				●
FUNCTION	DROP	IF EXISTS						●
〃	CREATE	－		●				●
EVENT	DROP	－						●
〃	CREATE	－		●				●

上記の出力結果を見ていくと、
当然ながらDROP ～が出力されていない状態で、
CREATE ～が生成されている場合が多く見受けられます。

その状態で、CREATE ～ IF NOT EXISTS のオプションが生成されていない場合は、
当然ながら、既に対象の構造が存在している場合には、エラーとなります。

ただし、EVENT要素に関してのCREATE・DROPでIF判定がありませんから、
既存のデータベースに対して取り込む際には常に注意が必要となります。

なお、イベントはその特性上、データベースに対してインポートを行うものではなく、
データベーススキーマ（インスタンス）に対して一意の名称で管理されます。

空のデータベースに対して、各ケースによって出力されたSQLの取り込みを行った結果、
以下のような取り込み結果が得られました。

結局のところ、C02のケース以外は、エクスポート元のデータベースに存在する構造から、
何かしらの欠損（不足）が発生する状態での移行になります。

普通に考えるとC06（一旦すべてを消し再作成を行う）のケースでは、
エラーが発生せず、新しくデータベースが移行できそうに思いますが、
EVENTのDROP処理でIF EXISTSがないことから、エラーとなってしまいます。
※EVENTのDROP行をSQLから削除すれば当然問題は解決できます。

よく言われるようにデータベースの移行では「移行先を一旦すべて消す必要がある」というのは、
C06のように移行を行った場合のことを示していることになりますが、
実際には新規データベースへの移行であれば、C02で事足ります。

データを消す必要があるかどうかは、データの移行方法の問題です。

構造インポート(新規)
CASE	処理結果	T	V	P	F	E	メッセージ
C01	正常	●	●	－	－	－	インポートは正常終了しました。16 個のクエリを実行しました。 (C-01.sql)
C02	正常	●	●	●	●	●	インポートは正常終了しました。17 個のクエリを実行しました。 (C-02.sql)
C03	正常	●	●	－	－	－	インポートは正常終了しました。14 個のクエリを実行しました。 (C-03.sql)
C04	正常	●	●	－	－	－	インポートは正常終了しました。14 個のクエリを実行しました。 (C-04.sql)
C05	正常	●	●	－	－	－	インポートは正常終了しました。14 個のクエリを実行しました。 (C-05.sql)
C06	エラー	●	●	●	●	×	#1539 – Unknown event ‘delete_test’

※T:テーブル、V:ビュー、P:プロシージャ、F:ファンクション、E:イベント
●は正常に作成されたことを示す。－は処理されず生成されなかったことを示す。
×は処理過程でエラー終了を示す。

出力元となった既存のデータベースに対して、
各ケースによって出力されたSQLの取り込みを行った結果、
以下のような取り込み結果が得られました。

結局のところ、C01とC06以外のケースでは、
設計要素が既に存在するとのことで正常に取り込みを行うことができませんでした。

C01では正常に処理が行われていますが、既存データベースであれば問題になりませんが、
このSQLを新規のデータベースに対して実行した場合には、
プロシージャ・ファンクション・イベントが存在しないデータベースとして移行されます。

C02、C03、C05では既存のテーブルの有無判定を行わずに、
CREATE TABLEを行ってしまっている為、既に存在しているとのエラーになります。

C04ではテーブルに関しては、IF NOT EXISTSで既存判定を行っているものの、
ビューに関してはDROPすることなく、CREATE VIEWを行ってしまっている為エラーになります。

C06ではすべての構造要素を一旦すべて削除したのちに新しく作成を行うため、
新規・既存を問わず再現が行えますが、一旦すべてを削除するというリスクが付きまといます。

構造インポート（既存）
CASE	処理結果	T	V	P	F	E	メッセージ
C01	正常終了	●	●	○	○	○	インポートは正常終了しました。16 個のクエリを実行しました。 (C-01.sql)
C02	エラー	○	○	×	○	○	#1304 – PROCEDURE pc_test already exists
C03	エラー	×	○	○	○	○	#1050 – Table ‘test’ already exists
C04	エラー	●	×	○	○	○	#1050 – Table ‘view_test’ already exists
C05	エラー	×	○	○	○	○	#1050 – Table ‘test’ already exists
C06	正常終了	●	●	●	●	●	インポートは正常終了しました。22 個のクエリを実行しました。 (C-06.sql)