在系统开发过程中，我们经常要用到唯一编号。使用过mysql的人都应该知道，mysql有一个定义列为自增的属性：AUTO_INCREMENT。
指定了AUTO_INCREMENT的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（不一定要做主键）

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

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

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

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

  1. 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并1为基数递增。
  2. 当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。 ```sql 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)

  1. 3 上面语句等同于下面语句：`mysql> insert into t5 (name) values ('test');`<br />4 当插入记录时，如果为AUTO_INCREMENT字段明确指定了一个数值，则会出现两种情况：   情况一，如果插入的值与已有的编号重复，则会出现出 错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；   情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。<br />换句话说，就是自增字段可以跳过一些编号。
  2. ```sql
  3. ## 初始表
  4. mysql> show create table t2\G;
  5. *************************** 1. row ***************************
  6.        Table: t2
  7. Create Table: CREATE TABLE `t2` (
  8.   `id` int(11) NOT NULL AUTO_INCREMENT,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  12. ## 插入数据
  13. mysql> insert into t2 values (null),(null),(null);  
  14. Query OK, 3 rows affected (0.00 sec)
  16. ## auto_increment变成4
  17. mysql> show create table t2\G;
  18. *************************** 1. row ***************************
  19.        Table: t2
  20. Create Table: CREATE TABLE `t2` (
  21.   `id` int(11) NOT NULL AUTO_INCREMENT,
  22.   PRIMARY KEY (`id`)
  23. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  25. ## 插入7
  26. mysql> insert into t2 values (7);
  27. Query OK, 1 row affected (0.00 sec)
  29. ## auto_increment变成8
  30. mysql> show create table t2\G;
  31. *************************** 1. row ***************************
  32.        Table: t2
  33. Create Table: CREATE TABLE `t2` (
  34.   `id` int(11) NOT NULL AUTO_INCREMENT,
  35.   PRIMARY KEY (`id`)
  36. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

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

  1. ## 当前状态
  2. mysql> show create table t2\G;
  3. *************************** 1. row ***************************
  4.        Table: t2
  5. Create Table: CREATE TABLE `t2` (
  6.   `id` int(11) NOT NULL AUTO_INCREMENT,
  7.   PRIMARY KEY (`id`)
  8. ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  9. 1 row in set (0.00 sec)
  11. ## 将id=7的数据update为10
  12. mysql> update t2 set id=10 where id=7;
  13. Query OK, 1 row affected (0.00 sec)
  14. Rows matched: 1  Changed: 1  Warnings: 0
  16. ## 最新的auto_increment变为11
  17. mysql> show create table t2\G;
  18. *************************** 1. row ***************************
  19.        Table: t2
  20. Create Table: CREATE TABLE `t2` (
  21.   `id` int(11) NOT NULL AUTO_INCREMENT,
  22.   PRIMARY KEY (`id`)
  23. ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
  24. 1 row in set (0.00 sec)

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

  1. mysql> show create table t3\G;
  2. *************************** 1. row ***************************
  3.        Table: t3
  4. Create Table: CREATE TABLE `t3` (
  5.   `id` int(11) NOT NULL AUTO_INCREMENT,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  8. 1 row in set (0.00 sec)
  10. ## updae更新操作
  11. mysql> update t3 set id=10 where id=7;
  12. Query OK, 1 row affected (0.27 sec)
  13. Rows matched: 1  Changed: 1  Warnings: 0
  15. mysql> show create table t3\G;
  16. *************************** 1. row ***************************
  17.        Table: t3
  18. Create Table: CREATE TABLE `t3` (
  19.   `id` int(11) NOT NULL AUTO_INCREMENT,
  20.   PRIMARY KEY (`id`)
  21. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
  22. 1 row in set (0.00 sec)

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

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

6 .被delete语句删除的id值，除非sql中将id重新插入，否则前面空余的id不会复用。
7 . delete from t3该语句不会引起auto_increment的变化，

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

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

  1. mysql> truncate table t3;
  2. Query OK, 0 rows affected (0.53 sec)
  4. mysql> show create table t3\G;
  5. *************************** 1. row ***************************
  6.        Table: t3
  7. Create Table: CREATE TABLE `t3` (
  8.   `id` int(11) NOT NULL AUTO_INCREMENT,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  11. 1 row in set (0.00 sec)
  1. last_insert_id()函数可获得自增列自动生成的最后一个编号。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。

    修改AUTO_INCREMENT字段的起始值

    可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。
    但是如果设置的n比目前的数值小的话，执行的sql不会报错，但是不会生效！MyISAM和Innodb均是如此。 ``sql mysql> show create table t2; +-------+----------------------- CREATE TABLEt2(idint(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

  2. <a name="tWn0n"></a>
  3. ## auto_increment_increment & auto_increment_offset 两个变量的介绍
  4. **这两个参数作用：**控制自增列AUTO_INCREMENT的行为，用于MASTER-MASTER之间的复制，防止出现重复值。<br />两个变量均可以设置为全局或局部变量，并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。如果试图将这些变量设置为大于65,535或小于0的值，则会将该值设置为65,535。如果向将auto_increment_increment或auto_increment_offset设置为非整数值，则会给出错误，并且变量的实际值在这种情况下保持不变。<br />两个值的含义：<br />auto_increment_increment：自增值的自增量<br />auto_increment_offset： 自增值的偏移量<br />设置了两个值之后，改服务器的自增字段值限定为：<br />**auto_increment_offset + auto_increment_increment*N**  的值，其中N>=0，但是上限还是要受定义字段的类型限制。<br />比如：<br />auto_increment_offset=1<br />auto_increment_increment=2<br />那么ID则是所有的奇数[1,3,5,7，.....]<br />如果：<br />auto_increment_offset=5<br />auto_increment_increment=10<br />那么ID则是所有的奇数[5,15,25,35，.....]<br />**查看当前值：**
  5. ```sql
  6. mysql> show variables like '%auto_increment%';
  7. +--------------------------+-------+
  8. | Variable_name            | Value |
  9. +--------------------------+-------+
  10. | auto_increment_increment | 1     |
  11. | auto_increment_offset    | 1     |
  12. +--------------------------+-------+

配置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来设置

  1. set global auto_increment_increment=2;
  2. set global auto_increment_offset=2;

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

  1. mysql> set global auto_increment_increment=2;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> show variables like '%auto_increment%';
  5. +--------------------------+-------+
  6. | Variable_name            | Value |
  7. +--------------------------+-------+
  8. | auto_increment_increment | 1     |
  9. | auto_increment_offset    | 1     |
  10. +--------------------------+-------+
  11. 2 rows in set (0.00 sec)
  13. mysql> show session variables like '%auto_increment%';
  14. +--------------------------+-------+
  15. | Variable_name            | Value |
  16. +--------------------------+-------+
  17. | auto_increment_increment | 1     |
  18. | auto_increment_offset    | 1     |
  19. +--------------------------+-------+
  20. 2 rows in set (0.00 sec)
  22. mysql> show global variables like '%auto_increment%';       
  23. +--------------------------+-------+
  24. | Variable_name            | Value |
  25. +--------------------------+-------+
  26. | auto_increment_increment | 2     |
  27. | auto_increment_offset    | 1     |
  28. +--------------------------+-------+
  29. 2 rows in set (0.00 sec)

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

  1. set session auto_increment_increment=2;
  2. set session auto_increment_offset=2;

具体的例子：
auto_increment_increment=2
auto_increment_offset=1

  1. mysql> truncate t2;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> 
  5. mysql> 
  6. mysql> set session auto_increment_increment=2;
  7. Query OK, 0 rows affected (0.00 sec)
  9. mysql> set session auto_increment_offset=1;
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> show session variables like '%auto_incre%';
  13. +--------------------------+-------+
  14. | Variable_name            | Value |
  15. +--------------------------+-------+
  16. | auto_increment_increment | 2     |
  17. | auto_increment_offset    | 1     |
  18. +--------------------------+-------+
  19. 2 rows in set (0.00 sec)
  21. mysql>  insert into t2 values (null),(null),(null),(null),(null),(null);
  22. Query OK, 6 rows affected (0.00 sec)
  23. Records: 6  Duplicates: 0  Warnings: 0
  25. mysql> select * from t2;
  26. +----+
  27. | id |
  28. +----+
  29. |  1 |
  30. |  3 |
  31. |  5 |
  32. |  7 |
  33. |  9 |
  34. | 11 |
  35. +----+
  36. 6 rows in set (0.00 sec)

auto_increment_increment=2
auto_increment_offset=2

  1. mysql> truncate t2;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> 
  5. mysql> set session auto_increment_increment=2;
  6. Query OK, 0 rows affected (0.00 sec)
  8. mysql> set session auto_increment_offset=2;
  9. Query OK, 0 rows affected (0.00 sec)
  11. mysql> show session variables like '%auto_incre%';
  12. +--------------------------+-------+
  13. | Variable_name            | Value |
  14. +--------------------------+-------+
  15. | auto_increment_increment | 2     |
  16. | auto_increment_offset    | 2     |
  17. +--------------------------+-------+
  18. 2 rows in set (0.00 sec)
  20. mysql>  insert into t2 values (null),(null),(null),(null),(null),(null);
  21. Query OK, 6 rows affected (0.00 sec)
  22. Records: 6  Duplicates: 0  Warnings: 0
  24. mysql> select * from t2;
  25. +----+
  26. | id |
  27. +----+
  28. |  2 |
  29. |  4 |
  30. |  6 |
  31. |  8 |
  32. | 10 |
  33. | 12 |
  34. +----+
  35. 6 rows in set (0.00 sec)

auto_increment_increment=10
auto_increment_offset=5

  1. mysql> truncate t2;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> set session auto_increment_increment=10;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql>  set session auto_increment_offset=5;
  8. Query OK, 0 rows affected (0.00 sec)
  10. mysql> show session variables like '%auto_incre%';
  11. +--------------------------+-------+
  12. | Variable_name            | Value |
  13. +--------------------------+-------+
  14. | auto_increment_increment | 10    |
  15. | auto_increment_offset    | 5     |
  16. +--------------------------+-------+
  17. 2 rows in set (0.00 sec)
  19. mysql> insert into t2 values (null),(null),(null),(null),(null),(null);
  20. Query OK, 6 rows affected (0.00 sec)
  21. Records: 6  Duplicates: 0  Warnings: 0
  23. mysql> select * from t2;
  24. +----+
  25. | id |
  26. +----+
  27. |  5 |
  28. | 15 |
  29. | 25 |
  30. | 35 |
  31. | 45 |
  32. | 55 |
  33. +----+
  34. 6 rows in set (0.00 sec)

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

  1. mysql> insert into t2 values (57),(58);
  2. Query OK, 2 rows affected (0.01 sec)
  3. Records: 2  Duplicates: 0  Warnings: 0
  5. mysql> select * from t2;
  6. +----+
  7. | id |
  8. +----+
  9. |  5 |
  10. | 15 |
  11. | 25 |
  12. | 35 |
  13. | 45 |
  14. | 55 |
  15. | 57 |
  16. | 58 |
  17. +----+
  18. 8 rows in set (0.00 sec)
  20. mysql> insert into t2 values (null),(null),(null);
  21. Query OK, 3 rows affected (0.00 sec)
  22. Records: 3  Duplicates: 0  Warnings: 0
  24. mysql> select * from t2;
  25. +----+
  26. | id |
  27. +----+
  28. |  5 |
  29. | 15 |
  30. | 25 |
  31. | 35 |
  32. | 45 |
  33. | 55 |
  34. | 57 |
  35. | 58 |
  36. | 65 |
  37. | 75 |
  38. | 85 |
  39. +----+
  40. 11 rows in set (0.00 sec)