AUTO_INCREMENTは最大値以下には設定できないらしい

メモ

とある特別なデータを普通なら到達できないキリのいいidに設定しつつ、AUTO_INCREMENTを維持しつつけようとするこそくな手段は潰えました。。。というお話。

テーブルを作って適当に値を入れてみる。

[root@localhost] test> CREATE TABLE test1 ( id INT UNSIGNETD AUTO_INCREMENT PRIMARY KEY, c1 INT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

[root@localhost] test> show CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] test> INSERT test1 set c1=1;
Query OK, 1 row affected (0.05 sec)

[root@localhost] test> select * from test1;
+----+----+
| id | c1 |
+----+----+
|  1 |  1 |
+----+----+
1 row in set (0.00 sec)

[root@localhost] test> show CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


[root@localhost] test> INSERT test1 (c1) VALUES (2),(3),(4);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

[root@localhost] test> select * from test1;
+----+----+
| id | c1 |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
+----+----+
4 rows in set (0.00 sec)

[root@localhost] test> show CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

id=10のカラムを追加してみると、AUTO_INCREMENT=11となる。

[root@localhost] test> INSERT test1 (id,c1) VALUES (10,999);
Query OK, 1 row affected (0.02 sec)

[root@localhost] test> select * from test1;
+----+-----+
| id | c1  |
+----+-----+
|  1 |   1 |
|  2 |   2 |
|  3 |   3 |
|  4 |   4 |
| 10 | 999 |
+----+-----+
5 rows in set (0.00 sec)

[root@localhost] test> SHOW CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

この状態でAUTO_INCREMENT=5にしてみようとしてみても、AUTO_INCREMENT=11のまま。

[root@localhost] test> alter table test1 AUTO_INCREMENT=5;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

[root@localhost] test> SHOW CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

idの最大値より大きい場合はいくらでも変更可能らしい。

[root@localhost] test> alter table test1 AUTO_INCREMENT=12;
Query OK, 5 rows affected (0.02 sec)
Records: 5  Duplicates: 0  Warnings: 0

[root@localhost] test> SHOW CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] test> alter table test1 AUTO_INCREMENT=11;
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

[root@localhost] test> SHOW CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[root@localhost] test> alter table test1 AUTO_INCREMENT=9999999;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

[root@localhost] test> SHOW CREATE TABLE test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9999999 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

SEE ALSO

• 8.1.7. ALTER TABLE 構文
  • http://dev.mysql.com/doc/refman/5.1-olh/ja/alter-table.html

によると、こういうことらしいです。

MyISAM では、AUTO_INCREMENT カラム内の値が現在の最高値と同じかそれ以下の値なら、その値は現在の最高値プラス 1 にリセットされます。InnoDB では、この値がカラム内の現在の最大値より小さい場合、エラーは発生せず、現在のシーケンス値は変更されません。

ただ、InnoDBの場合は、この文の理解ではAUTO_INCREMENT=9999999のまま変わらないはずではと思いつつ。ふむー。

[root@localhost] test> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9999999 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] test> alter table test1 AUTO_INCREMENT=9;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0

[root@localhost] test> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

とりあえず、不可能なことが分かったので満足です。

ちなみにバージョンはこんな感じ。

% mysqld --version
mysqld  Ver 5.1.37-2~bpo50+1-log for debian-linux-gnu on i486 ((Debian))