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

前言

近期,對產品進行數據庫由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自增主鍵用法內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: