华为云云数据库GaussDB密态支持客户端排序（实验室特性）_云淘科技

云数据库 GaussDB

11 月 23, 2023

77 0

当前特性是实验室特性，使用时请联系华为工程师提供技术支持。

密态支持客户端排序主要通过对查询返回的密文结果在客户端进行解密并排序。要开启密态支持客户端排序，gsql需要开启-C2或–enable-client-encryption=2, JDBC连接需要开启enable_ce=2参数。

目前密态支持客户端排序支持的语法格式如下：

SELECT [ DISTINCT ] { * | {colname [ [ AS ] output_name ] | [ SUM | MIN | MAX | COUNT | AVG ] ( colname ) [ [ AS ] output_name ] } [, ...] }
[ FROM tablename ]
[ [ INNER ] JOIN | LEFT [ OUTER ] JOIN | RIGHT [ OUTER ] JOIN | FULL [ OUTER ] JOIN | CROSS JOIN ] { tablename } ON condition { [ AND | OR ] condition } 
[ WHERE condition ]
[ GROUP BY {colname | output_name} [, ...] ]
[ ORDER BY { {colname | output_name} [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ]} [, ...] ]
[ LIMIT count ]
[ OFFSET start ];

参数说明

colname

当colname作为聚合函数MAX、MIN、SUM、AVG、COUNT的入参时，仅支持直接引用列名，不支持对列进行类型转换。

output_name

output_name为colname的别名，不支持GROUP BY和ORDER BY后面加聚集函数的别名。

WHERE子句

condition为通过“AND”和“OR”连接的等值条件，如colname=1, colname!=1, colname IS NULL等，不可包含表达式。

其余参数请参考《开发者指南》中“SQL参考 > SQL语法 > SELECT”章节。

本版本支持了涉及加密列的ORDER BY、GROUP BY、DISTINCT、LIMIT和OFFSET操作，但不支持以下涉及加密列的排序场景：

不支持DISTINCT ON、HAVING子句、EXECUTE DIRECT语句、函数和存储过程、嵌套SQL语句以及视图。
涉及加密列排序使用的聚合函数仅支持MAX、MIN、SUM、AVG、COUNT，不支持对应的窗口函数。这里，五个聚合函数对应客户端针对密文聚集操作重写的函数实现，不支持用户使用该方式调用服务端自定义的同名聚合函数，也不支持使用SCHEMA名结合函数名，如使用PG_CATALOG.MAX的方式调用客户端聚合函数。对于字符串执行SUM、AVG聚集函数的结果为0。
GROUP BY后面带加密列时，在SELECT目标列中需显式指定列名，不能使用*。
ORDER BY或GROUP BY有加密列时，LIMIT和OFFSET后面不能带表达式，如2+2，请使用4代替。
不支持ORDER BY 1，2的语法，必须显式指定加密列列名。
密态支持客户端排序仅支持对字符串进行ASCII排序，不与服务端字符集相关。密态浮点类型、数值类型计算精度使用客户端默认精度，与服务端精度设置不相关。
GROUP BY、ORDER BY后的表名、列名、别名中不支持使用单引号等特殊字符，如：SELECT i2 as c FROM t1 as “‘a” ORDER BY “‘a”.i2;。
密态客户端排序不支持在一个PQexec调用中发送的多个查询。如：PQexec(“PREPARE p1 select distinct i1 from t1 order by i1;EXECUTE p1;”);
对于JOIN场景，密态客户端排序不支持对同名列的校验，可能导致非预期结果，若两表使用相同列名，不建议使用JOIN进行客户端排序。

客户端排序需要将服务端返回的密文数据解密后再进行排序操作，对客户端有一定的资源消耗，建议每次查询返回的数据不超过10万条。

当前版本支持排序场景有限，不在支持语法范围内可能导致非预期结果，不建议对加密字段做排序。

查询加密表并排序

创建密钥，以gsql为例，详细步骤请参考使用gsql操作密态数据库。
创建加密表。

1
2
3

gaussdb=# CREATE TABLE IF NOT EXISTS t1(id int, i1 int, i2 INT ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC), i3 varchar(20) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = cek1, ENCRYPTION_TYPE = DETERMINISTIC),
i4 INT);
CREATE TABLE

插入数据。

gaussdb=# INSERT INTO t1 VALUES(1,1,100, 'one hundred');
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(2,2,200, 'two hundreds - 1', 10);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(3,7,300, 'three hundreds - 1', 11);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(4,6,400, 'four hundreds', 12);
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(5,5,300, 'three hundreds - 2');
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(6,4,200, 'two hundreds - 2');
INSERT 0 1
gaussdb=# INSERT INTO t1 VALUES(7,3,200, 'two hundreds - 3');
INSERT 0 1

执行排序查询

gaussdb=# select sum(id), i2 from t1 group by i2 order by i2;
 sum | i2  
-----+-----
   1 | 100
  15 | 200
   8 | 300
   4 | 400
(4 rows)
gaussdb=# select min(id), i2 from t1 group by i2 order by i2;
 min | i2  
-----+-----
   1 | 100
   2 | 200
   3 | 300
   4 | 400
(4 rows)

父主题： 设置密态等值查询

同意关联代理商云淘科技，购买华为云产品更优惠（QQ 78315851）

内容没看懂？不太想学习？想快速解决？有偿解决：联系专家