You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@shardingsphere.apache.org by 明年烟花多 <71...@qq.com> on 2019/09/25 07:07:50 UTC
咨询一个关于数据库分片和数据脱敏一起用的问题
您好。
我现在用sharding-jdbc数据脱敏过程中遇到一个问题，单数据源情况下没问题，能够正常加密写入和读出，但是在分库分表的情况下总是提示我逻辑表不存在，请问您能帮我解答一下吗？
一 、2个数据库实例（Postgresql）：
dsjava和dsc
以组织作为分库列，java和c
----------------------------------------
二 、每个库各有2张表：
t_staff_male, t_staff_female
以性别作为分表列，男和女
-------------------------------------------
三 、建表脚本：
DROP TABLE "public"."t_staff_male";
CREATE TABLE "public"."t_staff_male" (
"staff_id" varchar(10) NOT NULL,
"staff_name" varchar(16) NOT NULL,
"staff_sex" varchar(8) NOT NULL,
"group_code" varchar(8),
"id_number_plain" varchar(32),
"id_number_cipher" varchar(64)
)
WITH (OIDS=FALSE)
;
ALTER TABLE "public"."t_staff_male" ADD PRIMARY KEY ("staff_id");
--------------------------------------------------------------------------------------
四 、Springboot2.1.*+Mybatis3.5.* 配置文件（application.yml）：
mybatis:
config-location: classpath:META-INF/mybatis-config.xml
mapper-locations: classpath:com/example/shardingsphere/dao/mappers/*.xml
spring:
shardingsphere:
datasource:
names: dsjava,dsc
dsjava:
type: org.apache.commons.dbcp2.BasicDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5444/dsjava
username: fan
password:
dsc:
type: org.apache.commons.dbcp2.BasicDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5444/dsc
username: fan
password:
sharding:
default-database-strategy:
inline:
sharding-column: group_code
algorithm-expression: ds$->{group_code}
tables:
t_staff:
actual-data-nodes: ds$->{['java', 'c']}.t_staff_$->{['male', 'female']}
table-strategy:
inline:
sharding-column: staff_sex
algorithm-expression: t_staff_$->{staff_sex}
encrypt:
encryptors:
encryptor_aes:
type: aes
props:
aes.key.value: 123456
tables:
t_staff:
columns:
id_number:
plainColumn: id_number_plain
cipherColumn: id_number_cipher
encryptor: encryptor_aes
props:
sql.show: true
query.with.cipher.comlum: true ---------------------------------------------------------------------------- 五 、StaffMapper.xml <insert id="save" parameterType="staff">
insert into t_staff
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="staffId != null" >
staff_id,
</if>
<if test="staffName != null" >
staff_name,
</if>
<if test="staffSex != null" >
staff_sex,
</if>
<if test="groupCode != null" >
group_code,
</if>
<if test="idNumber != null" >
id_number,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="staffId != null" >
#{staffId},
</if>
<if test="staffName != null" >
#{staffName},
</if>
<if test="staffSex != null" >
#{staffSex},
</if>
<if test="groupCode != null" >
#{groupCode},
</if>
<if test="idNumber != null" >
#{idNumber},
</if>
</trim>
</insert> ---------------------------------------------------------------------- 六 、问题描述 1、将上面application.yml的数据源注掉一个，数据库中只创建一个t_staff表，其他不动的情况下可以实现数据脱敏； 2、完成按照上面的实现，会报错： ### Error updating database. Cause: org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在
位置：13
### The error may exist in file [D:\idea_workspace\sharding-sphere\target\classes\com\example\shardingsphere\dao\mappers\StaffMapper.xml]
### The error may involve com.example.shardingsphere.dao.StaffMapper.save-Inline
### The error occurred while setting parameters
### SQL: insert into t_staff( staff_id, staff_name, staff_sex, group_code, id_number ) values (?, ?, ?, ?, ? )
### Cause: org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在 位置：13; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在 位置：13] with root cause 感谢你们的努力，期待得到您的回复。
回复：咨询一个关于数据库分片和数据脱敏一起用的问题
Posted by Juan Pan <pa...@apache.org>.
Hi,
Actually, we need to communicate in English. Otherwise, i can not confirm the validity of this configuration , for it is presented in plain format in this email. So my suggestion is to compare it with those configurations in our example project[1].
[1] https://github.com/apache/incubator-shardingsphere-doc
Regards,
Trista
Juan Pan
panjuan@apache.org
Juan Pan(Trista), Apache ShardingSphere
在2019年09月25日 15:07，明年烟花多<71...@qq.com> 写道：
您好。
我现在用sharding-jdbc数据脱敏过程中遇到一个问题，单数据源情况下没问题，能够正常加密写入和读出，但是在分库分表的情况下总是提示我逻辑表不存在，请问您能帮我解答一下吗？
一 、2个数据库实例（Postgresql）：
dsjava和dsc
以组织作为分库列，java和c
----------------------------------------
二 、每个库各有2张表：
t_staff_male, t_staff_female
以性别作为分表列，男和女
-------------------------------------------
三 、建表脚本：
DROP TABLE "public"."t_staff_male";
CREATE TABLE "public"."t_staff_male" (
"staff_id" varchar(10) NOT NULL,
"staff_name" varchar(16) NOT NULL,
"staff_sex" varchar(8) NOT NULL,
"group_code" varchar(8),
"id_number_plain" varchar(32),
"id_number_cipher" varchar(64)
)
WITH (OIDS=FALSE)
;
ALTER TABLE "public"."t_staff_male" ADD PRIMARY KEY ("staff_id");
--------------------------------------------------------------------------------------
四 、Springboot2.1.*+Mybatis3.5.* 配置文件（application.yml）：
mybatis:
config-location: classpath:META-INF/mybatis-config.xml
mapper-locations: classpath:com/example/shardingsphere/dao/mappers/*.xml
spring:
shardingsphere:
datasource:
names: dsjava,dsc
dsjava:
type: org.apache.commons.dbcp2.BasicDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5444/dsjava
username: fan
password:
dsc:
type: org.apache.commons.dbcp2.BasicDataSource
driver-class-name: org.postgresql.Driver
url: jdbc:postgresql://127.0.0.1:5444/dsc
username: fan
password:
sharding:
default-database-strategy:
inline:
sharding-column: group_code
algorithm-expression: ds$->{group_code}
tables:
t_staff:
actual-data-nodes: ds$->{['java', 'c']}.t_staff_$->{['male', 'female']}
table-strategy:
inline:
sharding-column: staff_sex
algorithm-expression: t_staff_$->{staff_sex}
encrypt:
encryptors:
encryptor_aes:
type: aes
props:
aes.key.value: 123456
tables:
t_staff:
columns:
id_number:
plainColumn: id_number_plain
cipherColumn: id_number_cipher
encryptor: encryptor_aes
props:
sql.show: true
query.with.cipher.comlum: true ---------------------------------------------------------------------------- 五 、StaffMapper.xml <insert id="save" parameterType="staff">
insert into t_staff
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="staffId != null" >
staff_id,
</if>
<if test="staffName != null" >
staff_name,
</if>
<if test="staffSex != null" >
staff_sex,
</if>
<if test="groupCode != null" >
group_code,
</if>
<if test="idNumber != null" >
id_number,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="staffId != null" >
#{staffId},
</if>
<if test="staffName != null" >
#{staffName},
</if>
<if test="staffSex != null" >
#{staffSex},
</if>
<if test="groupCode != null" >
#{groupCode},
</if>
<if test="idNumber != null" >
#{idNumber},
</if>
</trim>
</insert> ---------------------------------------------------------------------- 六 、问题描述 1、将上面application.yml的数据源注掉一个，数据库中只创建一个t_staff表，其他不动的情况下可以实现数据脱敏； 2、完成按照上面的实现，会报错： ### Error updating database. Cause: org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在
位置：13
### The error may exist in file [D:\idea_workspace\sharding-sphere\target\classes\com\example\shardingsphere\dao\mappers\StaffMapper.xml]
### The error may involve com.example.shardingsphere.dao.StaffMapper.save-Inline
### The error occurred while setting parameters
### SQL: insert into t_staff( staff_id, staff_name, staff_sex, group_code, id_number ) values (?, ?, ?, ?, ? )
### Cause: org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在 位置：13; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: 错误: 关系 "t_staff" 不存在 位置：13] with root cause 感谢你们的努力，期待得到您的回复。