前言

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