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

2013/10/20 2014/11/22

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

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

はじめに

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

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

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

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

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

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

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

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

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

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

エクスポート元データ

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

ON

CREATE TABLE IF NOT EXISTS `test` (
  `key` int(10) unsigned NOT NULL DEFAULT '0',
  `value` int(20) unsigned DEFAULT NULL,
  `autoinc` int(100) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`key`),
  UNIQUE KEY `autoinc` (`autoinc`)
) ENGINE=InnoDB  DEFAULT CHARSET=armscii8 AUTO_INCREMENT=16 ;

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES
(101, 1, 1),
(102, 2, 2),
(103, 3, 3),
(104, 4, 4),
(105, 5, 5),
(106, NULL, 6),
(107, NULL, 7),
(108, NULL, 8),
(109, NULL, 9),
(110, NULL, 10),
(111, 11, 11),
(112, 12, 12),
(114, 14, 14);

CREATE TABLE IF NOT EXISTS `test` (

`key` int(10) unsigned NOT NULL DEFAULT '0',

`value` int(20) unsigned DEFAULT NULL,

`autoinc` int(100) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`key`),

UNIQUE KEY `autoinc` (`autoinc`)

) ENGINE=InnoDB DEFAULT CHARSET=armscii8 AUTO_INCREMENT=16 ;

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES

(101, 1, 1),

(102, 2, 2),

(103, 3, 3),

(104, 4, 4),

(105, 5, 5),

(106, NULL, 6),

(107, NULL, 7),

(108, NULL, 8),

(109, NULL, 9),

(110, NULL, 10),

(111, 11, 11),

(112, 12, 12),

(114, 14, 14);

OFF

CREATE TABLE IF NOT EXISTS `test` (
  `key` int(10) unsigned NOT NULL DEFAULT '0',
  `value` int(20) unsigned DEFAULT NULL,
  `autoinc` int(100) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`key`),
  UNIQUE KEY `autoinc` (`autoinc`)
) ENGINE=InnoDB  DEFAULT CHARSET=armscii8;

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES
(101, 1, 1),
(102, 2, 2),
(103, 3, 3),
(104, 4, 4),
(105, 5, 5),
(106, NULL, 6),
(107, NULL, 7),
(108, NULL, 8),
(109, NULL, 9),
(110, NULL, 10),
(111, 11, 11),
(112, 12, 12),
(114, 14, 14);

CREATE TABLE IF NOT EXISTS `test` (

`key` int(10) unsigned NOT NULL DEFAULT '0',

`value` int(20) unsigned DEFAULT NULL,

`autoinc` int(100) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`key`),

UNIQUE KEY `autoinc` (`autoinc`)

) ENGINE=InnoDB DEFAULT CHARSET=armscii8;

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES

(101, 1, 1),

(102, 2, 2),

(103, 3, 3),

(104, 4, 4),

(105, 5, 5),

(106, NULL, 6),

(107, NULL, 7),

(108, NULL, 8),

(109, NULL, 9),

(110, NULL, 10),

(111, 11, 11),

(112, 12, 12),

(114, 14, 14);

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

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

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

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

AUTO_INCREMENTがON

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

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

AUTO_INCREMENTがOFF

wordpressバックアップ利用時

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

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

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

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

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

インポートのオプション

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

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

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES
(300, 30, NULL),
(301, 31, NULL),
(302, 32, NULL),
(303, 33, 0),
(304, 34, NULL),
(305, 35, NULL);

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES

(300, 30, NULL),

(301, 31, NULL),

(302, 32, NULL),

(303, 33, 0),

(304, 34, NULL),

(305, 35, NULL);

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

ON

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

値ゼロで使用しないがON

OFF

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

値ゼロで使用しないがOFF

wordpressバックアップ利用時

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

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

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

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

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

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

REPLACE INTO `test` (`key`, `value`, `autoinc`) VALUES
(300, 30, NULL),
(301, 31, NULL),
(302, 32, NULL),
(NULL, 33, NULL),
(304, 34, NULL),
(305, 35, NULL);