MySQL AUTO_INCREMENT

来源：互联网发布：如何加入淘宝有好货编辑：程序博客网时间：2024/05/21 19:44
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有主键插入值重复的错误出现。
0 0