首页 » OceanBase » 如何查看Oceanbase Table隐藏自增列值？

如何查看Oceanbase Table隐藏自增列值？

  • 2024/01/30
  • OceanBase
  • 105 views
  • 如何查看Oceanbase Table隐藏自增列值？已关闭评论

众所周知，在MySQL中通常建议增加主键，如MySQL的innoDB存储引擎一种索引组织表（B+tree）的形式，如果未指定主键时？数据库会自动创建隐藏PK索引（6字节）做为CLUSTER PRIMARY KEY，像达梦数据库默认也是这种B+Tree组织形式。OceanBase企业租户目前有两种兼容模式，兼容 Oracle 或 MySQL。OceanBase 集群默认会有一个租户（兼容 MySQL），租户名是 sys ，里面存储的是集群元数据信息，集群内部管理需要，不建议建表存储数据。OceanBase社区版仅支持mysql版。OceanBase MySQL兼容没有使用innoDB存储引擎，这点与中兴与万里开源的数据库有本质区别， OB存储引擎使用自研的分层LSMTree结构，数据分为2部分：基线数据(SSTable)在磁盘和增量数据(MemTable)在内存。 Oracle数据库通常是一种Heap Table组织形式（非IOT），Table上会有一个rowid隐藏列，MySQL要求有主键，对于Oceanbase数据库，虽然是一种LSMTree,但是在创建表时，无论是Oracle租户还是MySQL租户，同样在创建表时如果未指定PK， 会自动创建一个隐藏的自增列PK。这里演示如何查询隐藏自增列值?

— Oceanbase V3.2.4

Oceanbase for mysql租户

obclient [test]> select tenant_id,tenant_name,zone_list,info,compatibility_mode from oceanbase.__all_tenant;
+-----------+-------------+-------------------+---------------+--------------------+
| tenant_id | tenant_name | zone_list         | info          | compatibility_mode |
+-----------+-------------+-------------------+---------------+--------------------+
|         1 | sys         | zone1;zone2;zone3 | system tenant |                  0 |
|      1001 | orauser     | zone1;zone2;zone3 |               |                  1 |
+-----------+-------------+-------------------+---------------+--------------------+
2 rows in set (0.003 sec)


obclient [test]> SHOW TENANT;
+---------------------+
| Current_tenant_name |
+---------------------+
| sys                 |
+---------------------+
1 row in set (0.004 sec)

obclient [(none)]> use test;
Database changed
obclient [test]> create table test2(name varchar(100));
Query OK, 0 rows affected (0.131 sec)

obclient [test]> insert into test2 values('anbob.com');
Query OK, 1 row affected (0.032 sec)

obclient [test]> commit;
Query OK, 0 rows affected (0.001 sec)

obclient [test]> desc test2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.004 sec)

Note:
创建了无指定PK的表.

查询隐藏列

obclient [test]> select table_id,TABLE_NAME from sys.ALL_VIRTUAL_TABLE_REAL_AGENT where UPPER(TABLE_NAME) LIKE 'TEST2';
+---------------+------------+
| table_id      | TABLE_NAME |
+---------------+------------+
| 1099511677805 | test2      |
+---------------+------------+
1 row in set (0.008 sec)

obclient [test]> SELECT TABLE_ID,COLUMN_ID,COLUMN_NAME,IS_HIDDEN,AUTOINCREMENT FROM oceanbase.__all_virtual_column WHERE TABLE_ID=1099511677805;
+---------------+-----------+----------------+-----------+---------------+
| TABLE_ID      | COLUMN_ID | COLUMN_NAME    | IS_HIDDEN | AUTOINCREMENT |
+---------------+-----------+----------------+-----------+---------------+
| 1099511677805 |         1 | __pk_increment |         1 |             1 |
| 1099511677805 |        16 | name           |         0 |             0 |
+---------------+-----------+----------------+-----------+---------------+
2 rows in set (0.100 sec)

obclient [test]>  select __pk_increment from test2;
ERROR 1054 (42S22): Unknown column '__pk_increment' in 'field list'
obclient [test]> exit

Note：
及时是sys用户当前也没有办法查询隐藏列，注意隐藏的自增列叫__pk_increment。如果查询需要创建一个指定用户__oceanbase_inner_drc_user。

