頑張らない!でも諦めない!必死にならずにええかげん

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

      2014/11/22

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

はじめに

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

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

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

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

環境

  • OS: windows7 x64
  • Apache: 2.2.17 (Win32)
  • MySQL: 5.6.10
  • PHP: 5.2.17
  • phpMyAdmin: 3.4.2

phpMyAdminの出力オプション(データ)

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

01_データエクスポートオプション

データエクスポートオプション

この各オプションをそれぞれONにした際に、どのようなSQL文が生成されるのかを検証します。

検証パターン

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

データエクスポートの設定と出力
CASE 構文 ON 備考
P01 -01 INSERT INSERT DELAYED コマンド [上の両方を行う]を選択
-02 INSERT IGNORE コマンド [上の両方を行う]を選択
-03 全ての INSERT コマンドに
カラム名を含める
-04 全ての INSERT コマンドを
複数行挿入で行う
-05 上の両方を行う -01、-02はOFF
-06 上のどちらでもない
P02 -01 UPDATE INSERT DELAYED コマンド [上の両方を行う]を選択
-02 INSERT IGNORE コマンド [上の両方を行う]を選択
-03 全ての INSERT コマンドに
カラム名を含める
-04 全ての INSERT コマンドを
複数行挿入で行う
-05 上の両方を行う -01、-02はOFF
-06 上のどちらでもない
P03 -01 REPLACE INSERT DELAYED コマンド [上の両方を行う]を選択
-02 INSERT IGNORE コマンド [上の両方を行う]を選択
-03 全ての INSERT コマンドに
カラム名を含める
-04 全ての INSERT コマンドを
複数行挿入で行う
-05 上の両方を行う -01、-02はOFF
-06 上のどちらでもない

※InnoDBなどに対して、INSERT DELAYEDを有効化した際には以下のようなエラーが発生します。
その為、データベースの種別はMyISAMを利用します。

#1616 - DELAYED option not supported for table 'test'

なお、処理結果の過程はいいから、処理の結果のみを知りたい方は、
以下まで読み飛ばしてください。
各処理結果の一覧

各構文の再確認

INSERT

12.2.4. INSERT 構文

INSERT は既存テーブルに新しい行を挿入します。INSERT ... VALUES と INSERT ... SET 型のステートメントは、明示的に指定された値に基づいて行を挿入します。INSERT ... SELECT 型は別のテーブルから選択された行を挿入します。INSERT ... SELECT については INSERT ... SELECT 構文」 でさらに詳しく説明されています。

