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

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

 

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

1
2
3
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 key
mysql>

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

1
2
3
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值。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    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. 上面语句等同于下面语句:

    1
    mysql> insert into t5 (name) values (‘test‘);
  4. 当插入记录时,如果为AUTO_INCREMENT字段明确指定了一个数值,则会出现两种情况:

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    ## 初始表
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create 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变成4
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
         
    ## 插入7
    mysql> insert into t2 values (7);
    Query OK, 1 row affected (0.00 sec)
         
    ## auto_increment变成8
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create 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如下所示

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    ## 当前状态
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
        
    ## 将id=7的数据update为10
    mysql> 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变为11
    mysql> show create table t2\G;
    *************************** 1. row ***************************
           Table: t2
    Create Table: CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

    Innodb表的update操作如下所示

    (可以看到在update前后,表定义语句没有变化),接着执行insert会导致主键错误!

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    mysql> show create table t3\G;
    *************************** 1. row ***************************
           Table: t3
    Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    1 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: t3
    Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

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

    1
    2
    3
    4
    5
    6
    7
    8
    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的变化,

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

     

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    mysql> truncate table t3;
    Query OK, 0 rows affected (0.53 sec)
       
    mysql> show create table t3\G;
    *************************** 1. row ***************************
           Table: t3
    Create Table: CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 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均是如此。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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,.....]

 

 

查看当前值:

1
2
3
4
5
6
7
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来设置

1
2
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 来查看对应的变量值。

   下面是个例子:

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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有效

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

 

具体的例子:

 

auto_increment_increment=2

auto_increment_offset=1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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,那再往后生成的值会受前面插入数据的影响吗?

答案是: 不会的!!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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)

 

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

(0)
(0)
   
举报
评论 一句话评论(0
上一篇:mysql出现ERROR1698(28000):Access denied for user root@localhost错误解决方法


下一篇:狠人!标星 3.4 万的项目说删就删,几行代码搞崩数万个开源项目