<< 返回文章列表

MogDB / openGauss 如何实现自增主键

2024年2月27日
M
o
g
D
B
,
o
p
e
n
G
a
u
s
s
,
,
,
阎书利
71

概述

自增主键是我们在设计数据库表结构时经常使用的主键生成策略,主键的生成可以完全依赖数据库,无需人为干预,在新增数据的时候,我们只需要将主键的值设置为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 databaseMogDB=
正确的使用方式如下:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
MogDB=# create database db_mysql with dbcompatibility ='B';CREATE DATABASEMogDB=# \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_INCREMENTIndexes: "test_create_autoinc_source_pkey" PRIMARY KEY, btree (id) TABLESPACE pg_default

--->插入值进行验证
db_mysql=# INSERT INTO test_create_autoinc_source VALUES(DEFAULT);INSERT 0 1db_mysql=# INSERT INTO test_create_autoinc_source VALUES(DEFAULT);INSERT 0 1db_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 | 0Owned by: public.test_serial_a1.id
--->插入值进行验证
MogDB=# insert into test_serial_a1 values(DEFAULT,'no1'); INSERT 0 1MogDB=# insert into test_serial_a1 values(DEFAULT,'no1');INSERT 0 1MogDB=# 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 1MogDB=# insert into test_serial_a2 values(DEFAULT,'no1');INSERT 0 1MogDB=# 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_seqSTART WITH 1INCREMENT BY 1NO MINVALUENO MAXVALUECACHE 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 TABLEMogDB=# \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 1MogDB=# insert into test_bbb values(DEFAULT,'no2');INSERT 0 1MogDB=# 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掉。