古い行に上書きする為に、INSERT の代わりに REPLACE を利用する事ができます。REPLACE は、古い行を複製する固有キー値を含む新しい行の取り扱いの中では INSERT IGNORE と同等になります。新しい行は捨てられるのではなく、古い行を置き換えるのに利用されます。詳しくは REPLACE 構文」 を参照してください。
(引用元: MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.4 INSERT 構文

UPDATE

12.2.10. UPDATE 構文

単一テーブル構文には、UPDATE ステートメントは新しい値を利用して tbl_name 内に既存行のカラムを更新します。SET 条項は、どのカラムを変更し、それらにはどの値が与えられるべきかという事を指示します。もし WHERE 条項が与えられたら、それはどの行を更新するべきかを決定します。WHERE 条項が無ければ、全ての行が更新されます。もしORDER BY 条項が指定されると、指定された順に行が更新されます。LIMIT 条項は、更新できる行数に制限を設定します。

複合テーブル構文には、UPDATE が、条件を満たす table_references で名づけられたそれぞれのテーブルの行を更新します。この場合、ORDER BY と LIMIT を利用する事はできません。

where_condition は更新される各行に対して正しい結果の式です。それは SELECT 構文」 で述べられている通りに指定されます。

UPDATE ステートメントは次の修飾因子をサポートします。
(引用元: MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.10 UPDATE 構文

REPLACE

12.2.6. REPLACE 構文

REPLACE は、もしテーブル内の古い行が PRIMARY KEY か UNIQUE インデックスの新しい行と同じ値を持っていれば、古い行は新しい行が挿入される前に削除されるという事以外、INSERT と全く同じように機能します。詳しくは INSERT構文」 を参照してください。

REPLACE は SQL スタンダードの MySQL 拡張子です。それは挿入、または 削除 と挿入を行います。挿入、または 更新 — を行うスタンダード SQL — の別の MySQL 拡張子に関しては、 INSERT ... ON DUPLICATE KEY UPDATE 構文」 を参照してください。

テーブルが PRIMARY KEY か UNIQUE インデックスを持たなければ、REPLACE ステートメントの利用は何の意味も持たないという事を覚えておいてください。新しい行が別の行を複製するかどうかを決める為に利用するインデックスが無い為、それは INSERT と同等になります。

全てのカラムの値は、REPLACE ステートメントの中で指定された値から取られています。紛失したカラムは、INSERT と同じように、デフォルト値に設定されます。現在の行から値を参照し、それらを新しい行の中で利用する事はできません。もし、SET col_name = col_name + 1 のような割り当てを利用すると、右側のカラム名の参照は DEFAULT(col_name)として扱われるので、その割り当ては SET col_name = DEFAULT(col_name) + 1 と同等になります。

REPLACE を利用する為には、テーブルに対して INSERT と DELETE 権限の両方を持つ必要があります。

REPLACE ステートメントは、影響を受けた行数を表す為に総数を返します。これは、削除、挿入された行の総数です。もし単列 REPLACE の総数が1であれば、行が1つ挿入され、削除された行はないという事になります。 もし総数が1よりも大きければ、新しい行が挿入される前に、1つまたはそれ以上の行が削除されたという事になります。もしテーブルが複数の固有インデックスを含んでいれば、単列が複数の古い行を置き換える事が可能であり、そして新しい行は異なる固有のインデックス内の異なる古い行に値を複製します。

影響を受けた行の総数によって、REPLACE が行を追加しただけなのか、それとも行の置き換えも行ったのか、という事を簡単に知る事ができます。総数が1(追加された)か、それよりも大きい(置き換えが行われた)かを確認してください。

もしC API を利用していれば、mysql_affected_rows() 関数を利用する事で、影響を受けた行の総数を得る事ができます。

現在は、サブクエリの中で1つのテーブルに置き換え、同じテーブルから選択する事はできません。

MySQL は次のアルゴリズムを REPLACE (と LOAD DATA ... REPLACE)に利用します。
(引用元: MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.2.6 REPLACE 構文

各パターン別の出力結果とインポート結果

上記の条件で各ケースを出力した結果、以下のようにSQL文が生成されます。
今回はケースが多いため、インポート結果も併せて併記します。

取り込み対象について
[新規]

新規は空のデータベース(テーブルなどは存在・レコードなし)へインポートを行った結果です。
空のテーブルへの正常挿入の確認を行います。

実行前のテーブルデータ

02_取り込み対象[新規]データベースの処理前状態

取り込み対象[新規]データベースの処理前状態

[既存]

既存は出力元のデータベースから1レコード削除・1レコード値更新をして、インポートを行った結果です。
増分追加と既存データ更新の結果を確認を行います。

実行前のテーブルデータ

03_取り込み対象[既存]データベースの処理前状態

取り込み対象[既存]データベースの処理前状態

P01:INSERT
P01-01
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-01.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-01.sql)

04_P01-01(実行後)

P01-01(実行後)

P01-02
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-02.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-02.sql)

05_P01-02(実行後)

P01-02(実行後)

P01-03
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P01-03.sql)

既存データベースへ取り込み

INSERT INTO test ( col1 , col2 )VALUES ( 1, '2013-09-16' ) ;
#1062 - Duplicate entry '1' for key 'PRIMARY'

06_P01-03(実行後)

P01-03(実行後)

P01-04
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-04.sql)

既存データベースへ取り込み

INSERT INTO test VALUES
( 1, '2013-09-16' ) , ( 2, '2013-09-17' ) , ( 3, '2013-09-15' ) , ( 4, '2013-09-17' ) ;
#1062 - Duplicate entry '1' for key 'PRIMARY'

07_P01-04(実行後)

P01-04(実行後)

P01-05
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P01-05.sql)

既存データベースへ取り込み

INSERT INTO test ( col1 , col2 ) VALUES
( 1, '2013-09-16' ) , ( 2, '2013-09-17' ) , ( 3, '2013-09-15' ) , ( 4, '2013-09-17' ) ;
#1062 - Duplicate entry '1' for key 'PRIMARY'

08_P01-05(実行後)

P01-05(実行後)

P01-06
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
INSERT

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P01-06.sql)

既存データベースへ取り込み

INSERT INTO test VALUES ( 1, '2013-09-16' ) ;
#1062 - Duplicate entry '1' for key 'PRIMARY'

09_P01-06(実行後)

P01-06(実行後)

P02:UPDATE
P02-01
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-01.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-01.sql)

10_P02-01(実行後)

P02-01(実行後)

P02-02
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-02.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-02.sql)

11_P02-02(実行後)

P02-02(実行後) 

P02-03
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-03.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-03.sql)

12_P02-03(実行後)

P02-03(実行後) 

P02-04
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-04.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-04.sql)

