MogDB/openGauss如何实现自增主键

2023年 10月 30日 55.5k 0

自增主键是我们在设计数据库表结构时经常使用的主键生成策略,主键的生成可以完全依赖数据库,无需人为干预,在新增数据的时候,我们只需要将主键的值设置为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(10+)提供了三种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掉

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论