南大通用GBase 8a 在V95版本对表自增列auto_increment的支持样例

发表于 作者 laozizhu

GBase 8a数据库集群，从一开始就不支持主键，外键，唯一键等，同时因为技术原因，也没有支持自增列。 在V95版本里，第一次正式支持了自增列，但也有一些限制，特别是不保证数值连续。如果用户非常关心连续性，请在原始数据里包含计算好的列值。

参考

GBase 8a自增列不允许insert报错auto_increment column should not be specified in insert

自增列关键字 auto_increment

如下是一个测试表，其中自增列，比如是一个主键 ，所以要和 primary key auto_increment 一起使用。否则会报错。

gbase> create table t_autoinc(id int primary key auto_increment, name varchar(100));
Query OK, 0 rows affected (Elapsed: 00:00:00.83)

gbase> show create table t_autoinc;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                  |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_autoinc | CREATE TABLE "t_autoinc" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "name" varchar(100) DEFAULT NULL,
  PRIMARY KEY ("id")
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> create table t_autoinc(id int auto_increment, name varchar(100));
ERROR 1050 (42S01): Table 't_autoinc' already exists
gbase> create table t_autoinc2(id int auto_increment, name varchar(100));
ERROR 1702 (HY000): gcluster table error: Incorrect table definition; there can be only one auto column and it must be defined as a key.

集群规模

是3个节点

[gbase@localhost ~]$ gcadmin
CLUSTER STATE:         ACTIVE
VIRTUAL CLUSTER MODE:  NORMAL

=============================================================
|           GBASE COORDINATOR CLUSTER INFORMATION           |
=============================================================
|   NodeName   | IpAddress  | gcware | gcluster | DataState |
-------------------------------------------------------------
| coordinator1 | 10.0.2.102 |  OPEN  |   OPEN   |     0     |
-------------------------------------------------------------
| coordinator2 | 10.0.2.202 |  OPEN  |   OPEN   |     0     |
-------------------------------------------------------------
| coordinator3 | 10.0.2.203 |  OPEN  |   OPEN   |     0     |
-------------------------------------------------------------
=========================================================================================================
|                                    GBASE DATA CLUSTER INFORMATION                                     |
=========================================================================================================
| NodeName |                IpAddress                 | DistributionId | gnode | syncserver | DataState |
---------------------------------------------------------------------------------------------------------
|  node1   |                10.0.2.102                |       3        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node2   |                10.0.2.202                |       3        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------
|  node3   |                10.0.2.203                |       3        | OPEN  |    OPEN    |     0     |
---------------------------------------------------------------------------------------------------------

insert value插入数据

可以看到id列确实是自增的，但数值并没有连续。原因就是数据库是3个节点，而insert values 方式插入数据是选择一个节点，所以本来分配个其它2个节点的id编号，丢失了。

gbase> select * from t_autoinc;
Empty set (Elapsed: 00:00:00.34)

gbase> insert into t_autoinc (name) values ('First'),('Second'),('Third');
Query OK, 3 rows affected (Elapsed: 00:00:00.55)
Records: 3  Duplicates: 0  Warnings: 0

gbase> select * from t_autoinc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  4 | Second |
|  7 | Third  |
+----+--------+
3 rows in set (Elapsed: 00:00:00.03)

获得自增列的值

只能得到上次操作的自增列的值，从每个节点返回last_insert_id，选择最小的返回。所以对于insert values单值时最有效。<=0表示无效。

如下是3计算节点集群的效果。

gbase> insert into t3_inc (name) values('t3_first');
Query OK, 1 row affected (Elapsed: 00:00:00.13)

gbase> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> insert into t3_inc (name) values('t3_first'),('333'),('444');
Query OK, 3 rows affected (Elapsed: 00:00:00.10)

gbase> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t3_inc;
+----+----------+
| id | name     |
+----+----------+
|  1 | t3_first |
|  2 | t3_first |
|  3 | t3_first |
|  6 | 333      |
|  9 | 444      |
+----+----------+
5 rows in set (Elapsed: 00:00:00.01)

load 加载方式入库

构造一份数据，目前3个节点，我们先构造个3的整倍数。

cat /home/gbase/inc.txt
AAAA
BBBB
CCCC

加载，看结果

其中 id列是自增列，不允许加载，所以我们用table_fields 指定我们只加载name列。可以看到加载的本次数据id是自增的。

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/inc.txt' into table t_autoinc fields terminated by ',' table_fields 'name';
Query OK, 3 rows affected (Elapsed: 00:00:01.26)
Task 10245 finished, Loaded 3 records, Skipped 0 records

