MySQL和PostgreSQL的自增序列探讨

PostgreSQL中文社区 2021-03-11

3000

作者简介

李鑫&崔鹏,任职于海能达通信股份有限公司,数据库开发高级工程师,专网通信领域、公共安全领域PostgreSQL数据库的管理与维护,致力于高并发高可用数据库架构设计和数据库性能的优化。

在工作中不可避免的使用自增序列，因为比较熟悉MySQL和PostgreSQL，所以对其自增序列做一个探讨。

在MySQL中给与字段AUTO_INCREMENT属性来支持自增序列。

创建一个表带有自增序列主键：

'''
    drop table if exists test;
    create table if not exists test
    (
    id_1 int not null auto_increment primary key,
    name_1 char(10)
    );
'''

插入三条记录：

insert into test values(1,'a');
    insert into test(id_1,name_1) values(2,'a');
    insert into test(name_1) values('b');

查询一下看到：

 select * from test;
     1  a
     2  a
     3  b

也可以这么插入，找一个不会出现在自增列的数字站位，避免主键冲突：

 insert into test values(0,'a');

执行两遍查看结果：

 select * from test;
    1  a
    2  a
    3  b
    4  a
    5  a

删除id_1=5的行，再执行插入：

delete from test where id_1=5;
    insert into test values(0,'a');
    select * from test;
    1  a
    2  a
    3  b
    4  a
    6  a

可以看到是自增序列是继续增加的。

插入一条跳跃的序列，然后再插入一条记录：

insert into test values(9,'c');
    insert into test values(0,'a');
    select * from test;
    1  a
    2  a
    3  b
    4  a
    6  a
    9  c
    10  a

可以看到序列是继续增加到10的，而不是接着跳跃之前的6增加到7.

delete和truncate的区别：

重建表插入数据进行delete验证：

 drop table if exists test;
    create table if not exists test
    (
     id_1 int not null auto_increment primary key,
     name_1 char(10)
     );
     
    insert into test values(1,'a');
    insert into test(id_1,name_1) values(2,'a');
    insert into test(name_1) values('b');
    delete from test;
    insert into test values(0,'a');
    select * from test;
    
    4  a

truncate删除表中数据，再插入时自增长id又从1开始。

重建表插入数据进行truncate验证：

drop table if exists test;
    create table if not exists test
    (
     id_1 int not null auto_increment primary key,
     name_1 char(10)
     );
     
    insert into test values(1,'a');
    insert into test(id_1,name_1) values(2,'a');
    insert into test(name_1) values('b');
    truncate table test;
    insert into test values(0,'a');
    select * from test;
    
    1  a

mysql的自增序列有最大值，达到最大值之后就不再增加，这里int的最大值为2147483647

插入一条最大值的记录

 insert into test values(2147483647,'d');
    select * from test;
    1  a
    2147483647  d

再次执行插入会报错：

insert into test values(0,'a');
    [SQL]insert into test values(0,'a');
    [Err] 1062 - Duplicate entry '2147483647' for key 'PRIMARY'

插入比2147483647打的数也报错：

 insert into test values(2147483648,'d');
    [SQL]insert into test values(2147483648,'d');
    [Err] 1062 - Duplicate entry '2147483647' for key 'PRIMARY'

所以当自增序列在使用时达到最大值的时候需要对表数据进行维护。

PostgreSQL 使用序列来标识字段的自增长，数据类型有 smallserial、serial 和 bigserial。

serialzi是4字节范围是1 到 2,147,483,647跟上面的mysql例子中匹配，所以选用其做pg的实验。

创建一个表带有自增序列主键：

 drop table if exists test;
    create table if not exists test
    (
     id_1 SERIAL not null  primary key,
     name_1 char(10)
    );

插入三条记录：

insert into test values(1,'a');
    insert into test(id_1,name_1) values(2,'a');
    insert into test(name_1) values('b');

发现前两条是成功的，执行第三条失败了

insert into test(name_1) values('b');
    [Err] ERROR:  duplicate key value violates unique constraint "test_pkey"
    DETAIL:  Key (id_1)=(1) already exists.

再次执行该语句：

insert into test(name_1) values('b');
    [Err] ERROR:  duplicate key value violates unique constraint "test_pkey"
    DETAIL:  Key (id_1)=(2) already exists.

