GBase 8a MPP Cluster 产品手册 4 管理员指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 463 表语句和数据为: create table t (i1 int, vc1 varchar(10) masked with (function='partial(2,"*****",0)'), vc2 varchar(10) masked with (function='partial(2,"*****",0)')); insert into t values (1, 'nblknabpa', 'pombkaia'); insert into t values (2, '.mapkna', '0jbadflk'); insert into t values (); 示例1:第i 类函数case when 脱敏列规则; gbase>select case i1 when 1 then vc1 when 2 then '12345' else '67890' end as res from t; +---------+ | res | +---------+ | nb***** | | 12***** | | 67***** | +---------+ 示例2:第i 类函数coalesce 脱敏列规则; gbase>select coalesce(vc1,'12345') as res from t; +---------+ | res | +---------+ | nb***** | | .m***** | | 12***** | +---------+ 示例3:第i 类函数case when 多脱敏列默认脱敏; gbase> select case i1 when 1 then vc1 when 2 then vc2 else '67890' end as res from t; +------+ | res | +------+ | xxxx | | xxxx | | xxxx | +------+ 示例4:第ii 类函数substring 默认脱敏;
GBase 8a MPP Cluster 产品手册 4 管理员指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 464 gbase> select substring(vc1, 1, 2) as res from t; +------+ | res | +------+ | xx | | xx | | NULL | +------+ 示例5:第ii 类函数concat 默认脱敏; gbase> select concat(vc1,'123') as res from t; +------+ | res | +------+ | xxxx | | xxxx | | NULL | +------+ 示例6:第i 类函数嵌套使用; gbase> select case when i1 > 1 then coalesce(vc1, '12345') else '67890' end as res from t; +---------+ | res | +---------+ | 67***** | | .m***** | | 67***** | +---------+ 示例7:第ii 类函数嵌套使用; gbase> select concat(substring(vc1,1,2),'123') as res from t; +------+ | res | +------+ | xxxx | | xxxx | | NULL | +------+ 示例8:第i 类和ii 类函数混合嵌套调用;
GBase 8a MPP Cluster 产品手册 4 管理员指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 465 gbase> select coalesce(substring(vc1,1,2),'12345') as res from t; (或者select substring(coalesce (vc1, '12345'), 1, 2) as res from t;) +------+ | res | +------+ | xxxx | | xxxx | | xxxx | +------+ 示例9:子查询内或外调用i 类; gbase> select res from (select coalesce(vc1,'12345')as res from t) as tmp; (或者select coalesce(vc1, '12345') as res from (select vc1 from t) as tmp;) +---------+ | res | +---------+ | nb***** | | .m***** | | 12***** | +---------+ 示例10:子查询内或外调用ii 类; gbase> select res from (select concat(vc1,'123') as res from t) as tmp; (或者select concat (vc1, '123') as res from (select vc1 from t) as tmp;) +------+ | res | +------+ | xxxx | | xxxx | | NULL | +------+ 示例11:子查询内外调用i 类; gbase> select case when i1 > 1 then res1 else '67890' end as res from (select i1,coalesce(vc1, '12345') as res1 from t) as tmp; +---------+ | res | +---------+ | 67***** | | .m***** | | 67***** | +---------+ 示例12:子查询内外调用ii 类;
GBase 8a MPP Cluster 产品手册 4 管理员指南 文档版本953(2022-04-10) 南大通用数据技术股份有限公司 466 gbase> select concat(res1,'123') as res from (select substring(vc1,1,2) as res1 from t) as tmp; +------+ | res | +------+ | xxxx | | xxxx | | NULL | +------+ 示例13:子查询内外混合调用i 类和ii 类。 gbase> select substring(res1,1,4) as res from (select coalesce(vc1, '12345') as res1 from t) as tmp; (或者 gbase> select coalesce(res1, '12345') as res from (select substring(vc1,1,2) as res1 from t) as tmp; ) +------+ | res | +------+ | xxxx | | xxxx | | xxxx | +------+ 说明 脱敏列脱敏后如果超过脱敏列的最大长度,则自动截断为脱敏列的最大长度。 比如脱敏列定义为: mask_col varchar(5) masked with (function ='partial(2,"xxxx",2)') 则值“abcde”理论上应脱敏为“abxxxxde”,脱敏后的长度超过了最大长度5,自动截断为 “abxxx”。 脱敏列脱敏后对于一些可设置长度的函数脱敏时也会被截断,比如: select left(mask_col, 2) from t; left 函数将使用默认脱敏,理论上应脱敏为“xxxx”,但超过了left 函数设置的最大长度,所 以自动截断为“xx”。 Union、Intersect 和Minus 运算时,对应列的脱敏规则与控制流函数的脱敏规则一致。 比如: select mask_int_col from t union select mask_int_col as col from t; 将使用mask_int_col 的脱敏规则,而: select mask_int_col from t union select mask_int_col+1 from t;