«

PostgreSQL自增主键及在mybatis中使用的方法是什么

时间:2024-8-2 10:48     作者:韩俊     分类: Mysql


这篇“PostgreSQL自增主键及在mybatis中使用的方法是什么”文章的知识点大部分人都不太理解,所以小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“PostgreSQL自增主键及在mybatis中使用的方法是什么”文章吧。

    什么是自增主键?

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

    为什么需要自增主键?

      自增主键可让主键索引保持递增顺序插入,因此避免了页分开;

      相较于其他类型(比如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 【第一步中的最大值或者加一点】。

    标签: mysql PostgreSQL

    热门推荐