MogDB/openGauss如何實現自增主鍵

T1YSL發表於2023-11-07

自增主鍵是我們在設計資料庫表結構時經常使用的主鍵生成策略,主鍵的生成可以完全依賴資料庫,無需人為幹預,在新增資料的時候,我們只需要將主鍵的值設定為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掉



來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69990629/viewspace-2993184/,如需轉載,請註明出處,否則將追究法律責任。

相關文章