概述

自增主键是我们在设计数据库表结构时经常使用的主键生成策略，主键的生成可以完全依赖数据库，无需人为干预，在新增数据的时候，我们只需要将主键的值设置为default，数据库就会为我们自动生成一个主键值。

MySQL 主键自增使用AUTO_INCREMENT关键字，PostgreSQL 自增使用SERIAL关键字或者序列。而 MogDB openGauss 里兼容两种语法。AUTO_INCREMENT在 MogDB-3.1.0 openGauss-5.0.0以上适配。

下文会针对 MogDB openGauss 里几种自增主键的实现进行一个简单的验证。

一、MySQL 的方式（AUTO_INCREMENT）

注意，AUTO_INCREMENT功能，只有在 MogDB openGauss 的B兼容模式下才可以使用，否则将会有如下提示：



MogDB=#  SELECT current_database(); 


 current_database 


------------------


 postgres


(1 row)







MogDB=# \l postgres


                                 List of databases


   Name   | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility 


----------+-------+----------+---------+-------+-------------------+---------------


 postgres | om5   | UTF8     | C       | C     |                   | A


(1 row)







MogDB=# CREATE TABLE test_create_autoinc(id bool auto_increment primary key, name varchar(200),a int) auto_increment=1;


ERROR:  auto_increment is supported only in B-format database


MogDB=# 



正确的使用方式如下：



MogDB=# create database db_mysql with dbcompatibility ='B';


CREATE DATABASE


MogDB=# \c db_mysql 


Non-SSL connection (SSL connection is recommended when requiring high-security)


You are now connected to database "db_mysql" as user "om5".


db_mysql=# CREATE TABLE test_create_autoinc_source(id int auto_increment primary key) AUTO_INCREMENT = 100;


NOTICE:  CREATE TABLE will create implicit sequence "test_create_autoinc_source_id_seq" for serial column "test_create_autoinc_source.id"


NOTICE:  CREATE TABLE  PRIMARY KEY will create implicit index "test_create_autoinc_source_pkey" for table "test_create_autoinc_source"


CREATE TABLE







db_mysql=# \d test_create_autoinc_source


 Table "public.test_create_autoinc_source"


 Column |  Type   |        Modifiers        


--------+---------+-------------------------


 id     | integer | not null AUTO_INCREMENT


Indexes:


    "test_create_autoinc_source_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default












--->插入值进行验证







db_mysql=# INSERT INTO test_create_autoinc_source VALUES(DEFAULT);


INSERT 0 1


db_mysql=# INSERT INTO test_create_autoinc_source VALUES(DEFAULT);


INSERT 0 1


db_mysql=# SELECT id FROM test_create_autoinc_source ORDER BY 1;


 id  


-----


 100


 101


(2 rows)







二、PostgreSQL 的方式（SERIAL）

PostgreSQL（v10以上版本）提供了三种serial类型：smallserial, serial, bigserial，他不是真正的类型，而是在创建唯一标识符列的标志以方便使用。bigserial会创建一个bigint类型的自增；serial用以创建一个int类型的自增；smallserial用以创建一个smallint类型的自增。这几种类型在 MogDB openGauss 里都是支持的。serial的MAXVALUE=9223372036854775807，起始值为1。自增列的默认值是nextval(‘table_name_seq’::regclass)。

方式一



MogDB=# create table test_serial_a1(


id serial,


name character varying(256),


constraint pk_test_serial_id primary key( id)


);


NOTICE:  CREATE TABLE will create implicit sequence "test_serial_a1_id_seq" for serial column "test_serial_a1.id"


NOTICE:  CREATE TABLE  PRIMARY KEY will create implicit index "pk_test_serial_id" for table "test_serial_a1"


CREATE TABLE







MogDB=# \d test_serial_a1


                                 Table "public.test_serial_a1"


 Column |          Type          |                          Modifiers                          


--------+------------------------+-------------------------------------------------------------


 id     | integer                | not null default nextval('test_serial_a1_id_seq'::regclass)


 name   | character varying(256) | 


Indexes:


    "pk_test_serial_id" PRIMARY KEY, btree (id) TABLESPACE pg_default







MogDB=# \d test_serial_a1_id_seq


     Sequence "public.test_serial_a1_id_seq"


    Column     |  Type   |         Value         


---------------+---------+-----------------------


 sequence_name | name    | test_serial_a1_id_seq


 last_value    | bigint  | 2


 start_value   | bigint  | 1


 increment_by  | bigint  | 1


 max_value     | bigint  | 9223372036854775807


 min_value     | bigint  | 1


 cache_value   | bigint  | 1


 log_cnt       | bigint  | 31


 is_cycled     | boolean | f


 is_called     | boolean | t


 uuid          | bigint  | 0


