PostgreSQL自增主键的用法以及在mybatis中的使用

客官°小女子只卖身不卖艺 2023-09-30 15:58 11阅读 0赞

## 前言 ##

近期，对产品进行数据库由MySql迁移至PostgreSQL过程中，在MySql自增主键到PostgreSQL自增主键的迁移适配中，历经了一点曲折，最终通过跳坑和出坑的过程也算解决了问题，特此记录，给遇到类似的同学做过经验总结。

## 什么是自增主键？ ##

设置了`自增主键`时，主键的生成完全依赖数据库，无需人为干预。新增数据的时，开发人员不需要手动设置主键字段的值，数据库就会自动生成一个主键值。

## 为什么需要自增主键？ ##

*  自增主键可让主键索引保持递增顺序插入，因此避免了页分裂；
 *  相较于其他类型（比如varchar），使用自增主键一定程度上更加节省存储开销；
 *  应用程序维护较为简单，代码中只需要统一配置，无需手动设置主键值；

## 一、MySql中自增主键的使用 ##

### 1、创建一个自增主键的表 ###

create table t_user(
    	`id` INT NOT NULL AUTO_INCREMENT COMMENT '主键id',
    	`age` INT(11) NOT NULL DEFAULT 10 COMMENT '年龄',
    	PRIMARY KEY (`id`)
    )

### 2、SQL插入数据时的写法 ###

insert into t_user(age) values(18)
    insert into t_user(age) values(20)

查询一下上述的插入数据

![aa0eda3b73614b1791509550e1eefee2.png][]

### 3、修改自增主键的起始值 ###

在某些情况下，应用需要ID主键从某个指定的位置开始，或者说大于这个值（比如：预留中间的某个ID范围区间），可以在已知表的基础上使用下面的sql语句调整

> alter table t\_user auto\_increment=10;

然后，再往上面的表插入两条数据

> insert into t\_user(age) values(24)  
> insert into t\_user(age) values(26)

再次观察效果，可以发现这时候id的起始值就变成了11

![29e89d2e9e9f4935a23cf1d170c688a8.png][]

### 4、mybatis中自增主键的用法 ###

在mybatis对自增主键的处理上面可以采用下面的方式

<insert id="addUser" parameterType="com.congge.entity.TUser">
            INSERT INTO t_user
            (age)
            VALUES (
            #{age}
            )
            <selectKey keyProperty="id" resultType="java.lang.Integer" order="AFTER">
                SELECT LAST_INSERT_ID();
            </selectKey>
        </insert>

**补充说明**

> 上述建表的时候，直接通过建表限定了主键自增，也可以在建表之前不指定，而是使用alert语句修改：
> 
> alter table t\_user modify id integer auto\_increment ;

## 二、PostgreSQL中自增主键的使用 ##

在PostgreSQL中，自增主键的使用略有差别，在建表的时候通过指定字段类型为serial ，来标识当前字段为自增主键；

PostgreSQL中，可以通过如下两种方式来达到设置一个主键值为递增的序列（mysql同样可以）

**前置准备，创建一个普通的PG表**

create table t_user(
    	id INT NOT NULL ,
    	age INT NOT NULL DEFAULT 10,
    	PRIMARY KEY (id)
    )

### 方式1：通过手动创建序列达到递增的效果 ###

> 创建一个自增的序列（类似于mysql中的函数），后续每次需要获取自增主键的时候，调用一下这个序列就可以了。

####    **1、建立自增主键序列** ####

> CREATE SEQUENCE  
> t\_user\_id\_seq  
> INCREMENT 1 -- 步长  
> MINVALUE 1 -- 最小值  
> MAXVALUE 9999 --最大值  
> START WITH 1 --起始值  
> CACHE 1;

执行一下上面的创建序列方法之后，可以通过下面的sql查询数据库中的所有序列

> select \* from information\_schema.sequences where sequence\_schema = 'public';

图中圈起来的即为上面我们刚刚创建的序列值

![8bd3fdd6a453449e9966de060da87b2c.png][]

#### 2、调用自增序列方法插入数据 ####

接下来就是要调用序列的相关方法，然后用到插入语句的sql中即可，假如要给上面的t\_user表插入数据，可以使用下面的sq操作，

insert into t_user values(
    	nextval('t_user_id_seq') , 18
    )
    
    insert into t_user values(
    	nextval('t_user_id_seq') , 22
    )

![92b3176dd13e418eb5d084b73dc0d473.png][]

#### 3、自增序列常用方法总结 ####

