在系统开发过程中,我们经常要用到唯一编号。使用过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的列,在插入时:
-
如果把一个NULL插入到一个AUTO_INCREMENT数据列里去,MySQL将自动生成下一个序列编号。编号从1开始,并1为基数递增。
-
当插入记录时,没有为AUTO_INCREMENT明确指定值,则等同插入NULL值。
12345678910mysql> 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)
-
上面语句等同于下面语句:
1mysql> insert into t5 (name) values (
‘test‘
);
-
当插入记录时,如果为AUTO_INCREMENT字段明确指定了一个数值,则会出现两种情况:
情况一,如果插入的值与已有的编号重复,则会出现出 错信息,因为AUTO_INCREMENT数据列的值必须是唯一的;
情况二,如果插入的值大于已编号的值,则会把该插入到数据列中,并使在下一个编号将从这个新值开始递增。
12345678910111213141516171819202122232425262728293031323334## 初始表
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
换句话说,就是自增字段可以跳过一些编号。
-
对于MyISAM表,如果用UPDATE命令更新自增列,如果列值与已有的值重复,则会出错。如果大于已有值,则下一个编号从该值开始递增。但是对于innodb表,update auto_increment字段,会导致发生报错
MyISAM表的update如下所示
123456789101112131415161718192021222324## 当前状态
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会导致主键错误!
12345678910111213141516171819202122mysql> 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之后会正常插入
12345678mysql> 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‘
-
被delete语句删除的id值,除非sql中将id重新插入,否则前面空余的id不会复用。
-
delete from t3该语句不会引起auto_increment的变化,
1234567891011mysql>
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的变化,从头开始。
1234567891011mysql> 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)
-
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 var iables 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 var iables like ‘%auto_increment%‘ ;
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+ 2 rows in set ( 0.00 sec)
mysql> show session var iables like ‘%auto_increment%‘ ;
+--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+ 2 rows in set ( 0.00 sec)
mysql> show global var iables 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 var iables 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 var iables 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 var iables 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)
|