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這個方法，屬於自增序列中默認提供的方法之一，自增序列提供瞭常見的下面幾種方法提供參考

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！

推薦閱讀：

• postgresql 中的序列nextval詳解
• PostgreSQL三種自增列sequence,serial,identity的用法區別
• postgresql 導入數據庫表並重設自增屬性的操作
• Oracle中的序列SEQUENCE詳解
• PostgreSQL Sequence序列的使用詳解