PostgreSQL自增主鍵用法及在mybatis中的使用教學

2022-10-18 14:00:12

前言

近期，對產品進行資料庫由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 【第一步中的最大值或者加一點】;

到此這篇關於PostgreSQL自增主鍵用法及在mybatis中使用的文章就介紹到這了,更多相關PostgreSQL自增主鍵用法內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！