mysql 中auto_mysql中的auto_increment

在构建数据库应用时，经常会遇到这样的情景：我们需要一个唯一的整数标号(id)来标识一条记录，但显然我们不想在插入一条记录之前还要先遍历一次整个表，然后确定一个合适的值来做为这条要插入记录的唯一标号，因为这会大大的影响效率，幸运的是mysql本身就提供了一种叫做自增列的东东，它是由关键字auto_increment来标识的(在ms的sql server中标记为identity的字段为自增列)。简单地说，auto_increment就是一个计数器，它决定了数据表中即将插入的下一条记录的自增字段的值。

如果一个数据表中有自增列，mysql就为这个表维护了一个计数器auto_increment，这个计数器是一直存在于内存中的，当数据库启动的时候会重新初始化这个计数器，默认情况下它的值会被初始化为表中自增列的当前最大数再加1，对auto_increment计数器的访问和操作是由专门的表级锁来控制的，从而防止多线程导致的竞争问题。auto_increment值的确定是由mysql中的两个全局变量auto_increment_increment和auto_increment_offset来确定的，它们的默认值都是1，在mysql中可以通过show variables命令来查看它们的当前值。可以在my.cnf文件中修改它们的值，也可以直接通过命令set auto_increment_increment/offset来进行修改。

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> SET auto_increment_increment=5;

Query OK, 0 rows affected (0.00 sec)

mysql> SET auto_increment_offset=2;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_increment_%';

+--------------------------+-------+

| Variable_name            | Value |

+--------------------------+-------+

| auto_increment_increment | 5     |

| auto_increment_offset    | 2     |

+--------------------------+-------+

2 rows in set (0.00 sec)

auto_increment的取值是由auto_increment_offset+N*auto_increment_increment来决定的，其中N的取值为0,1,2...以auto_increment_offset=2，auto_increment_increment=5为例，插入后的记录自增列的值如下

mysql> create table test(id int primary key auto_increment);

Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values(null);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null);

Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

+----+

| id |

+----+

| 2 |

| 7 |

| 12 |

| 17 |

| 22 |

+----+

5 rows in set (0.00 sec)

要注意auto_increment_increment/offset都是全局的变量，修改后会影响到其它表和用户的操作，需要慎重修改，所以接下来将这两个值改回默认值1。

在mysql中通过show create table table_name可以查看当前表中auto_increment计数器的值。

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> SET auto_increment_offset=1;

Query OK, 0 rows affected (0.00 sec)

mysql> SET auto_increment_increment=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

可以看出如果我们不去手动修改的话，auto_increment计数器的当前值是表中已存在的所有记录(包括被删除的)的自增列的最大值再加1，其实并不符合上面的计算规则，但在新记录被插入的时候mysql确实会根据上面的规则计算出下一个插入记录的id值。

如果往自增列中写null或0值它会被由mysql根据当前auto_increment计数器计数规则计算出的值所取代，但如果你指定了一个特定的正整数的值(假定为i)则会发生如下几种情况:

1)i正好和已有的记录中的值相同，mysql会报错

2)i小于auto_increment计数器的当前值并且这个值不和已有记录中的值重复，这条新记录的自增列的值i，auto_increment计数器的值不变

2)i大于auto_increment计数器的当前值，这条新记录的自增列的值为i，同时auto_increment计数器的值变为i+1

mysql> insert into test values(7);

ERROR 1062 (23000): Duplicate entry '7' for key 'PRIMARY'

mysql> insert into test values(8);

Query OK, 1 row affected (0.00 sec)

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> insert into test values(25);

Query OK, 1 row affected (0.00 sec)

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

在使用mysql的auto_increment时，有时会碰到auto_increment计数器的值远远大于数据表中自增列的最大值(这种情况出现的原因很多，比如我们错误地插入了一个有很大id值的记录但后面又将这条记录给删除了)，这时我们需要修改auto_increment计数器的值，除了重启数据库让mysql自动初始化这个计数器外，我们还能通过alter table table_name auto_increment=X;命令来修改计数器的值，这时同样会导致两种情况：

1)如果x的值比数据表中自增列的当前最大值还要大，auto_increment计数器的值会变为x

2)如果x的值不大于数据表中自增列的当前最大值，auto_increment计数器的值会变为自增列中当前最大值加1，这与重启数据库初始化计数器的值一样

mysql> alter table test auto_increment=10;

Query OK, 7 rows affected (0.05 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table test auto_increment=30;

Query OK, 7 rows affected (0.27 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> show create table test;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

| test | CREATE TABLE `test` (

`id` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)