Owned by: public.test_serial_a1.id







--->插入值进行验证







MogDB=# insert into test_serial_a1 values(DEFAULT,'no1'); 


INSERT 0 1


MogDB=# insert into test_serial_a1 values(DEFAULT,'no1');


INSERT 0 1


MogDB=# SELECT * FROM test_serial_a1;


 id | name 


----+------


  1 | no1


  2 | no1


(2 rows)










方式二



MogDB=# create table test_serial_a2(


id serial PRIMARY KEY,


name character varying(256)


);


NOTICE:  CREATE TABLE will create implicit sequence "test_serial_a2_id_seq" for serial column "test_serial_a2.id"


NOTICE:  CREATE TABLE  PRIMARY KEY will create implicit index "test_serial_a2_pkey" for table "test_serial_a2"


CREATE TABLE







MogDB=# \d test_serial_a2


                                 Table "public.test_serial_a2"


 Column |          Type          |                          Modifiers                          


--------+------------------------+-------------------------------------------------------------


 id     | integer                | not null default nextval('test_serial_a2_id_seq'::regclass)


 name   | character varying(256) | 


Indexes:


    "test_serial_a2_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

















--->插入值进行验证







MogDB=# insert into test_serial_a2 values(DEFAULT,'no1');


INSERT 0 1


MogDB=# insert into test_serial_a2 values(DEFAULT,'no1');


INSERT 0 1


MogDB=# SELECT * FROM test_serial_a2;


 id | name 


----+------


  1 | no1


  2 | no1


(2 rows)



这两种方法用的是 
PostgreSQL 的
serial类型实现自增，
drop表的时候指定的序列也会
drop掉。

三、基于序列

基于序列的方式其实和第二种的基于serial的思路一样，一般的主键表，没有使用serial类型，那么我们可以通过创建序列，并在建表的时候指定默认值字段为序列的nextval来实现。

1.手动创建序列



MogDB=# CREATE SEQUENCE test_aaa_id_seq


START WITH 1


INCREMENT BY 1


NO MINVALUE


NO MAXVALUE


CACHE 1;


CREATE SEQUENCE







MogDB=# \d test_aaa_id_seq


       Sequence "public.test_aaa_id_seq"


    Column     |  Type   |        Value        


---------------+---------+---------------------


 sequence_name | name    | test_aaa_id_seq


 last_value    | bigint  | 1


 start_value   | bigint  | 1


 increment_by  | bigint  | 1


 max_value     | bigint  | 9223372036854775807


 min_value     | bigint  | 1


 cache_value   | bigint  | 1


 log_cnt       | bigint  | 0


 is_cycled     | boolean | f


 is_called     | boolean | f


 uuid          | bigint  | 0

2.创建主键表



MogDB=#  create table test_bbb (


id integer PRIMARY KEY default nextval('test_aaa_id_seq'::regclass),


name character varying(128)


);


NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_bbb_pkey" for table "test_bbb"


CREATE TABLE


MogDB=# \d test_bbb


                                 Table "public.test_bbb"


 Column |          Type          |                       Modifiers                       


--------+------------------------+-------------------------------------------------------


 id     | integer                | not null default nextval('test_aaa_id_seq'::regclass)


 name   | character varying(128) | 


Indexes:


    "test_bbb_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default












--->插入值进行验证


MogDB=# insert into test_bbb values(DEFAULT,'no1');


INSERT 0 1


MogDB=# insert into test_bbb values(DEFAULT,'no2');


INSERT 0 1


MogDB=# select * from test_bbb;


 id | name 


----+------


  1 | no1


  2 | no2


(2 rows)

也可以创建完表、创建完序列后，使用alter语句，将序列赋值给主键，语句如下所示：



alter table test_aaa alter column id set default nextval('test_aaa_id_seq');

这种自行使用序列的方法在drop表的时候序列不会随着drop掉。

“

作者简介

/ Author Introduction

阎书利 / 云和恩墨PG技术顾问

PostgreSQL ACE，《快速掌握 PostgreSQL 版本新特性》一书副主编，中国PG分会认证讲师，PGfans 2021年度MVP，Gauss松鼠会2021年度优秀会员，拥有PGCM、OCP(MySQL)等十多项数据库认证。目前主要从事于 PostgreSQL、openGauss / MogDB 的运维以及去O工作。

ITPub博客

循序渐进丨MogDB 数据库如何实现自增主键

最新文章