tencent cloud

TencentDB for TcaplusDB

Release Notes
Product Introduction
Product Overview
Benefits
Use Cases
Architecture
Regions
Purchase Guide
Product Pricing
Payment Overdue
Getting Started
Basic Concepts
Creating Cluster
Creating Table Group
Creating Table
Get Connection Information
Accessing TcaplusDB
Operation Guide
Managing Cluster and Table Group
Managing Table
Monitoring and Alarming
Access Management
Tag
Task List
TcaplusDB Client
Accessing by Client Tool
Client Tool Commands
TcaplusDB SDK
Release History
SDK Download
C++ SDK API
TcaplusDB Error Codes
SDK Installation
Directions for Protobuf Table SDK for C++
Directions for TDR Table SDK for C++
TcaplusDB RESTful APIs
Description
Go
Java
PHP
Python
Downloading RESTful API Samples in Multiple Languages
Practical Tutorial
Best Practice for Table Structure Design
Best Practice for Database Interaction
FAQs
Database Features
Database Use
Database Principles
API Documentation
History
Introduction
API Category
Making API Requests
Table Group APIs
Other APIs
Table APIs
Cluster APIs
Data Types
Error Codes
Service Agreement
Service Level Agreement
Terms of Service
Glossary
Contact Us
문서TencentDB for TcaplusDBTcaplusDB ClientClient Tool Commandsselect with global index

select with global index

PDF
포커스 모드
폰트 크기
마지막 업데이트 시간: 2024-12-04 10:12:05

Index-based Query

After the global index feature is enabled, TcaplusDB supports the field query, provided that the field in the query condition must have global index created. The fields in an aggregate query also require global index. An index-based query returns up to 3,000 results.

Supported statements

Query conditions

The following query conditions are supported, including =, >, >=, <, <=, !=, between, in, not in, like, not like, and, or.
Note:
The two values of between are included in the range. For example, if you use between 1 and 100, both 1 and 100 are inclusive. In other words, the query range should be [1,100].
The like query supports fuzzy matching. The wildcard % matches zero or multiple characters, while the wildcard _ matches one character.
tcaplus> select * from pb_generic_index_shardingkey where openid>10 and tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

tcaplus> select * from pb_generic_index_shardingkey where openid between 1 and 300  and tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

tcaplus> select * from pb_generic_index_shardingkey where openid>10 or tconndid<1000;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+


total 5 records

Paginated query

The paginated query limit offset is supported.
Note:
The paginated query must use limit offset. Neither limit 1 or limit 0,1 can be used.
tcaplus> select * from pb_generic_index_shardingkey where openid>10 limit 3 offset 0;
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |other_property                           |items|lockid   |pay|id_uint32|id_int32|
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|[{"key":1,"value":1},{"key":2,"value":2}]|-    |[1,2,3,4]|-  |1        |1       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+

Aggregate query

The following aggregate query functions are supported, including sum, count, max, min, avg.
Note:
The aggregate query does not support limit offset.
Currently, only the count function can be used with distinct. For example, select count(distinct(a)) from table where a > 1000.
tcaplus> select sum(openid), count(*), max(openid), avg(openid) from pb_generic_index_shardingkey where openid>10 ;
1010,5,204,202

Specified field query

The values of specified fields can be queried.
Note:
You can also query nested fields in the Protobuf table. For example, select field1.field2.field3, a, b from table where a > 1000.
tcaplus> select svrid,gamesvrid from pb_generic_index_shardingkey where openid>10 or tconndid<1000;
+------+---------+--------+-------+-----------+
|openid|timekey  |tconndid|svrid  |gamesvrid  |
+------+---------+--------+-------+-----------+
|204   |"timekey"|204     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|203   |"timekey"|203     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|202   |"timekey"|202     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|200   |"timekey"|200     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+
|201   |"timekey"|201     |"svrid"|"gamesvrid"|
+------+---------+--------+-------+-----------+


total 5 records

Unsupported SQL statements

Using an aggregate query with non-aggregate query

select *, a, b from table where a > 1000;

select sum(a), a, b from table where a  > 1000;

select count(*), * from table where a  > 1000;

Query by order by

select * from table where a > 1000 limit 100 offset 0;

Query by group by

select * from table where a > 1000 group by a;

Query by having

select sum(a) from table where  a > 1000 group by a having sum(a) > 10000;

Multi-table query

select * from table1 where table1.a > 1000 and table1.a = table2.b;

Nested SELECT query

select * from table where a > 1000 and b in (select b from table where b < 5000);

AS query

select sum(a) as sum_a from table where a > 1000;

Other queries not supported

JOIN
UNION
Queries like select a+b from table where a > 1000
Queries like select * from table where a+b > 1000
Queries like select * from table where a >= b
Others
이전 주제: select다음 주제: getttl

도움말 및 지원

문제 해결에 도움이 되었나요?

피드백