再次执行该语句：

insert into test(name_1) values('b');

时间: 0.052s

受影响的行: 1

执行成功，数据插入：

 select * from test;
    1  a         
    2  a         
    3  b

说明即使指定了id_1字段的值插入数据，当自增列缺省时再次插入，序列还是会从1开始计数。

这个序列自增的含义意味着是第几次执行插入动作，当该字段被赋值时，不计数在内。

执行带有负值和0值的id_1，发现插入成功：

  insert into test values(-1,'a');
    insert into test values(0,'a');
    select * from test;
    -1  a         
    0  a

所以指定serial字段类型字段插入的情况下是可以插入负值和0值的。

插入5条记录然后再删除id_1=5的记录，再执行插入：

drop table if exists test;
    create table if not exists test
    (
    id_1 SERIAL not null  primary key,
    name_1 char(10)
    );
    
    insert into test(name_1) values('b');
    insert into test(name_1) values('b');
    insert into test(name_1) values('b');
    insert into test(name_1) values('b');
    insert into test(name_1) values('b');
    
    delete from test where id_1=5;
    
    select * from test;
    1  b         
    2  b         
    3  b         
    4  b         

    insert into test(name_1) values('b');
    select * from test;
    1  b         
    2  b         
    3  b         
    4  b         
    6  b

同样跳过了id_1=5，自增序列是继续增加的。

根据之前的验证，如果插入id_1=9的记录，再次执行插入，新插入的id_1=7

insert into test values(9,'c');
    insert into test(name_1) values('b');
    select * from test;
    1  b         
    2  b         
    3  b         
    4  b         
    6  b         
    9  c         
    7  b

delete和truncate的验证：

在上面操作的基础上，对表执行delete操作然后插入一条数据库，并查看：

delete from test;
    insert into test(name_1) values('b');
    select * from test;
    8  b

发现id_1自增到了8。

在上面操作的基础上，对表执行truncate操作然后插入一条数据库，并查看：

truncate table test;
    insert into test(name_1) values('b');
    select * from test;
    9  b

发现id_1自增到了9。

这里可以看到跟mysql的区别，TRUNCATE并没有将自增序列的id初始化为1，而是继续增长.

SMALLSERIAL、SERIAL 和 BIGSERIAL 范围：

SMALLSERIAL 1 到 32,767

SERIAL 1 到 2,147,483,647

BIGSERIAL 1 到 922,337,2036,854,775,807

同样实验下达到最大值后溢出的场景

drop table if exists test;
    create table if not exists test
    (
    id_1 SMALLSERIAL not null  primary key,
    name_1 char(10)
    );

    DO $$
    declare
       id_count integer;
    begin
    id_count := 0;
      while id_count < 32767 
      loop
    insert into test(name_1) values('b');
    id_count := id_count+1;
    end loop;
    end $$;

用SMALLSERIAL作为实验对象，插入32767记录，然后再次执行插入看下效果：

insert into test(name_1) values('b');
    [Err] ERROR:  nextval: reached maximum value of sequence "test_id_1_seq" (32767)

同样报错，说明到最大值的时候，并不是循环使用而是超限报错了。

结论：

1、mysql在字段上加AUTO_INCREMENT属性来实现自增，pg使用serial类型，序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是创建了一个序列。

2、mysql插入的是字段的值，pg执行插入的时候变化的是字段值和序列的值，只不过在缺省插入的时候二者相等。

所以，INSERT时如果没有给该字段赋值会默认获取下一个值或者对应序列的下一个值。

但是在该字段有赋值插入之后，就有区别了，pg赋值插入的时候是改变了字段的值但是没有改变序列的值，所以再次缺省赋值插入时依旧是顺序获取对应序列的下一个值。

3、mysql和pg在delete之后插入是同样的，但是truncate操作在pg上是清空了表数据，但是没有清空对应的序列，所以在执行truncate之后，再次插入会获取者对应序列的下一个值，而在mysql上是从1开始。

4、达到规定最大值之后都不能继续插入，所以不能循环使用。

PostgreSQL中文社区欢迎广大技术人员投稿
投稿邮箱：press@postgres.cn

mysql postgresql

文章转载自PostgreSQL中文社区，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL和PostgreSQL的自增序列探讨

评论