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)