创建__oceanbase_inner_drc_user用户

obclient [(none)]>  create user __oceanbase_inner_drc_user identified by 'root';
Query OK, 0 rows affected (0.114 sec)

obclient [(none)]> grant select on *.* to __oceanbase_inner_drc_user;
Query OK, 0 rows affected (0.085 sec)

[admin@OceanBase1 ~]$ obclient -h172.xxx.xxx.107 -u__oceanbase_inner_drc_user@sys -P2881 -p -cA -p'root'
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 3221536586
Server version: OceanBase 3.2.4.1 (r101000052023010822-346aa35c32e99d1b82d713f75f0072c45bdf7aab) (Built Jan  8 2023 22:52:43)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> use test
Database changed
obclient [test]> select __pk_increment from test2;
+----------------+
| __pk_increment |
+----------------+
|              1 |
+----------------+
1 row in set (0.015 sec)

自增？不会补空?

# session 2

obclient [test]> select * from test2;
+-----------+
| name      |
+-----------+
| anbob.com |
+-----------+
1 row in set (0.001 sec)

obclient [test]> insert into test2 values('a');
Query OK, 1 row affected (0.003 sec)

obclient [test]> insert into test2 values('a');
Query OK, 1 row affected (0.004 sec)

obclient [test]> commit;
Query OK, 0 rows affected (0.001 sec)

obclient [test]> delete from  test2 where name='a';
Query OK, 2 rows affected (0.006 sec)

obclient [test]> commit;
Query OK, 0 rows affected (0.001 sec)

obclient [test]> insert into test2 values('a');
Query OK, 1 row affected (0.003 sec)

obclient [test]> select * from test2;
+-----------+
| name      |
+-----------+
| anbob.com |
| a         |
+-----------+
2 rows in set (0.001 sec)

## session 1
obclient [test]> select name,__pk_increment from test2;
+-----------+----------------+
| name      | __pk_increment |
+-----------+----------------+
| anbob.com |              1 |
| a         |              4 |
+-----------+----------------+
2 rows in set (0.003 sec)

Note:
注意__pk_increment列在我们insert delete后再insert没有复用原来的值。

Oceanbase for ORACLE租户

obclient [ANBOB]> desc TEST200
    -> ;
+----------------+---------------+------+-----+---------+-------+
| FIELD          | TYPE          | NULL | KEY | DEFAULT | EXTRA |
+----------------+---------------+------+-----+---------+-------+
| OWNER          | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| OBJECT_NAME    | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| SUBOBJECT_NAME | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
| OBJECT_ID      | NUMBER        | YES  | NULL | NULL    | NULL  |
| DATA_OBJECT_ID | NUMBER        | YES  | NULL | NULL    | NULL  |
| OBJECT_TYPE    | VARCHAR2(23)  | YES  | NULL | NULL    | NULL  |
| CREATED        | DATE          | YES  | NULL | NULL    | NULL  |
| LAST_DDL_TIME  | DATE          | YES  | NULL | NULL    | NULL  |
| TIMESTAMP      | VARCHAR2(256) | YES  | NULL | NULL    | NULL  |
| STATUS         | VARCHAR2(7)   | YES  | NULL | NULL    | NULL  |
| TEMPORARY      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
| GENERATED      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
| SECONDARY      | VARCHAR2(1)   | YES  | NULL | NULL    | NULL  |
| NAMESPACE      | NUMBER        | YES  | NULL | NULL    | NULL  |
| EDITION_NAME   | VARCHAR2(128) | YES  | NULL | NULL    | NULL  |
+----------------+---------------+------+-----+---------+-------+
15 rows in set (0.005 sec)

obclient [ANBOB]> select tenant_id, table_id,TABLE_NAME from sys.ALL_VIRTUAL_TABLE_REAL_AGENT where UPPER(TABLE_NAME) LIKE 'TEST200';
+-----------+------------------+------------+
| TENANT_ID | TABLE_ID         | TABLE_NAME |
+-----------+------------------+------------+
|      1001 | 1100611139453778 | TEST200    |
+-----------+------------------+------------+
1 row in set (0.002 sec)
######################################################################
SQL> @dec 1100611139453778
                                DEC                  HEX
