循序渐进丨MogDB / openGauss 如何实现自增主键

2024年 2月 28日 234.0k 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(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工作。

                数据驱动,成就未来,云和恩墨,不负所托!

                云和恩墨创立于2011年,以“数据驱动,成就未来”为使命,是智能的数据技术提供商。我们致力于将数据技术带给每个行业、每个组织、每个人,构建数据驱动的智能未来。

                云和恩墨在数据承载(分布式存储、数据持续保护)、管理(数据库基础软件、数据库云管平台、数据技术服务)、加工(应用开发质量管控、数据模型管控、数字化转型咨询)和应用(数据服务化管理平台、数据智能分析处理、隐私计算)等领域为各个组织提供可信赖的产品、服务和解决方案,围绕用户需求,持续为客户创造价值,激发数据潜能,为成就未来敏捷高效的数字世界而不懈努力。

                相关文章

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

                发布评论