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

phpMyAdminを利用したMySQLデータのAuto Increments Option動作比較

      2014/11/22

Google or AdMax Promotion(it)

MySQLをphpMyAdminで利用していて、
AUTO_INCREMENTに関するオプションの利用法について、
疑問に思ったことはありませんか?

なんとなくONにして利用しているけど、それでいいの?
そんな疑問を解決しておきましょう。
オプションのON/OFFでどのような動作の違いがあるのかをご紹介しています。

はじめに

AUTO_INCREMENTオプションといえば、 テーブルに自動で採番してくれる便利な機能ですね。
しかし、この自動採番機能がエクスポートやインポートで、
どのように影響するかを考えたことはありますか?

同じようなAUTO_INCREMENTに関係するような機能で、
phpMyAdminからのエクスポート時の「AUTO_INCREMENT」チェックボックス機能と、
インポート時の「値がゼロのものに対して AUTO_INCREMENTを使用しない」の機能があります。
それぞれの機能と動作についてご紹介します。

エクスポート:生成オプション

phpMyAdminを利用してエクスポートを行う際に、
生成オプションには「AUTO_INCREMENT」チェックボックスが表示されています。

01_エクスポートの生成オプション

エクスポートの生成オプション

このチェックボックスをONにした場合には、
エクスポートの際に生成されるSQL文に対して、
AUTO_INCREMENTの最大値を含んだSQL文が生成されるようになります。

例として、以下のようなテーブルデータをそれぞれのオプションで、
エクスポートした結果を例にご紹介します。

このテーブルは以下のようなテーブル構造で3カラムを持っています。
3カラム目にはAUTO_INCREMENTが有効な列になっています。

順にレコードを追加し、13番目と15番目のレコードは削除しています。

その為、AUTO_INCREMENTの次の値は16となっています。

02_エクスポート元データ

エクスポート元データ

このテーブルをエクスポートオプションのON/OFFで比較すると、 以下のような違いで出力されます。

ON

OFF

このオプションは、
インポートを行った際のインポート先テーブルで新規にデータを追加した際に、
のAUTO_INCREMENT開始番号をエクスポート元の値で制御することができます。

実際にエクスポートしたSQLをインポートしたのちに、
1レコード追加した結果は以下のようになります。

ONデータのインポート結果+1レコード追加

エクスポート元のAUTO_INCREMENTのNEXT値が引き継がれ、
インポート後のテーブルに対してレコードを追加した際に、
削除されて欠番となっている15が利用されずレコードが追加されます。

03_AUTO_INCREMENTがON

AUTO_INCREMENTがON

OFFデータのインポート結果+1レコード追加

エクスポート元のAUTO_INCREMENTのNEXT値が引き継がれず、
インポート後のテーブルに対してレコードを追加した際に、
削除されて欠番となっている15が再利用され追加されます。

04_AUTO_INCREMENTがOFF

AUTO_INCREMENTがOFF

wordpressバックアップ利用時

このAUTO_INCREMENTのオプション値に関して、
wordpressのバックアップなどを行う場合には、基本的にONとして利用した方がいいでしょう。

wordpressのテーブルではAUTO_INCREMENTが有効になっているカラムが多くあり、
投稿のIDなどに利用されています。

このIDがテーブル間でずれてしまうなどの不慮の状態を作り出さないためにも、
AUTO_INCREMENTオプションはONで利用した方がいいでしょう。

インポート:フォーマット特有オプション

phpMyAdminを利用してSQLフォーマットのインポートを行う際に、
フォーマット特有オプションには、
「値がゼロのものに対して AUTO_INCREMENTを使用しない」チェックボックスが表示されています。

05_インポートのオプション

インポートのオプション

このオプションはエクスポートされたテーブルをインポートするような場合には、
特に問題になることもなく、ONでもOFFでもインポートが行われるでしょう。

このオプションの有効無効の違いが出るのは、
先ほどのテーブルに対して、以下のようなSQL文を実行するとわかります。

このSQL文では、5行目のautoinc(カラム3) の値を0としています。

ON

このSQLを「値がゼロのものに対して AUTO_INCREMENTを使用しない」をONとして、
SQLをインポートすると、以下のように値の0が有効となって取り込まれます。

06_値ゼロで使用しないがON

値ゼロで使用しないがON

OFF

このSQLを「値がゼロのものに対して AUTO_INCREMENTを使用しない」をOFFとして、
SQLをインポートすると、以下のように値の0が無視されインクリメントされて取り込まれます。

07_値ゼロで使用しないがOFF

値ゼロで使用しないがOFF

wordpressバックアップ利用時

通常、このような不正なデータを含むテーブルがエクスポートされることはないと思います。
その為、あまり意識しなくてもインポートには影響がないはずです。

こうした動作をさせることができるという点を覚えておくくらいでいいのではないかと思います。

ただ、AUTO_INCREMENTを特に意識せずに利用した場合には、
最小値が1から採番されていきます。
AUTO_INCREMENTの有効なカラムに対して値0を設定し、
特別なレコードを生成している場合などには、
このオプションを有効にしてインポートしなければ、0の値は設定できないことになります。

こうした用途では必ずONにする必要があると言えるかもしれません。

「値がゼロのものに対して AUTO_INCREMENTを使用しない」の不思議な動作

先ほどのこのオプションですが、実行するSQL文を以下のように変更すると、
少し不思議な動作をします。

このSQL文では、5行目のKey(カラム1) の値をNULLとしています。

ON

このSQLを「値がゼロのものに対して AUTO_INCREMENTを使用しない」をONとして、
SQLをインポートすると、エラーとなるはずのレコードが、
NULLを0としてインポートされ処理が行われます。

この0という値はデフォルト値が設定されているのですが、
このオプションを有効にしているとデフォルト値が採用され取り込まれます。

08_値ゼロで使用しないがON(キー無効)

値ゼロで使用しないがON(キー無効)

OFF

このSQLを「値がゼロのものに対して AUTO_INCREMENTを使用しない」をOFFとして、
SQLをインポートすると、正しくエラーとしてインポートが行われません。
この場合は、カラムのデフォルト値は採用されません。

#1048 – Column ‘key’ cannot be null

09_値ゼロで使用しないがOFF(キー無効)

値ゼロで使用しないがOFF(キー無効)

少し、紛らわしいですがこのオプションがONの場合には、
想定していない動作をしてインポートが行われてしまうという危険性が伴います。

特に意図している用途がない場合には、
通常はOFFとして取り込みを行う方が無難だと思います。

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


最後までお読みいただきありがとうございました。
アルゴリズン


役に立ったと思っていただけたら、以下からシェア頂けたら幸いです^^

QRコードからもこのURLを開けます。

Pitta Promotion

アイキャッチ画像に利用させて頂いております、各社ブランドのロゴやシンボルに関しては、
各社ブランドのロゴや商標等に関する帰属についてをご確認頂けますようお願い致します。
ロゴの使用等に関する苦情・ご意見等がございましたら管理者までご連絡ください。

AdMax Promotion

カテゴリ
すべて展開 | すべて省略

Rakuten Promotion

プロモーション(btm)

Google or AdMax Promotion (it)

 - MySQL, phpMyAdmin , ,

  関連記事