Mysql中自增字段（AUTO_INCREMENT)的一些常识

Mysql中自增字段（AUTO_INCREMENT)的一些常识

在系统开发过程中，我们经常要用到唯一编号。使用过mysql的人都应该知道，mysql有一个定义列为自增的属性：AUTO_INCREMENT。

指定了AUTO_INCREMENT的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（不一定要做主键）

mysql> create table t4 (id int auto_increment);ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql>

下面的定义把t5表的主键定义为了name，而非自增的id字段

mysql> mysql> create table t5 (id int auto_increment,name varchar(20) primary key,key(id)); Query OK, 0 rows affected (0.01 sec)

指定了auto_increment的列，在插入时：

1. 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。

2. 当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
   

   mysql> insert into t5 (id,name) values (null,'test');Query OK, 1 row affected (0.00 sec)    mysql> select * from t5;+----+------+| id | name |+----+------+|  2 | test |+----+------+1 row in set (0.00 sec)

3. 上面语句等同于下面语句：

   mysql> insert into t5 (name) values ('test');

4. 当插入记录时，如果为AUTO_INCREMENT字段明确指定了一个数值，则会出现两种情况：
   

      情况一，如果插入的值与已有的编号重复，则会出现出 错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；

      情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。

   ## 初始表mysql> show create table t2\G;*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8    ## 插入数据mysql> insert into t2 values (null),(null),(null);  Query OK, 3 rows affected (0.00 sec)    ## auto_increment变成4mysql> show create table t2\G;*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8    ## 插入7mysql> insert into t2 values (7);Query OK, 1 row affected (0.00 sec)    ## auto_increment变成8mysql> show create table t2\G;*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

   换句话说，就是自增字段可以跳过一些编号。

5. 对于MyISAM表，如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。但是对于innodb表，update auto_increment字段，会导致发生报错

   MyISAM表的update如下所示
   

   ## 当前状态mysql> show create table t2\G;*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set (0.00 sec)   ## 将id=7的数据update为10mysql> update t2 set id=10 where id=7;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0   ## 最新的auto_increment变为11mysql> show create table t2\G;*************************** 1. row ***************************       Table: t2Create Table: CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf81 row in set (0.00 sec)

   Innodb表的update操作如下所示

   （可以看到在update前后，表定义语句没有变化），接着执行insert会导致主键错误！

   mysql> show create table t3\G;*************************** 1. row ***************************       Table: t3Create Table: CREATE TABLE `t3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set (0.00 sec)   ## updae更新操作mysql> update t3 set id=10 where id=7;Query OK, 1 row affected (0.27 sec)Rows matched: 1  Changed: 1  Warnings: 0   mysql> show create table t3\G;*************************** 1. row ***************************       Table: t3Create Table: CREATE TABLE `t3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set (0.00 sec)

   Innodb表继续插入会导致报错，但是只会报错一次，跳过10之后会正常插入

   mysql> insert into t3 values (null);Query OK, 1 row affected (0.46 sec)   mysql> insert into t3 values (null);Query OK, 1 row affected (0.11 sec)   mysql> insert into t3 values (null);ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'

   
   

6. 被delete语句删除的id值，除非sql中将id重新插入，否则前面空余的id不会复用。