13_P02-04(実行後)

P02-04(実行後) 

P02-05
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-05.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-05.sql)

14_P02-05(実行後)

P02-05(実行後) 

P02-06
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
UPDATE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-06.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P02-06.sql)

15_P02-06(実行後)

P02-06(実行後) 

P03:REPLACE
P03-01
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-01.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-01.sql)

16_P03-01(実行後)

P03-01(実行後)

P03-02
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-02.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-02.sql)

17_P03-02(実行後)

P03-02(実行後) 

P03-03
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P03-03.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P03-03.sql)

18_P03-03(実行後)

P03-03(実行後)

P03-04
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-04.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-04.sql)

19_P03-04(実行後)

P03-04(実行後) 

P03-05
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-05.sql)

既存データベースへ取り込み

インポートは正常終了しました。10 個のクエリを実行しました。 (P03-05.sql)

20_P03-05(実行後)

P03-05(実行後) 

P03-06
設定抜粋
構文 DELAYED IGNORE カラム名 複数行 両方 それ以外
REPLACE

新規データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P03-06.sql)

既存データベースへ取り込み

インポートは正常終了しました。13 個のクエリを実行しました。 (P03-06.sql)

21_P03-06(実行後)

P03-06(実行後)

各処理結果の一覧

各ケース別に処理を行った結果、以下のような結果が得られました。

各ケースのインポート結果
CASE 新規 既存 備考
処理 行数 挿入 更新 処理 行数 挿入 更新
P01-01 終了 4 終了 4 × 新規OK
P01-02 終了 4 終了 4 × 新規OK
P01-03 終了 4 エラー 2 × × 利用不可
P01-04 終了 4 エラー 2 × × 利用不可
P01-05 終了 4 エラー 2 × × 利用不可
P01-06 終了 4 エラー 2 × × 利用不可
P02-01 終了 0 × 終了 2 × 利用不可
P02-02 終了 0 × 終了 2 × 利用不可
P02-03 終了 0 × 終了 2 × 利用不可
P02-04 終了 0 × 終了 2 × 利用不可
P02-05 終了 0 × 終了 2 × 利用不可
P02-06 終了 0 × 終了 2 × 利用不可
P03-01 終了 4 終了 4 新規・既存OK
P03-02 終了 4 終了 4 新規・既存OK
P03-03 終了 4 終了 4 新規・既存OK
P03-04 終了 4 終了 4 新規・既存OK
P03-05 終了 4 終了 4 新規・既存OK
P03-06 終了 4 終了 4 新規・既存OK

INSERTではそれぞれデータの増分に対しての挿入は行われますが、
既存のレコードに対しての値の更新が行われません。

UPDATEでは既存データの更新は行われますが、
新たに追加された増分レコードに対しては、挿入が行われません。

結論として、既存のデータベースに対するデータの更新と、
新規データベースへのデータ挿入の両方に対応ができる出力方法はREPLACEによる出力のみです。

REPLACEは非常に便利な結果が得られることがわかります。

 

*本ページ内に記載されされた各社ブランド、サービス名、商標、登録商標については、
各社ブランドのロゴや商標等に関する帰属についてをご確認ください


最後までお読みいただきありがとうございました。

アイキャッチ画像に利用させて頂いております、各社ブランドのロゴやシンボルに関しては、
各社ブランドのロゴや商標等に関する帰属についてをご確認頂けますようお願い致します。

AdMax Promotion

ブログ記事のご紹介
すべて展開 | すべて省略

Rakuten Promotion

ページ
すべて展開 | すべて省略

プロモーション(btm)

Google or AdMax Promotion (it)

 - MySQL, phpMyAdmin ,

  関連記事

eye_phpmyadmin
phpMyAdminを利用したMySQLデータのエクスポート

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_php
PHP.iniに設定する設定をhttpd.confや.htaccessに対して行うメリット

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
レンタルサーバ環境へのphpMyAdmin設置

Google or AdMax Promotion(it) 禁断の機能がau公式 …

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

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_php
phpmyadminダウンロードが始まらない時の対処

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminを利用したMySQL同期とWM3500Rポート解放

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_ipv46.info250
GROUP_CONCATのgroup_concat_max_len()が有効にならない対処法

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_kagoya
KAGOYA™(カゴヤ)のMySQL単独プランの利用手順

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_phpmyadmin
phpMyAdminでMySQLエクスポートが途中で切れる場合の対応策

Google or AdMax Promotion(it) 禁断の機能がau公式 …

eye_php
ファイルアップロード上限の変更(PHP phpMyAdmin wordpress)

Google or AdMax Promotion(it) 禁断の機能がau公式 …