南大通用GBase 8a的数字类型int,bigint,decimal长度限制测试

根据测试结果,GBase 8a数据库的数字类型长度,int和bigint并没有做【严格】的限制,除非超过了类型允许的最大长度。算上负数的标志,int最长11位,bigint是20位。decimal做了严格限制,超了长度会报错。

参考

GBase 8a相同数据在不同数字字段类型下的磁盘占用情况验证

GBase 8a数据库集群基本技术指标

测试表

长度为3的int类型。

gbase> create table t1(id int(3));
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> desc t1;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | int(3) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.01)

测试int(3)

分别insert长度为3,4,9,10的数字,均正常入库。长度为11的,报超过数字范围的错误。而2的31次方,长度为10,算上负号,正好11位。

gbase> insert into t1 values(123);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t1 values(1234);
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> insert into t1 values(123456789);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t1 values(1234567890);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t1 values(12345678901);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
gbase> select power(2,31);
+-------------+
| power(2,31) |
+-------------+
|  2147483648 |
+-------------+
1 row in set (Elapsed: 00:00:00.00)

测试int(3)加载

可以正常加载,不会因为长度超过3而报错。

gbase> select * from t1;
+------------+
| id         |
+------------+
|        123 |
|       1234 |
|  123456789 |
| 1234567890 |
+------------+
4 rows in set (Elapsed: 00:00:00.02)

gbase> load data infile 'ftp://gbase:gbase1234@10.0.2.101/t1_2.txt' into table t1;
Query OK, 10 rows affected (Elapsed: 00:00:00.40)
Task 21 finished, Loaded 10 records, Skipped 0 records

gbase> select * from t1;
+------------+
| id         |
+------------+
|        123 |
|       1234 |
|  123456789 |
| 1234567890 |
|      13700 |
|       7748 |
|      27384 |
|      32486 |
|      10580 |
|       1371 |
|      10886 |
|      28139 |
|      12636 |
|      25419 |
+------------+
14 rows in set (Elapsed: 00:00:00.01)

测试默认int

长度位11位,要包含负号。

gbase> create table t2(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.15)

gbase> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> insert into t2 values(-1234567890);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

Bigint测试

最长19位,算上负号是20位。

gbase> create table t3(id bigint);
Query OK, 0 rows affected (Elapsed: 00:00:00.14)

gbase> desc t3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> insert into t3 values (-1234567890123456789);
Query OK, 1 row affected (Elapsed: 00:00:00.10)

gbase> insert into t3 values (-12345678901234567890);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
gbase>

Decimal类型测试

decimal严格做了长度限制,负号不在范围内。

gbase> create table t4(id decimal(5));
Query OK, 0 rows affected (Elapsed: 00:00:00.15)

gbase> insert into t4 values(1234);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t4 values(12345);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t4 values(123456);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

gbase> insert into t4 values(-12345);
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> select * from t4;
+--------+
| id     |
+--------+
|   1234 |
|  12345 |
| -12345 |
+--------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> desc t4;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | decimal(5,0) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)

总结

对int、bigint类型,只要不超过其范围限制,从实现看并不对其长度做【严格】限制。decimal做了严格限制。