7. delete from t3该语句不会引起auto_increment的变化，

   mysql> delete from t3;Query OK, 8 rows affected (0.34 sec)  mysql> show create table t3\G;*************************** 1. row ***************************       Table: t3Create Table: CREATE TABLE `t3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf81 row in set (0.00 sec)

   
   

   truncate table t3 该语句会引起auto_increment的变化，从头开始。

   mysql> truncate table t3;Query OK, 0 rows affected (0.53 sec)  mysql> show create table t3\G;*************************** 1. row ***************************       Table: t3Create Table: CREATE TABLE `t3` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

   
   

8. last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。
   

修改AUTO_INCREMENT字段的起始值

   可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。

   但是如果设置的n比目前的数值小的话，执行的sql不会报错，但是不会生效！MyISAM和Innodb均是如此。

mysql> show create table t2;+-------+----------------------- CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)  mysql> mysql> alter table t2 auto_increment=2;Query OK, 6 rows affected (0.04 sec)Records: 6  Duplicates: 0  Warnings: 0  mysql> show create table t2;+-------+-------------------- CREATE TABLE `t2` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

auto_increment_increment & auto_increment_offset 两个变量的介绍

这两个参数作用：控制自增列AUTO_INCREMENT的行为，用于MASTER-MASTER之间的复制，防止出现重复值。

两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。如果试图将这些变量设置为大于65,535或小于0的值，则会将该值设置为65,535。如果向将auto_increment_increment或auto_increment_offset设置为非整数值，则会给出错误，并且变量的实际值在这种情况下保持不变。

两个值的含义：

auto_increment_increment：自增值的自增量

auto_increment_offset： 自增值的偏移量

设置了两个值之后，改服务器的自增字段值限定为：

auto_increment_offset + auto_increment_increment*N  的值，其中N>=0，但是上限还是要受定义字段的类型限制。

比如：

auto_increment_offset=1

auto_increment_increment=2

那么ID则是所有的奇数[1,3,5,7，.....]

如果：

auto_increment_offset=5

auto_increment_increment=10

那么ID则是所有的奇数[5,15,25,35，.....]

查看当前值：

mysql> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+

配置auto-increment-increment&auto-increment-offset的值：

（1）：修改配置文件，重启mysqld

   vi my.cnf

   将

   auto-increment-increment = 2

   auto-increment-offset = 2

   加入到mysqld相关的配置中

（2）：通过set命令修改，不需要重启mysqld，一般需要用set global来设置

set global auto_increment_increment=2;set global auto_increment_offset=2;

   注意：在一个会话中，如果用set global 修改了mysql的某个变量值，如果不退出session，重新连接，你用show variables 看到的还是修改之前的值，因为show variables 默认返回的是当前session的值，最好用show session variables  和 show global variables 来查看对应的变量值。

   下面是个例子：

mysql> set global auto_increment_increment=2;Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec) mysql> show session variables like '%auto_increment%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec) mysql> show global variables like '%auto_increment%';       +--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 2     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec)

当然也可以只设定当前session有效

set session auto_increment_increment=2;set session auto_increment_offset=2;

具体的例子：

auto_increment_increment=2

auto_increment_offset=1

mysql> truncate t2;Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> set session auto_increment_increment=2;Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_offset=1;Query OK, 0 rows affected (0.00 sec) mysql> show session variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 2     || auto_increment_offset    | 1     |+--------------------------+-------+2 rows in set (0.00 sec) mysql>  insert into t2 values (null),(null),(null),(null),(null),(null);Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0 mysql> select * from t2;+----+| id |+----+|  1 ||  3 ||  5 ||  7 ||  9 || 11 |+----+6 rows in set (0.00 sec)

auto_increment_increment=2

auto_increment_offset=2

mysql> truncate t2;Query OK, 0 rows affected (0.00 sec) mysql> mysql> set session auto_increment_increment=2;Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_offset=2;Query OK, 0 rows affected (0.00 sec) mysql> show session variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 2     || auto_increment_offset    | 2     |+--------------------------+-------+2 rows in set (0.00 sec) mysql>  insert into t2 values (null),(null),(null),(null),(null),(null);Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0 mysql> select * from t2;+----+| id |+----+|  2 ||  4 ||  6 ||  8 || 10 || 12 |+----+6 rows in set (0.00 sec)

auto_increment_increment=10

auto_increment_offset=5

mysql> truncate t2;Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_increment=10;Query OK, 0 rows affected (0.00 sec) mysql>  set session auto_increment_offset=5;Query OK, 0 rows affected (0.00 sec) mysql> show session variables like '%auto_incre%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 10    || auto_increment_offset    | 5     |+--------------------------+-------+2 rows in set (0.00 sec) mysql> insert into t2 values (null),(null),(null),(null),(null),(null);Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0 mysql> select * from t2;+----+| id |+----+|  5 || 15 || 25 || 35 || 45 || 55 |+----+6 rows in set (0.00 sec)

一个很重要的问题：如果在原有的序列中强制插入一个值，比如上面的例子，下一个数据我插入57，那再往后生成的值会受前面插入数据的影响吗？

答案是： 不会的！！

mysql> insert into t2 values (57),(58);Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0 mysql> select * from t2;+----+| id |+----+|  5 || 15 || 25 || 35 || 45 || 55 || 57 || 58 |+----+8 rows in set (0.00 sec) mysql> insert into t2 values (null),(null),(null);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0 mysql> select * from t2;+----+| id |+----+|  5 || 15 || 25 || 35 || 45 || 55 || 57 || 58 || 65 || 75 || 85 |+----+11 rows in set (0.00 sec)