可以看到，数据正如我们预期的按照主键自增的方式插入进去了，在上面的插入语句中，使用到了nextval这个方法，属于自增序列中默认提供的方法之一，自增序列提供了常见的下面几种方法提供参考

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>函 数</td> 
   <td>返 回 类 型</td> 
   <td>描说明</td> 
  </tr> 
  <tr> 
   <td>lastval()</td> 
   <td>bigint</td> 
   <td>返回最近一次用 nextval 获取的任意序列的数值</td> 
  </tr> 
  <tr> 
   <td>nextval( regclass )</td> 
   <td>bigint</td> 
   <td>递增序列并返回新值</td> 
  </tr> 
  <tr> 
   <td>currval( regclass )</td> 
   <td>bigint</td> 
   <td>获取指定序列最近一次使用netxval后的数值，如果没有使用nextval而直接使用currval会出错。</td> 
  </tr> 
  <tr> 
   <td>setval( regclass,bigint ,boolean )</td> 
   <td>bigint</td> 
   <td>设置序列的当前数值以及 is_called 标志，如果为true则立即生效，如果为false，则调用一次nextval后才会生效</td> 
  </tr> 
  <tr> 
   <td>setval( regclass,bigint )</td> 
   <td>bigint</td> 
   <td>设置序列的当前数值</td> 
  </tr> 
 </tbody> 
</table>

#### 4、设置自增主键默认值 ####

上面提供的方式发现在写insert语句的时候，还需要附加上函数，多少有点繁琐，于是，可以考虑使用下面的方式对ID字段的默认值进行调整；

**设置id字段的默认值为nextval('t\_user\_id\_seq')**，在上面创建完毕序列的基础上直接添加这一句

alter table 
    	t_user 
    alter column 
    	id  
    set default nextval(
    	't_user_id_seq'  
    );

再次插入数据时候，就直接写成下面这样即可

insert into t_user values(
    	23
    )
    insert into t_user values(
    	24
    )

查询数据，发现仍然可以成功写进去

![121583df15874f4182a3529e4aed684b.png][]

### 方式2：通过指定字段为serial类型达到递增的效果 ###

#### 1、使用下面的建表语句 ####

create table t_user(
    	id serial NOT NULL ,
    	age INT NOT NULL DEFAULT 10,
    	PRIMARY KEY (id)
    )

#### 2、查看创建的序列 ####

> select \* from information\_schema.sequences where sequence\_schema = 'public';

注意，之前创建的序列不会被清理，除非手动调用清理序列的语句，默认情况下，序列的名称为表明+id\_seq，由于之前创建过，这里自动再后面拼接了一个seq1，也就是说，id使用serial，PG默认就会为当前这个表附加一个序列；

![bf7fd1980942478e97e0cdc97a0dcbf6.png][]

#### 3、插入数据 ####

再次插入数据时，直接像下面这样写就可以了

insert into t_user(age) values(
    	23
    );
    insert into t_user(age) values(
    	24
    );

仍然可以查出数据，而且id是递增的；

![2b547d8f03e5487ead1db5e09463b9d0.png][]

#### 4、PG序列在mybatis中的使用 ####

<insert id="addUser" parameterType="com.congge.entity.TUser">
            INSERT INTO t_user
            (age)
            VALUES (
            #{age}
            )
            <selectKey keyProperty="id" resultType="java.lang.Integer" order="AFTER">
                SELECT nextval('t_user_id_seq'::regclass) as id
            </selectKey>
        </insert>

## 三、MySql数据迁移至PostgreSQL关于自增主键的一点建议 ##

实际业务中，可能会先做数据层面的迁移，迁移完成之后，业务上才能正常的使用，但是小编在迁移数据之后，却发现界面上操作报错，报错的主要原因就是主键冲突，为啥会这样呢？

举例来说，mysql中t\_user这张表的数据id最大值为99，迁移到pg之后，数据最大值仍为99，这没有毛病，但是切库之后，使用PG自增序列创建数据时，可是从1开始的啊，迁移后，ID为1的这条数据已经存在了，当然会报错了；

这个时候，就需要对迁移后的序列做一下简单的设置了，核心思路如下，可供参考：

*  使用max函数查找当前ID的最大值：select max(id) from t\_user;
 *  手动调整一下序列的起始值：alter sequence t\_user\_id\_seq restart with 【第一步中的最大值或者加一点】;

[aa0eda3b73614b1791509550e1eefee2.png]: https://img-blog.csdnimg.cn/aa0eda3b73614b1791509550e1eefee2.png
[29e89d2e9e9f4935a23cf1d170c688a8.png]: https://img-blog.csdnimg.cn/29e89d2e9e9f4935a23cf1d170c688a8.png
[8bd3fdd6a453449e9966de060da87b2c.png]: https://img-blog.csdnimg.cn/8bd3fdd6a453449e9966de060da87b2c.png
[92b3176dd13e418eb5d084b73dc0d473.png]: https://img-blog.csdnimg.cn/92b3176dd13e418eb5d084b73dc0d473.png
[121583df15874f4182a3529e4aed684b.png]: https://img-blog.csdnimg.cn/121583df15874f4182a3529e4aed684b.png
[bf7fd1980942478e97e0cdc97a0dcbf6.png]: https://img-blog.csdnimg.cn/bf7fd1980942478e97e0cdc97a0dcbf6.png
[2b547d8f03e5487ead1db5e09463b9d0.png]: https://img-blog.csdnimg.cn/2b547d8f03e5487ead1db5e09463b9d0.png