Auto_increment详解

2016-07-31 阅读量

Auto_increment

Mysql AUTO_INCREMENT

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

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开始，但是该强制的默认值是保留在内存中的，如果该值在使用之前数据库重新启动，那么这个强制的默认值就会丢失，就需要数据库启动后重新设置

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()查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。

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

非主键
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表，自动增长列可以使组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。

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最大值被删除，将不会被重用。就是说会跳号

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不推荐

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属性能正确工作，但以下情况还是有问题：

INSERT DELAYED … VALUES(LAST_INSERT_ID())不能被正确复制
存储过程插入的使用AUTO_INCREMENT属性的记录不能被正确复制
通过”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的值会被忽略）

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)