Auto_increment详解

Auto_increment

Mysql AUTO_INCREMENT

1.Innodb表的自动增长列可以手工插入,但是插入的值如果是空或者0,则实际插入的将是自动增长后的值

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
mysql> create table t1(id int not null auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.06 sec)
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into t1 values(0,'fanboshi');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(null,'duyalan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
+----+----------+
2 rows in set (0.00 sec)

2.可以通过alter table t1 auto_incremenrt=n 语句强制设置自动增长列的初始值,默认从1开始,但是该强制的默认值是保留在内存中的,如果该值在使用之前数据库重新启动,那么这个强制的默认值就会丢失,就需要数据库启动后重新设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> alter table t1 auto_increment=5;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into t1 values(null,'handudu');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
| 5 | handudu |
+----+----------+
3 rows in set (0.00 sec)

3.可以是用last_insert_id()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。

1
2
3
4
5
6
7
8
9
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)
注意last_insert_id()是所有表auto_increment的最新插入值,
因此在并发的情况下,获取某表的最新插入auto_increment可能出现错误

4.对于innodb表,自动增长列必须是索引,且必须是组合索引的第一列,且一个表只能有一个auto_increment属性。

mysql> create table t2(id int not null auto_increment,name varchar(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

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
非主键
mysql> create table t2(id int not null auto_increment,name varchar(10),index(id));
Query OK, 0 rows affected (0.09 sec)
mysql> mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 1 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
不是主键,只是有索引
mysql> insert into t2 values(1,'fan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(2,'fan');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | fan |
| 2 | fan |
+----+------+
2 rows in set (0.00 sec)
如果是组合索引,也必须是组合索引的第一列
mysql> create table t3(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

但是对于MyISAM表,自动增长列可以使组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的。

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
41
42
43
44
45
mysql> create table t3_myisam(id1 int not null auto_increment,id2 int,name varchar(10),index(id2,id1)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t3_myisam;
+-----+------+----------+
| id1 | id2 | name |
+-----+------+----------+
| 1 | 3 | fanboshi |
| 1 | 1 | duyalan |
| 2 | 1 | daduzi |
| 1 | 2 | fan |
| 1 | 5 | hehe |
| 1 | 6 | keke |
+-----+------+----------+
6 rows in set (0.00 sec)
好像看不出啥规律
再插入一次
mysql> insert into t3_myisam(id2,name) values(3,'fanboshi'),(1,'duyalan'),(1,'daduzi'),(2,'fan'),(5,'hehe'),(6,'keke');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t3_myisam order by id2,id1;
+-----+------+----------+
| id1 | id2 | name |
+-----+------+----------+
| 1 | 1 | duyalan |
| 2 | 1 | daduzi |
| 3 | 1 | duyalan |
| 4 | 1 | daduzi |
| 1 | 2 | fan |
| 2 | 2 | fan |
| 1 | 3 | fanboshi |
| 2 | 3 | fanboshi |
| 1 | 5 | hehe |
| 2 | 5 | hehe |
| 1 | 6 | keke |
| 2 | 6 | keke |
+-----+------+----------+
12 rows in set (0.00 sec)
id2=1有四个,所以id1有1,2,3,4
id2=2有俩,id1=1,2
自动增长列id1作为组合索引的第二列,对该表插入一些记录后,可以发现自动增长列是按照组合索引第一列id2进行排序后分组递增的

5.MyISAM 及INNODB表,表中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
mysql> insert into t1(name) values('hehe');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
| 5 | handudu |
| 6 | hehe |
+----+----------+
4 rows in set (0.00 sec)
mysql> delete from t1 where id=6;
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1(name) values('keke');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 2 | duyalan |
| 5 | handudu |
| 7 | keke |
+----+----------+
4 rows in set (0.00 sec)

6.用”WHERE auto_col IS NULL”条件选择出新插入的行,即在INSERT后马上用:

SELECT * FROM t4 WHERE id IS NULL;
选择出来的将是新插入的行,而非真正的满足”id IS NULL”条件的行。
但你要是再执行一次上述查询,则返回的又变成了真正的满足”a IS NULL”条件的行,
由于a是主键,因此肯定会返回空集。这看上去很诡异是吗,不过MySQL也不想这么干,为了支持 ODBC标准
不过可以将SQL_AUTO_IS_NULL设为0来禁止这一用法。
此方法获取last_insert_id不推荐

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> insert into t1(name) values('new');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1 where id is null;
Empty set (0.00 sec)
mysql> show variables like 'sql_auto_is_null';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_auto_is_null | OFF |
+------------------+-------+
1 row in set (0.00 sec)
mysql> set session sql_auto_is_null=on;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like 'sql_auto_is_null';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_auto_is_null | ON |
+------------------+-------+
1 row in set (0.00 sec)
mysql> select * from t1 where id is null;
+----+------+
| id | name |
+----+------+
| 8 | new |
+----+------+
1 row in set (0.01 sec)
mysql> select * from t1 where id is null;
Empty set (0.00 sec)

7.AUTO_INCREMENT属性也给复制带来了麻烦。一般情况下复制AUTO_INCREMENT属性能正确工作,但以下情况还是有问题:

  1. INSERT DELAYED … VALUES(LAST_INSERT_ID())不能被正确复制
  2. 存储过程插入的使用AUTO_INCREMENT属性的记录不能被正确复制
  3. 通过”ALTER TABLE”命令增加AUTO_INCREMENT属性时在主从节点上产生的值可能是不一样的,因为这个各行AUTO_INCREMENT属性的值取决于物理上的存储顺序。

8.对于replication的master-master方式 为防止auto_increment字段的重复,可做如下设置

A服务器的my.cnf设置如下:

auto_increment_offset = 1
auto_increment_increment = 2

这样A的auto_increment字段产生的数值是:1, 3, 5, 7, …

B服务器的my.cnf设置如下:

auto_increment_offset = 2
auto_increment_increment = 2

这样B的auto_increment字段产生的数值是:2, 4, 6, 8, …

3和节点
A offset=1 incr=3
B offset=2 incr=3
C offset=3 incr=3

8.根据官方的说明:If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. (如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略)

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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 session auto_increment_offset=5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 5 |
+--------------------------+-------+
2 rows in set (0.00 sec)
mysql> create table t5 like t1;
Query OK, 0 rows affected (0.07 sec)
mysql> desc t5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> insert into t5(name) values('fanboshi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
+----+----------+
1 row in set (0.00 sec)
mysql> set session auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t5(name) values('duyalan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5(name) values('heheda');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t5;
+----+----------+
| id | name |
+----+----------+
| 1 | fanboshi |
| 5 | duyalan |
| 10 | heheda |
+----+----------+
3 rows in set (0.00 sec)

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#