----------------------------------- --------------------
            1100611139453778.000000        3E9000000C352  -- tenant_id+

SQL> @dec 1001
                                DEC                  HEX
----------------------------------- --------------------
                        1001.000000                  3E9
######################################################################
obclient [test]> SELECT TABLE_ID,COLUMN_ID,COLUMN_NAME,IS_HIDDEN,AUTOINCREMENT FROM oceanbase.__all_virtual_column WHERE TABLE_ID=1100611139453778;
+------------------+-----------+----------------+-----------+---------------+
| TABLE_ID         | COLUMN_ID | COLUMN_NAME    | IS_HIDDEN | AUTOINCREMENT |
+------------------+-----------+----------------+-----------+---------------+
| 1100611139453778 |         1 | __pk_increment |         1 |             1 |
| 1100611139453778 |        16 | OWNER          |         0 |             0 |
| 1100611139453778 |        17 | OBJECT_NAME    |         0 |             0 |
| 1100611139453778 |        18 | SUBOBJECT_NAME |         0 |             0 |
| 1100611139453778 |        19 | OBJECT_ID      |         0 |             0 |
| 1100611139453778 |        20 | DATA_OBJECT_ID |         0 |             0 |
| 1100611139453778 |        21 | OBJECT_TYPE    |         0 |             0 |
| 1100611139453778 |        22 | CREATED        |         0 |             0 |
| 1100611139453778 |        23 | LAST_DDL_TIME  |         0 |             0 |
| 1100611139453778 |        24 | TIMESTAMP      |         0 |             0 |
| 1100611139453778 |        25 | STATUS         |         0 |             0 |
| 1100611139453778 |        26 | TEMPORARY      |         0 |             0 |
| 1100611139453778 |        27 | GENERATED      |         0 |             0 |
| 1100611139453778 |        28 | SECONDARY      |         0 |             0 |
| 1100611139453778 |        29 | NAMESPACE      |         0 |             0 |
| 1100611139453778 |        30 | EDITION_NAME   |         0 |             0 |
+------------------+-----------+----------------+-----------+---------------+
16 rows in set (0.034 sec)

obclient [ANBOB]> create table test301(id int primary key,name varchar(10));
Query OK, 0 rows affected (0.147 sec)

obclient [ANBOB]> select table_id,TABLE_NAME from sys.ALL_VIRTUAL_TABLE_REAL_AGENT where UPPER(TABLE_NAME) LIKE 'TEST301';
+------------------+------------+
| TABLE_ID         | TABLE_NAME |
+------------------+------------+
| 1100611139453797 | TEST301    |
+------------------+------------+
1 row in set (0.003 sec)

obclient [ANBOB]> select tenant_id, table_id,TABLE_NAME from sys.ALL_VIRTUAL_TABLE_REAL_AGENT where UPPER(TABLE_NAME) LIKE 'TEST301';
+-----------+------------------+------------+
| TENANT_ID | TABLE_ID         | TABLE_NAME |
+-----------+------------------+------------+
|      1001 | 1100611139453797 | TEST301    |
+-----------+------------------+------------+
1 row in set (0.006 sec)

obclient [test]> SELECT TABLE_ID,COLUMN_ID,COLUMN_NAME,IS_HIDDEN,AUTOINCREMENT FROM oceanbase.__all_virtual_column WHERE TABLE_ID=1100611139453797;
+------------------+-----------+-------------+-----------+---------------+
| TABLE_ID         | COLUMN_ID | COLUMN_NAME | IS_HIDDEN | AUTOINCREMENT |
+------------------+-----------+-------------+-----------+---------------+
| 1100611139453797 |        16 | ID          |         0 |             0 |
| 1100611139453797 |        17 | NAME        |         0 |             0 |
+------------------+-----------+-------------+-----------+---------------+
2 rows in set (0.034 sec)

Note:
oracle租户一样未创建索引时，会自动创建隐藏自增列，而如果手动指定PK,则不会。

Summary:
Oceanbase2种模式在创建表里如果未明确PK列，会自动创建隐藏自增列，需要创建专用用户__oceanbase_inner_drc_user 查看表上的隐藏列值。

打赏

, ,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：