GBase 8a 在V95版本对表自增列的支持样例

 

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

 

自增列关键字 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)

 

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/1073