56,914
社区成员




create table t1(id int auto_increment primary key, v datetime) auto_increment=1;
create table t2(id int auto_increment primary key, v datetime) auto_increment=2;
create table t3(id int auto_increment primary key, v datetime) auto_increment=3;
create table t4(id int auto_increment primary key, v datetime) auto_increment=4;
set auto_increment_increment=2;
set auto_increment_offset=1;
insert t1(id,v) values(1,now()); insert t1(v) values(now()); insert t1(v) values(now());
insert t2(id,v) values(2,now()); insert t2(v) values(now()); insert t2(v) values(now());
insert t3(id,v) values(3,now()); insert t3(v) values(now()); insert t3(v) values(now());
insert t4(id,v) values(4,now()); insert t4(v) values(now()); insert t4(v) values(now());
select *, 't1' from t1 union all
select *, 't2' from t2 union all
select *, 't3' from t3 union all
select *, 't4' from t4;
drop table t1,t2,t3,t4;
这个是先在表中初始化一条数据,结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:42:33 | t1 |
| 3 | 2017-04-27 10:42:33 | t1 |
| 5 | 2017-04-27 10:42:33 | t1 |
| 2 | 2017-04-27 10:42:33 | t2 |
| 3 | 2017-04-27 10:42:33 | t2 |
| 5 | 2017-04-27 10:42:33 | t2 |
| 3 | 2017-04-27 10:42:33 | t3 |
| 5 | 2017-04-27 10:42:33 | t3 |
| 7 | 2017-04-27 10:42:33 | t3 |
| 4 | 2017-04-27 10:42:33 | t4 |
| 5 | 2017-04-27 10:42:33 | t4 |
| 7 | 2017-04-27 10:42:33 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=2; 结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:44:06 | t1 |
| 2 | 2017-04-27 10:44:06 | t1 |
| 4 | 2017-04-27 10:44:06 | t1 |
| 2 | 2017-04-27 10:44:06 | t2 |
| 4 | 2017-04-27 10:44:06 | t2 |
| 6 | 2017-04-27 10:44:06 | t2 |
| 3 | 2017-04-27 10:44:06 | t3 |
| 4 | 2017-04-27 10:44:06 | t3 |
| 6 | 2017-04-27 10:44:06 | t3 |
| 4 | 2017-04-27 10:44:06 | t4 |
| 6 | 2017-04-27 10:44:06 | t4 |
| 8 | 2017-04-27 10:44:06 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=3; 结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:45:00 | t1 |
| 3 | 2017-04-27 10:45:00 | t1 |
| 5 | 2017-04-27 10:45:00 | t1 |
| 2 | 2017-04-27 10:45:00 | t2 |
| 3 | 2017-04-27 10:45:00 | t2 |
| 5 | 2017-04-27 10:45:00 | t2 |
| 3 | 2017-04-27 10:45:00 | t3 |
| 5 | 2017-04-27 10:45:00 | t3 |
| 7 | 2017-04-27 10:45:00 | t3 |
| 4 | 2017-04-27 10:45:00 | t4 |
| 5 | 2017-04-27 10:45:00 | t4 |
| 7 | 2017-04-27 10:45:00 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=4; 结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:45:26 | t1 |
| 2 | 2017-04-27 10:45:26 | t1 |
| 4 | 2017-04-27 10:45:26 | t1 |
| 2 | 2017-04-27 10:45:26 | t2 |
| 4 | 2017-04-27 10:45:26 | t2 |
| 6 | 2017-04-27 10:45:26 | t2 |
| 3 | 2017-04-27 10:45:26 | t3 |
| 4 | 2017-04-27 10:45:26 | t3 |
| 6 | 2017-04-27 10:45:26 | t3 |
| 4 | 2017-04-27 10:45:26 | t4 |
| 6 | 2017-04-27 10:45:26 | t4 |
| 8 | 2017-04-27 10:45:26 | t4 |
+----+---------------------+----+create table t1(id int auto_increment primary key, v datetime) auto_increment=1;
create table t2(id int auto_increment primary key, v datetime) auto_increment=2;
create table t3(id int auto_increment primary key, v datetime) auto_increment=3;
create table t4(id int auto_increment primary key, v datetime) auto_increment=4;
set auto_increment_increment=2;
set auto_increment_offset=1;
insert t1(v) values(now());insert t1(v) values(now());
insert t2(v) values(now());insert t2(v) values(now());
insert t3(v) values(now());insert t3(v) values(now());
insert t4(v) values(now());insert t4(v) values(now());
select *, 't1' from t1 union all
select *, 't2' from t2 union all
select *, 't3' from t3 union all
select *, 't4' from t4;
drop table t1,t2,t3,t4;
这个测试的结果:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:37:42 | t1 |
| 3 | 2017-04-27 10:37:42 | t1 |
| 3 | 2017-04-27 10:37:42 | t2 |
| 5 | 2017-04-27 10:37:42 | t2 |
| 3 | 2017-04-27 10:37:42 | t3 |
| 5 | 2017-04-27 10:37:42 | t3 |
| 5 | 2017-04-27 10:37:42 | t4 |
| 7 | 2017-04-27 10:37:42 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=2,则结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 2 | 2017-04-27 10:39:54 | t1 |
| 4 | 2017-04-27 10:39:54 | t1 |
| 2 | 2017-04-27 10:39:54 | t2 |
| 4 | 2017-04-27 10:39:54 | t2 |
| 4 | 2017-04-27 10:39:54 | t3 |
| 6 | 2017-04-27 10:39:54 | t3 |
| 4 | 2017-04-27 10:39:54 | t4 |
| 6 | 2017-04-27 10:39:54 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=3,则结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 1 | 2017-04-27 10:40:28 | t1 |
| 3 | 2017-04-27 10:40:28 | t1 |
| 3 | 2017-04-27 10:40:28 | t2 |
| 5 | 2017-04-27 10:40:28 | t2 |
| 3 | 2017-04-27 10:40:28 | t3 |
| 5 | 2017-04-27 10:40:28 | t3 |
| 5 | 2017-04-27 10:40:28 | t4 |
| 7 | 2017-04-27 10:40:28 | t4 |
+----+---------------------+----+
如果 set auto_increment_offset=4,则结果是:
+----+---------------------+----+
| id | v | t1 |
+----+---------------------+----+
| 2 | 2017-04-27 10:41:06 | t1 |
| 4 | 2017-04-27 10:41:06 | t1 |
| 2 | 2017-04-27 10:41:06 | t2 |
| 4 | 2017-04-27 10:41:06 | t2 |
| 4 | 2017-04-27 10:41:06 | t3 |
| 6 | 2017-04-27 10:41:06 | t3 |
| 4 | 2017-04-27 10:41:06 | t4 |
| 6 | 2017-04-27 10:41:06 | t4 |
+----+---------------------+----+