note︰mysql auto increment reset

CREATE TABLE `userdata` (
  `id` int(4) unsigned NOT NULL auto_increment,
  `myname` char(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12345 DEFAULT CHARSET=utf8;

新增資料時，id 會從 12345 開始算︰

mysql> insert into `userdata` value (0,'name1');
Query OK, 1 row affected (0.04 sec)

mysql> select * from `userdata`;

+-------+--------+
| id    | myname |
+-------+--------+
| 12345 | name1  |
+-------+--------+
1 row in set (0.00 sec)

既有的 table 要改 Auto_Increment 的值，可以如下︰
但這招執行過程會鎖住整個 table，若是大型的 table 要特別注意
比較適用於 table 清空時要重設 Auto_Increment 的值 由 1 開始

** InnoDB 格式 不行重設 auto_increment 值

mysql> ALTER TABLE `userdata` AUTO_INCREMENT=20000;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into `userdata` value (0,'name2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from `userdata`;
+-------+--------+
| id    | myname |
+-------+--------+
| 12345 | name1  |
| 20000 | name2  |
+-------+--------+
2 rows in set (0.00 sec)

還有兩個相關的變數︰
SET @@auto_increment_increment=1;
SET @@auto_increment_offset=1;
主要用於 master to master 的複製 replication ，限制為 0 ~ 65,535

參考︰ http://dev.mysql.com/doc/refman/5.1/en/replication-options-master.html#sysvar_auto_increment_increment

---

另外，auto_increment 欄位爆掉時會發生什麼事？

mysql> insert into `userdata` value (4294967294,'nameA');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `userdata` value (0,'nameB');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `userdata` value (0,'nameC');
ERROR 1062 (23000): Duplicate entry '4294967295' for key 1
mysql>

另一招是
修改插入資料時的 auto_increment 欄位值
例如︰

mysql> SET insert_id = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `userdata` value (0,'name99');
Query OK, 1 row affected (0.00 sec)

mysql> select * from `userdata`;
+------------+--------+
| id         | myname |
+------------+--------+
| 4294967294 | nameA  |
| 4294967295 | nameB  |
|       1000 | name99 |
+------------+--------+
3 rows in set (0.00 sec)

取得某 table 下個 auto_increment 欄位值︰

// You have to connect to MySQL and select a database before you can do this
$table_name = "myTable";
$query = mysql_query("SHOW TABLE STATUS WHERE name='$table_name'");
$row = mysql_fetch_array($query);
$next_inc_value = $row["AUTO_INCREMENT"]; 

ref: http://x10hosting.com/forums/tutorials/111106-php-mysql-how-get-next-auto-increment-value.html