tencent cloud

游戏数据库 TcaplusDB

产品动态
产品简介
产品概述
产品优势
应用场景
产品架构
地域介绍
购买指南
产品定价
欠费说明
快速入门
了解基本概念
创建集群
创建表格组
创建表格
获取连接信息
访问 TcaplusDB
操作指南
管理集群与表格组
管理表格
监控与告警
访问管理
标签
任务列表
使用 TcaplusDB client
使用 client 工具访问数据
client 工具命令参考
使用 TcaplusDB SDK
变更历史
SDK 下载
SDK for C++ 接口说明
TcaplusDB 错误码
SDK 安装
PB 表 C++SDK 操作方法
TDR 表 C++SDK 操作方法
使用 TcaplusDB RESTful API
RESTful API 接口说明
Go RESTful API 接口说明
Java RESTful API 接口说明
PHP RESTful API 接口说明
Python RESTful API 接口说明
RESTful API 各语言示例下载
实践教程
表结构设计
数据库交互
常见问题
信息咨询类
数据库使用类
数据库原理类
API 文档
History
Introduction
API Category
Making API Requests
Table Group APIs
Other APIs
Table APIs
Cluster APIs
Data Types
Error Codes
相关协议
服务等级协议
服务条款
词汇表
联系我们
文档游戏数据库 TcaplusDB使用 TcaplusDB clientclient 工具命令参考select with global index

select with global index

PDF
聚焦模式
字号
最后更新时间： 2024-12-04 09:56:32

索引查询

当开启全局索引功能后，TcaplusDB 将支持按照普通字段查询，查询条件中的字段必须是建立了全局索引的字段。 如果是聚合查询，那么聚合查询的字段也必须是建立了全局索引的字段。 一个索引查询请求，最多返回3000条记录。

支持的查询语句

条件查询

支持=, >, >=, <, <=, !=, between, in, not in, like, not like, and, or，如：
注意：
between 查询时，between a and b，对应的查询范围为[a, b]，如 between 1 and 100，是会包含1和100这两个值的，即查询范围为[1,100]。
like 查询时，支持模糊匹配，其中“%”通配符，匹配0个或者多个字符； “_”通配符，匹配1个字符。
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

分页查询

支持 limit offset 分页查询，如：
注意：
当前 limit 必须与 offset 搭配使用，即不支持 limit 1 或者 limit 0,1。
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       |
+------+---------+--------+-------+-----------+-----------------------------------------+-----+---------+---+---------+--------+

聚合查询

当前支持的聚合查询包括：sum, count, max, min, avg，如：
注意：
聚合查询不支持 limit offset，即 limit offset 不生效。
目前只有 count 支持 distinct，即 select count(distinct(a)) from table where a > 1000，其他情况均不支持 distinct。
tcaplus> select sum(openid), count(*), max(openid), avg(openid) from pb_generic_index_shardingkey where openid>10 ;
1010,5,204,202

部分字段查询

支持查询部分字段的值，如：
说明：
对于 Protobuf 表，还支持查询嵌套字段的值，类似：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

不支持的 sql 查询语句

不支持聚合查询与非聚合查询混用

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;

不支持 order by 查询

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

不支持 group by 查询

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

不支持 having 查询

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

不支持多表联合查询

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

不支持嵌套 select 查询

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

不支持别名

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

不支持的其他查询

不支持 join 查询。
不支持 union 查询。
不支持类似 select a+b from table where a > 1000 的查询。
不支持类似 select * from table where a+b > 1000 的查询。
不支持类似 select * from table where a >= b 的查询。
不支持其他未提到的查询。
上一篇: select下一篇: getttl

帮助和支持

本页内容是否解决了您的问题？

填写满意度调查问卷，共创更好文档体验。

文档反馈