gbase> select * from t_autoinc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  4 | Second |
|  7 | Third  |
|  8 | BBBB   |
|  9 | CCCC   |
| 10 | AAAA   |
+----+--------+
6 rows in set (Elapsed: 00:00:00.03)

构造不是3的整倍数的数据，加载多次

可以看到ID确实是增加的，但及时在一次加载内部，也不保证是连续的。

cat /home/gbase/inc.txt
AAAA
BBBB
CCCC
DDDDDD

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/inc.txt' into table t_autoinc fields terminated by ',' table_fields 'name';
Query OK, 4 rows affected (Elapsed: 00:00:01.34)
Task 10246 finished, Loaded 4 records, Skipped 0 records

gbase> select * from t_autoinc;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  4 | Second |
|  7 | Third  |
|  8 | BBBB   |
| 11 | CCCC   |
|  9 | CCCC   |
| 12 | AAAA   |
| 15 | DDDDDD |
| 10 | AAAA   |
| 13 | BBBB   |
+----+--------+
10 rows in set (Elapsed: 00:00:00.03)

gbase> load data infile 'sftp://gbase:gbase1234@10.0.2.102/home/gbase/inc.txt' into table t_autoinc fields terminated by ',' table_fields 'name';
Query OK, 4 rows affected (Elapsed: 00:00:01.32)
Task 10247 finished, Loaded 4 records, Skipped 0 records

gbase> select * from t_autoinc;
+----+--------+
| id | name   |
+----+--------+
|  9 | CCCC   |
| 12 | AAAA   |
| 15 | DDDDDD |
| 17 | CCCC   |
| 10 | AAAA   |
| 13 | BBBB   |
| 18 | AAAA   |
| 21 | DDDDDD |
|  1 | First  |
|  4 | Second |
|  7 | Third  |
|  8 | BBBB   |
| 11 | CCCC   |
| 16 | BBBB   |
+----+--------+
14 rows in set (Elapsed: 00:00:00.04)

gbase> select * from t_autoinc order by id;
+----+--------+
| id | name   |
+----+--------+
|  1 | First  |
|  4 | Second |
|  7 | Third  |
|  8 | BBBB   |
|  9 | CCCC   |
| 10 | AAAA   |
| 11 | CCCC   |
| 12 | AAAA   |
| 13 | BBBB   |
| 15 | DDDDDD |
| 16 | BBBB   |
| 17 | CCCC   |
| 18 | AAAA   |
| 21 | DDDDDD |
+----+--------+
14 rows in set (Elapsed: 00:00:00.37)

加载相关其它信息，请参考

http://www.gbase8.cn/?tag=%e5%8a%a0%e8%bd%bd

注意事项

  • 集群层的自增列，不能指定起始值，基值，步长等参数，由集群自动维护。单机可以。
  • 自增列必须是整数类型，tinyint,smallint,int,bigint中的一种。
  • 集群自增列，不保证连续。
  • 自增列的表，不支持insert 并发，从表锁升级为独占写锁，避免并发自增列的值重复。
  • 每次DML操作前，都会获得所有节点自增列的当前最大值，估计是保存在元数据里。类似从gnode的tables表auto_increment列查询到。
[gbase@gbase_rh7_001 gcluster]$ gncli

GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> select table_name,auto_increment from information_schema.tables where auto_increment is not null;
+------------+----------------+
| table_name | auto_increment |
+------------+----------------+
| time_zone  |              0 |
| t2_inc_n1  |              1 |
| t2_inc_n3  |              0 |
| t3_inc_n1  |              1 |
| t3_inc_n3  |              0 |
| t_inc_n1   |            101 |
| t_inc_n3   |              0 |
+------------+----------------+
7 rows in set (Elapsed: 00:00:00.19)
Post Views: 1,187

相关文章:

  1. 南大通用GBase 8a集群支持分区表功能使用样例
  2. 南大通用GBase 8a集群常见故障V95版本gcadmin命令卡住
  3. 南大通用GBase 8a集群V95版本gcwar服务snapshot文件功能说明
  4. 南大通用GBase 8a数据库集群V95版本相对V8版本变动内容汇总
  5. 南大通用GBase 8a数据库集群V95版本启动和停止VC内的所有数据节点服务
  6. 南大通用GBase 8a查看每个节点的编号 nodeid
  7. 南大通用GBase 8a在V95版本支持session事务内内对一张表的多次更新变动DML操作
  8. 南大通用GBase 8a V9版本新功能镜像库，镜像表的使用样例
  9. 南大通用GBase 8a V95版本表空间配额使用样例
  10. 南大通用GBase 8a V95版本删除VC的操作手顺