AUTO_INCREMENTで任意の値から開始する方法

[test]> desc t2;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| a     | int(11) | NO   | PRI | NULL    | auto_increment |
| b     | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

[test]> select max(a) from t2;
+----------+
| max(a)   |
+----------+
| 16979416 |
+----------+
1 row in set (0.09 sec)

現在の状態

[test]> alter table t2 auto_increment=20000000;
Query OK, 16979416 rows affected (1 min 26.51 sec)
Records: 16979416  Duplicates: 0  Warnings: 0

AUTO_INCREMENTで開始したい値を設定する（若干時間がかかった）

[test]> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=20000000 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

設定されたAUTO_INCREMENT値を確認する

[test]> select max(a) from t2;
+----------+
| max(a)   |
+----------+
| 16979416 |
+----------+
1 row in set (0.00 sec)

この状態ではMAX値は変わっていない

[test]> insert into t2(a) values(null);
Query OK, 1 row affected (0.29 sec)

一行挿入

[test]> select max(a) from t2;
+----------+
| max(a)   |
+----------+
| 20000000 |
+----------+
1 row in set (0.00 sec)

MAX値も新しい値となった

[test]> show create table t2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

設定したAUTO_INCREMENT値の確認