MySQL AUTO_INCREMENT

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;


mysql> insert into t values();
Query OK, 1 row affected (0.06 sec)

mysql> insert into t values();
Query OK, 1 row affected (0.06 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


-- 从上面测试可以看AUTO_INCREMENT起作用了，并且show create table 中AUTO_INCREMENT看到的是下一个自增值3。




mysql> insert into t values(5);
Query OK, 1 row affected (0.05 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
+----+
3 rows in set (0.00 sec)


mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

--从上面测试看，show create table 中AUTO_INCREMENT看到的是下一个自增值是6而不是3。


mysql> insert into t values();
Query OK, 1 row affected (0.04 sec)

mysql> select * from t;
+----+
| id |
+----+
|  1 |
|  2 |
|  5 |
|  6 |
+----+
4 rows in set (0.00 sec)

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- 从上面测试看，当插入5后，再机器使用AUTO_INCREMENT自增时，是插入的6而不是3。
-- 也就意味着每次使用AUTO_INCREMENT插入数据时数据库是保证比这个列当前最大值大的，就不用担心自己插入数据后会再次使用AUTO_INCREMENT有主键插入值重复的错误出现。