索引查询
当开启全局索引功能后，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 recordstcaplus> 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 recordstcaplus> 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 的查询。
不支持其他未提到的查询。