【数据库_Postgresql】数据库主键自增长之加序列和不加序列2种方法

最新推荐文章于 2024-07-18 17:16:08 发布

转载最新推荐文章于 2024-07-18 17:16:08 发布 · 284 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dflmg/p/6641183.html

文章标签：

#数据库 #java

本文介绍了一种在数据库中实现表主键自动递增的方法，通过创建序列和表结构关联来确保主键唯一并自动增长。此外还提供了一种不使用序列时通过子查询实现id自增的方式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

将表的主键进行序列增加之后可以在数据库层面自动主键id增长

方法如下：先建序列，然后建表关联id主键，然后添加语句，不用考虑id主键

DROP SEQUENCE
IF EXISTS "public"."sp_warehouse_id_seq";

CREATE SEQUENCE "public"."sp_warehouse_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1;

DROP TABLE
IF EXISTS "public"."sp_warehouse";

CREATE TABLE "public"."sp_warehouse" (
"id" NUMERIC (10) DEFAULT nextval(
'sp_sn_main_id_seq' :: regclass
) NOT NULL,
"whid" VARCHAR (20) COLLATE "default",
"whname" VARCHAR (50) COLLATE "default",
"province" VARCHAR (20) COLLATE "default",
"city" VARCHAR (20) COLLATE "default",
"district" CHAR (20) COLLATE "default",
"addr" TEXT COLLATE "default"
) WITH (OIDS = FALSE);

INSERT INTO "public"."sp_warehouse" (

"whid",
"whname",
"province",
"city",
"district",
"addr"
)
VALUES
(
'whid1',
'ads',
'dsf',
'sdf',
'dsf ',
'sdf'
);

不用序列的话可以在sql中id部分加一句子查询，例如：

<insert id="insertSelective" parameterType="java.util.Map">

        INSERT INTO sp_sn_main
        <trim prefix="(" suffix=")" suffixOverrides=",">
        snmasterid,
            <if test="snnumber != null">
                snnumber,
            </if>
            <if test="whid != null">
                whid,
            </if>
            <if test="itemnumber != null">
                itemnumber,
            </if>
            <if test="inboundnumber != null">
                inboundnumber,
            </if>
            <if test="outboundnumber != null">
                outboundnumber,
            </if>
            <if test="inbounddate != null">
                inbounddate,
            </if>
            <if test="outbounddate != null">
                outbounddate,
            </if>
            <if test="employeeid != null">
                employeeid,
            </if>
            <if test="status != null">
                status,
            </if>

        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
        
        (
            (
                SELECT
                    MAX (snmasterid)
                FROM
                    sp_sn_main
            ) + 1
        ),
            <if test="snnumber != null">
                #{snnumber,jdbcType=VARCHAR},
            </if>
            <if test="whid != null">
                #{whid,jdbcType=VARCHAR},
            </if>
            <if test="itemnumber != null">
                #{itemnumber,jdbcType=VARCHAR},
            </if>
            <if test="inboundnumber != null">
                #{inboundnumber,jdbcType=VARCHAR},
            </if>
            <if test="outboundnumber != null">
                #{outboundnumber,jdbcType=VARCHAR},
            </if>
            <if test="inbounddate != null">
                #{inbounddate,jdbcType=DATE},
            </if>
            <if test="outbounddate != null">
                #{outbounddate,jdbcType=DATE},
            </if>
            <if test="employeeid != null">
                #{employeeid,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                #{status,jdbcType=VARCHAR},
            </if>

        </trim>
    </insert>