专栏首页乐沙弥的世界MySQL auto_increment_increment,auto_increment_offset 用法

MySQL auto_increment_increment,auto_increment_offset 用法

    MySQL中对于表上ID自增列可以在创建表的时候来指定列上的auto_increment属性;等同于SQL server中的identity属性;Oracle则是通过Sequence方式来实现。在MySQL中,系统变量auto_increment_increment,auto_increment_offset 影响自增列的值及其变化规则。本文主要描述这两个系统变量的相关用法。

1、auto_increment_increment与auto_increment_offset作用

auto_increment_increment控制列中的值的增量值,也就是步长。
auto_increment_offset确定AUTO_INCREMENT列值的起点,也就是初始值。
变量范围:可以在全局以及session级别设置这2个变量

--当前系统环境
root@localhost[(none)]> show variables like 'version';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| version       | 5.5.39-log |
+---------------+------------+

root@localhost[mysql]> create database tempdb;

root@localhost[mysql]> use tempdb;

--查看变量auto_increment_increment与auto_increment_offset
root@localhost[tempdb]> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

2、演示auto_increment_increment与auto_increment_offset

--创建演示表,使用auto_increment子句
root@localhost[tempdb]> create table t1(id int not null auto_increment primary key, col varchar(20));

--插入记录
root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');

--下面可以看到id列起始值为1,增量为1
root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  1 | robin |
|  2 | fred  |
|  3 | jack  |
|  4 | james |
+----+-------+

--设置步长为5
root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

--清空表t1
root@localhost[tempdb]> truncate table t1;

--再次插入记录
root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');

--如下查询可以看到步长以5位基数发生变化
root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  1 | robin |
|  6 | fred  |
| 11 | jack  |
| 16 | james |
+----+-------+

--设置初始值为5
root@localhost[tempdb]> set session auto_increment_offset=5;

root@localhost[tempdb]> show variables like '%auto_incre%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 5     |
+--------------------------+-------+

root@localhost[tempdb]> truncate table t1;

root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack'),('james');

--下面是新的结果
root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  5 | robin |
| 10 | fred  |
| 15 | jack  |
| 20 | james |
+----+-------+

3、auto_increment_increment与auto_increment_offset取值范围

--将变量auto_increment_increment设置为0
root@localhost[tempdb]> set session auto_increment_increment=0;

--实际值变成了1
root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 5     |
+--------------------------+-------+

--同样将auto_increment_offset设置为0
root@localhost[tempdb]> set session auto_increment_offset=0;

--实际值也变成了1
root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

--下面尝试将2个变量设置为大于65535
root@localhost[tempdb]> set session auto_increment_increment=65537;

root@localhost[tempdb]> set session auto_increment_offset=65537;

--其实际的值都变成了65535
root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 65535 |
| auto_increment_offset    | 65535 |
+--------------------------+-------+

--尝试为2个变量设置为负值
root@localhost[tempdb]> set session auto_increment_offset=-2;

root@localhost[tempdb]> set session auto_increment_increment=-5;

--下面的查询可以看出全部恢复到缺省值1
root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

由上可以看出2个变量只能设置为1至65535之间的整数值。
所有非正整数全部会置为缺省值1,大于65535的值会被自动置为65535。

4、全局与session级别的设置

--查看全局范围这2个变量的值
root@localhost[tempdb]> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

--下面分别设置session基本的值
root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> set session auto_increment_offset=10;

--查看session级别的值
root@localhost[tempdb]> show session variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 10    |
+--------------------------+-------+

--查看全局级别的值
root@localhost[tempdb]> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

--设置全局级别的值
root@localhost[tempdb]> set global auto_increment_increment=2;

root@localhost[tempdb]> set global auto_increment_offset=3;

root@localhost[tempdb]> show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 3     |
+--------------------------+-------+

5、已有auto_increment列值任一变量变化的情形

root@localhost[tempdb]> truncate table t1;

root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

root@localhost[tempdb]> insert into t1(col) values('robin'),('fred'),('jack');          

root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  1 | robin |
|  2 | fred  |
|  3 | jack  |
+----+-------+

root@localhost[tempdb]> set session auto_increment_increment=5;

root@localhost[tempdb]> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

--Author: Leshami
--Blog  : http://blog.csdn.net/leshami

root@localhost[tempdb]> insert into t1(col) values('david'),('tim'),('jerry');

root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  1 | robin |
|  2 | fred  |
|  3 | jack  |
|  6 | david |
| 11 | tim   |
| 16 | jerry |
+----+-------+

New_value = auto_increment_offset+ N * auto_increment_increment
New_value1 = 1 + 1 * 5 = 6
New_value2 = 1 + 2 * 5 = 11

--下面是修改auto_increment_offset后的结果
root@localhost[tempdb]> set session auto_increment_offset=2;

root@localhost[tempdb]> insert into t1(col) values('lewis'),('ian');

root@localhost[tempdb]> select * from t1;
+----+-------+
| id | col   |
+----+-------+
|  1 | robin |
|  2 | fred  |
|  3 | jack  |
|  6 | david |
| 11 | tim   |
| 16 | jerry |
| 22 | lewis |
| 27 | ian   |
+----+-------+

这个id为22,应该是这样推算来的:max(id)+(new_offset-old_offset)+increment
也就是说变化auto_increment_offset后的第一个值为max(id)+(new_offset-old_offset)+increment之后再按步长递增。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 知识体系解决迷茫的你

    最近在星球里群里都有小伙伴说道自己对未来的路比较迷茫,一旦闲下来就不知道自己改干啥,今天我这篇文章就是让你觉得一天给你 25 个小时你都不够用,觉得睡觉都是浪费...

    桃翁
  • 「我真的没有改需求」

    非著名程序员
  • 复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

    从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负...

    haifeiWu
  • SQL中GROUP BY用法示例

    GROUP BY我们可以先从字面上来理解,GROUP表示分组,BY后面写字段名,就表示根据哪个字段进行分组,如果有用Excel比较多的话,GROUP BY比较类...

    Awesome_Tang
  • 这是对付产品经理的一副毒药,程序员慎入

    程序员和产品经理的日常就像是一对天生的冤家,为了需求的实现,几乎天天在争吵。这不,就在昨天各大技术和产品群里一个程序员暴打产品经理的视频火了,被广泛传播。

    非著名程序员
  • 穿越十年后看互联网+:家电行业的金矿在哪里?

    现在市场上炒得火热的智能家居未来出路在何方?做智能家居的创业者应该注意哪些机会?传统家电厂商又到底如何借助互联网进行转型?本文以智能空调为例,用故事的形式,提前...

    华章科技
  • 【系统设置】CentOS 修改机器名

    ken.io
  • 白底黑字or黑底白字,眼睛更喜欢哪一个?

    腾讯大讲堂
  • 你可以从面试中学到什么?

    讲一下我对面试的一些。。。“偏见”,哈哈,熟悉我的同学们一定要批判的读接下来的内容哈。

    web前端教室
  • 今天我就说三句话

    腾讯NEXT学位

扫码关注云+社区

领取腾讯云代金券