MyISAMのAUTO_INCREMENT修正のmyisamchkとALTER TABLEについて

知る必要があったので、調べてみました。

環境

MySQL Version

4.1.22

myisamchk で修正する場合

http://dev.mysql.com/doc/refman/4.1/ja/myisam.html

1 つの AUTO_INCREMENT カラムを内部処理している。MyISAM では、このカラムが INSERT/UPDATE で自動更新される。AUTO_INCREMENT の値は、myisamchk でリセットできる。これによって AUTO_INCREMENT カラムの処理が速くなる（最低でも 10%）。また、以前の ISAM のように古い番号が再使用されない。マルチパートキーの最後の項目に AUTO_INCREMENT が定義されている場合は、以前の動作が引き続き有効となることに注意する。

以下、実験。

create database hoge;
use hoge

CREATE TABLE `fugas` (
      `id` TINYINT AUTO_INCREMENT,
      `name` varchar(255),
      PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO fugas (name) VALUE ("a");
INSERT INTO fugas (name) VALUE ("a");
INSERT INTO fugas (name) VALUE ("a");

select * from fugas;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
+----+------+
3 rows in set (0.00 sec)

いったん、MySQLから抜けて、mysql を停止して、myisamchk します。

% sudo myisamchk -A10 /var/lib/mysql/hoge/fugas.MYI
Updating MyISAM file: /var/lib/mysql/hoge/fugas.MYI
% sudo /etc/init.d/mysql restart

MySQLにログインして、INSERTします。

use hoge

INSERT INTO fugas (name) VALUE ("a");

select * from fugas;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
| 11 | a    |
+----+------+
4 rows in set (0.00 sec)

もう一度、MySQLから抜けて、mysql を停止して、myisamchk します。

% sudo myisamchk -A5 /var/lib/mysql/hoge/fugas.MYI
Updating MyISAM file: /var/lib/mysql/hoge/fugas.MYI
% sudo /etc/init.d/mysql restart

MySQLにログインして再びINSERTします。

use hoge

INSERT INTO fugas (name) VALUE ("a");

select * from fugas;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
| 11 | a    |
| 12 | a    |
+----+------+
5 rows in set (0.00 sec)

以下のマニュアル通りですね。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.6.3.4 その他の myisamchk オプション

AUTO_INCREMENT ナンバリングを強制して新しい行がある値で始まるようにします(あるいは、存在する行の AUTO_INCREMENT 値が大きい場合、さらに大きい値で始まるようにします。)もし値が特定されていない場合、新しい行の AUTO_INCREMENT 数字は現在テーブル内の最も高い値＋１になります。

ALTER TABLEで修正する場合

create database hoge;
use hoge

CREATE TABLE `fugas` (
      `id` TINYINT AUTO_INCREMENT,
      `name` varchar(255),
      PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO fugas (name) VALUE ("a");
INSERT INTO fugas (name) VALUE ("a");
INSERT INTO fugas (name) VALUE ("a");

ALTER TABLE fugas AUTO_INCREMENT = 10;

INSERT INTO fugas (name) VALUE ("a");

select * from fugas;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
| 10 | a    |
+----+------+
5 rows in set (0.00 sec)

ALTER TABLE fugas AUTO_INCREMENT = 6;

select * from fugas;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
| 10 | a    |
| 11 | a    |
+----+------+
6 rows in set (0.00 sec)

ALTERの場合は、設定値と同じ値からスタートしますね。

【おまけ】数値カラムが限界値に達して AUTO_INCREMENT したらどうなるのか

http://dev.mysql.com/doc/refman/4.1/ja/myisam.htmlには

数値型のカラムに、そのカラム型で許容されている範囲を超える値を格納しようとすると、MySQL では、値は許容範囲の最大値または最低値に丸められて格納されます。

とあるため、AUTO_INCREMENT を重ねていくと、値は許容範囲を超え、最大値がダブルはずです。以下、実験。

create database hoge;
use hoge

CREATE TABLE `fugas` (
      `id` TINYINT AUTO_INCREMENT,
      `name` varchar(255),
      PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO fugas (name) VALUE ("a");
・
・
・
INSERTを128回
・
・
・
INSERT INTO fugas (name) VALUE ("a");
ERROR 1062 (23000): Duplicate entry '127' for key 1

SELECT * FROM fugas;
・
・
・
| 126 | a    |
| 127 | a    |
+-----+------+
127 rows in set (0.00 sec)

128回目のINSERTで Duplicate entry ERROR が出て INSERTできませんでした。