正文 

当前位置： 首页 > java > java教程

postgresql自增主键的用法以及在mybatis中的使用(代码片段)

小码农叔叔 小码农叔叔     2023-03-29     712

关键词：

前言

近期，对产品进行数据库由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)

查询一下上述的插入数据

 

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

 

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';

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

 

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
)

 

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

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

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

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
)

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

 

方式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默认就会为当前这个表附加一个序列；

 

3、插入数据

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


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

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

 

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 【第一步中的最大值或者加一点】;

提取自增主键的标准做法是啥？

】提取自增主键的标准做法是啥？【英文标题】：Whatisthestandardpracticeforextractinganauto-incrementingprimarykey?提取自增主键的标准做法是什么？【发布时间】：2013-12-2505:59:29【问题描述】：我有一些对象将被创建并输入到数据库中。除... 查看详情

mysql自增主键怎么用(代码片段)

导航自增主键怎么设置MySQL是怎么保存自增主键的自增主键的修改机制造成自增主键不连续的几种情形自增主键和UUID及随机ID比较自增主键的好处自增主键的坏处UUID及随机ID参考文章自增主键怎么设置通常，自增主键的设置... 查看详情

mysql自增主键怎么用(代码片段)

导航自增主键怎么设置MySQL是怎么保存自增主键的自增主键的修改机制造成自增主键不连续的几种情形自增主键和UUID及随机ID比较自增主键的好处自增主键的坏处UUID及随机ID参考文章自增主键怎么设置通常，自增主键的设置... 查看详情

mybatis返回自增主键的xml方式

...insert方法，但insert方法返回的值依旧是受影响行数要获得自增主键，需要在执行insert方法后，再在实例的get主键方法获得因为自 查看详情

postgresql自增主键(代码片段)

1、自增主键；2、创建序列一、使用SERIAL自增主键createtabletest_no(idSERIALprimarykey,namevarchar(20));二、创建序列CREATESEQUENCEtest_seqSTARTWITH1INCREMENTBY1NOMINVALUENOMAXVALUECACHE1;应用：　　测试：　　　　selectnextval(‘test_seq‘);&nb 查看详情

SQL Server 中自增主键的上限

】SQLServer中自增主键的上限【英文标题】：UpperlimitforautoincrementprimarykeyinSQLServer【发布时间】：2010-09-2016:21:04【问题描述】：是多少？当SQLServer自动增量主键达到其上限时会发生什么？【问题讨论】：【参考方案1】：乔尔的回答... 查看详情

如何获取数据表中自增主键的值

...库　　当向数据库中插入一条数据的时候，默认是拿不到自增主键的值的，需要设置如下两个属性才可以拿到主键值！　　设置userGeneratedKeys属性值为true:使用自动增长的主键。使用keyProperty设置把主键值设置给哪一个属性1　　&l... 查看详情

innodb自增主键的一些问题vsmysql获得自增字段下一个值

今天发现批量插入下，自增主键不连续了。。。。。。。InnoDBAUTO_INCREMENTLockModesThissectiondescribesthebehaviorof AUTO_INCREMENT lockmodesusedtogenerateauto-incrementvalues,andhoweachlockmodeaffectsreplication.Auto-i 查看详情

mybatis自增主键返回

自增主键的返回：mysql自增主键，执行insert提交之前自动生成一个自增主键。通过mysql函数获取到刚插入记录的自增主键：LAST_INSERT_ID()是insert之后调用此函数。修改insertUser定义： 非自增主键返回：使用mysql的uuid()函数生成主... 查看详情

mybatis执行insert后马上能获取自增主键的语句写法(代码片段)

<!--keyColumnkeyPropertyuseGeneratedKeys用于在插入数据后,能直接使用user.getId()获取主键--><insertid="insert"keyColumn="Id"keyProperty="id"parameterType="com.chinacarbonal.carboncms.bean.UserRole"useGeneratedK 查看详情

如何使用 Hibernate 在 MySQL 中获取自增主键值

】如何使用Hibernate在MySQL中获取自增主键值【英文标题】：Howtogettheauto-incrementprimarykeyvalueinMySQLusingHibernate【发布时间】：2011-09-1420:15:03【问题描述】：我正在使用Hibernate访问MySQL，并且我有一个带有自动增量主键的表。每次我在... 查看详情

mycat分片表全局自增主键测试

mycat分片表全局自增主键测试mycat分片表全局自增主键测试一、全局序列号介绍在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，MyCat 提供了全局sequence，并且提供了包含本地配置和数据库配... 查看详情

sqlserver2000中如何设置自增主键?

设置自增主键的具体操作,在插入记录时,不插入自增主键,如何保证其他他字段成功插入!谢谢!在建立表的时候，把主键属性中的自增长设置为true，跨度是自动设为一的参考技术A在插入数据的时候把除自增字段外的其它字段的字... 查看详情

mycat全局系列号的学习和配置

...系列号：1.1产品介绍：在实现分库分表的情况下，数据库自增主键无法保证自增主键的全局唯一。为此，mycat提供了全局sequence，并且提供了包含本地配置和数据库配置多种实现方式。1.2模拟自增主键会出现的问题：脑瓜子有点... 查看详情

自增主键为什么会不连续

业务上的错误做法:设计依赖于自增主键的连续性.自增主键不连续的情况:测试使用的表结构CREATETABLE`t`(`id`int(11)NOTNULLAUTO_INCREMENT,`c`int(11)DEFAULTNULL,`d`int(11)DEFAULTNULL,PRIMARYKEY(`id`),UNIQUEKEY`c`(`c`))ENGINE=InnoDB;　　 查看详情

记一次mybatis返回自增主键的低级错误！(代码片段)

<insertid="insert"useGeneratedKeys="true"keyColumn="USER_ID"keyProperty="userId"parameterType="xxx.User">代码如上一切配置都没问题，然后我通过如下代码获取IDintuserId=baseDao.insert(user);结果返回的... 查看详情

关于sql优化，你需要掌握这些

1、一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15？(1)如果表的类型是MyISAM，那么是18因为MyISAM表会把自增主键的最大ID记录到数据文件里，... 查看详情

day903.自增主键不能保证连续递增-mysql实战(代码片段)

自增主键不能保证连续递增Hi，我是阿昌，今天学习记录的是关于自增主键不能保证连续递增的内容。MySql保证了主键是自增，但不相对连续；帮助开发人员快速识别每个行的唯一性，并提高查询效率。自增主... 查看详情