Innodb 的自动是如何控制的

zjcxc 2017-04-27 10:37:14
官网的说明没看明白, 谁详细解释一下呢?
表上的 auto_increment , 两个变量 auto_increment_increment, auto_increment_offset 之间是什么关系?

自己测试了一下,和 SQL Server 不一样,也没从结果中看出什么


...全文
308 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
totola147 2017-10-10
  • 打赏
  • 举报
回复
如果要用global级的则要在两个实例中测,原本这个参数是给 master - master 主主复制用的 ,两个实例上 increment 相同 offset不同且小于 increment 就可以实现你的需求
totola147 2017-10-10
  • 打赏
  • 举报
回复
这个变量既有session级的又有global级的 测试的时候用session级的 开两个终端测,不能再一个终端里测
测试结果如期望


其中要注意 offset 要小于 increment 否则会被忽略
参考:https://dev.mysql.com/doc/refman/5.7/en/replication-options-master.html#sysvar_auto_increment_offset
zjcxc 2017-04-28
  • 打赏
  • 举报
回复
有人研究过么?
rucypli 2017-04-27
  • 打赏
  • 举报
回复
我试了试 彻底糊涂了
zjcxc 2017-04-27
  • 打赏
  • 举报
回复
原来是想做两个表,把增长设置为2,起始值分别设置为1 和 2, 这样就可以两个表生成的自增值不冲突 结果按照 SQL Server 的想法,建两个表,AUTO_INCREMENT分别为1 和2,然后设置 AUTO_INCREMENT_INCREMENT=2,结果发现出来的数据不对 然后就弄了上面的测试,结果彻底糊涂了
zjcxc 2017-04-27
  • 打赏
  • 举报
回复
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 | +----+---------------------+----+
zjcxc 2017-04-27
  • 打赏
  • 举报
回复
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 | +----+---------------------+----+

56,914

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