OB怎么读取无主键表的自增隐藏列

概述

OB无主键表默认会创建一个隐藏自增列__pk_increment作为主键,但普通用户无法读取这一列的值,需要创建一个内部用户 __oceanbase_inner_drc_user，然后用这个内部用户去查询内部主键（__pk_increment）。

创建用户__oceanbase_inner_drc_user

MYSQL 租户

CREATE USER __oceanbase_inner_drc_user IDENTIFIED BY '<password>';
GRANT SELECT ON *.* TO __oceanbase_inner_drc_user;

ORACLE租户

CREATE USER '__OCEANBASE_INNER_DRC_USER'@'%' IDENTIFIED BY oceanbase;
GRANT CREATE SESSION TO '__OCEANBASE_INNER_DRC_USER';
GRANT SELECT ANY DICTIONARY TO '__OCEANBASE_INNER_DRC_USER';
GRANT SELECT ANY TABLE TO '__OCEANBASE_INNER_DRC_USER';

测试读取__pk_increment

MySQL [oceanbase]> select table_id from __all_virtual_table where table_name='RTEST1';
+------------------+
| table_id         |
+------------------+
| 1100611139453972 |
+------------------+
1 row in set (0.024 sec)


select column_id,column_name,rowkey_position,index_position,order_in_rowkey,is_hidden,data_type,data_length from __all_virtual_column where table_id=1100611139453972;

+-----------+----------------+-----------------+----------------+-----------------+-----------+-----------+-------------+
| column_id | column_name    | rowkey_position | index_position | order_in_rowkey | is_hidden | data_type | data_length |
+-----------+----------------+-----------------+----------------+-----------------+-----------+-----------+-------------+
|         1 | __pk_increment |               1 |              0 |               0 |         1 |        10 |           0 |
|        16 | ID             |               0 |              0 |               0 |         0 |        15 |           0 |
|        17 | C              |               0 |              0 |               0 |         0 |        22 |         100 |
+-----------+----------------+-----------------+----------------+-----------------+-----------+-----------+-------------+
3 rows in set (0.030 sec)

使用用户u__OCEANBASE_INNER_DRC_USER登录
obclient -h192.168.56.36 -P2883 -u__OCEANBASE_INNER_DRC_USER@test#obdemo -poceanbase -c


select T."__pk_increment",T.* from test.rtest1 T where id<10;

查询__pk_increment
obclient [__OCEANBASE_INNER_DRC_USER]> select T."__pk_increment",T.* from test.rtest1 T where id<10;
+----------------+------+-------+
| __pk_increment | ID   | C     |
+----------------+------+-------+
|              1 |    1 | test1 |
|              2 |    2 | test2 |
|              3 |    3 | test3 |
|              4 |    4 | test4 |
|              5 |    5 | test5 |
|              6 |    6 | test6 |
|              7 |    7 | test7 |
|              8 |    8 | test8 |
|              9 |    9 | test9 |
+----------------+------+-------+
9 rows in set (0.003 sec)

__pk_increment作为条件查询
obclient [__OCEANBASE_INNER_DRC_USER]> select T."__pk_increment",T.* from test.rtest1 T where "__pk_increment"=1;
+----------------+------+-------+
| __pk_increment | ID   | C     |
+----------------+------+-------+
|              1 |    1 | test1 |
+----------------+------+-------+
1 row in set, 2 warnings (0.004 sec)