You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/09/27 07:44:33 UTC
[GitHub] [shardingsphere] Mr-Zhe opened a new issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Mr-Zhe opened a new issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768
## Question
I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries?
I used shardingSphere-jdbC-core-spring-boot-starter #5.0.0-beta
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927617069
#### My Example:
```yml
spring:
shardingsphere:
props:
sql-show: true
datasource:
names: database-0,database-1
database-0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc1?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
username: xxx
password: xxx
database-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc2?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
username: xxx
password: xxx
rules:
sharding:
binding-tables: t_order,t_user
default-database-strategy:
complex:
sharding-columns: order_id,user_id
sharding-algorithm-name: database-complex
tables:
t_user:
actual-data-nodes: database-$->{0..1}.t_user$->{0..3}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: user_id
key-generator-name: snowflake
t_order:
actual-data-nodes: database-$->{0..1}.t_order$->{0..3}
table-strategy:
complex:
sharding-columns: order_id,user_id
sharding-algorithm-name: order-complex-keys
key-generate-strategy:
column: order_id
key-generator-name: snowflake
sharding-algorithms:
database-complex:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.tencent.springshardjdbc.sharding.algorithm.DatabaseCustomComplexKeysShardingAlgorithm
user-inline:
type: INLINE
props:
algorithm-expression: t_user$->{user_id % 4}
order-complex-keys:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.tencent.springshardjdbc.sharding.algorithm.OrderTableCustomComplexKeysShardingAlgorithm
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
```
`DatabaseCustomComplexKeysShardingAlgorithm`
```java
public class DatabaseCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Getter
@Setter
private Properties props = new Properties();
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
ComplexKeysShardingValue<Long> shardingValue) {
if (availableTargetNames.size() == 1) {
return availableTargetNames;
}
Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
int index = (int) (sum % 2);
return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
}
@Override
public void init() {
System.out.println("init");
}
@Override
public String getType() {
return "CustomComplexKeys";
}
}
```
`OrderTableCustomComplexKeysShardingAlgorithm`
```java
public class OrderTableCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Getter
@Setter
private Properties props = new Properties();
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
ComplexKeysShardingValue<Long> shardingValue) {
if (availableTargetNames.size() == 1) {
return availableTargetNames;
}
Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
int index = (int) (sum % 4);
return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
}
@Override
public void init() {
System.out.println("init");
}
@Override
public String getType() {
return "CustomComplexKeys";
}
}
```
#### SQL:
```sql
select t1.*
from t_order t1,
t_user t2
where t1.user_id = t2.user_id and t2.user_id = #{pageDTO.userId}
limit #{pageDTO.pageNumber},#{pageDTO.pageSize}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927618660
#### further information
```sql
create table t_order0
(
order_id bigint not null comment '主键'
primary key,
user_id bigint not null comment '用户ID',
order_name varchar(255) default '' not null comment '名称',
gmt_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '更新时间',
gmt_create timestamp default CURRENT_TIMESTAMP not null comment '创建时间'
)
comment '订单表0';
```
```sql
create table t_user0
(
user_id bigint not null
primary key,
id_card varchar(50) not null,
user_name varchar(50) not null,
sex int null,
phone varchar(20) not null,
gmt_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
gmt_create timestamp default CURRENT_TIMESTAMP not null
)
comment '用户表';
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-929071493
If you are interested, you can check the implementation of the federation execution engine through the source code.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe edited a comment on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe edited a comment on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927617069
#### My Example:
```yml
spring:
shardingsphere:
props:
sql-show: true
datasource:
names: database-0,database-1
database-0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc1?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
username: xxx
password: xxx
database-1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://127.0.0.1:3306/spring-sharding-jdbc2?characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&rewriteBatchedStatements=true
username: xxx
password: xxx
rules:
sharding:
binding-tables: t_order,t_user
default-database-strategy:
complex:
sharding-columns: order_id,user_id
sharding-algorithm-name: database-complex
tables:
t_user:
actual-data-nodes: database-$->{0..1}.t_user$->{0..3}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: user-inline
key-generate-strategy:
column: user_id
key-generator-name: snowflake
t_order:
actual-data-nodes: database-$->{0..1}.t_order$->{0..3}
table-strategy:
complex:
sharding-columns: order_id,user_id
sharding-algorithm-name: order-complex-keys
key-generate-strategy:
column: order_id
key-generator-name: snowflake
sharding-algorithms:
database-complex:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.springshardjdbc.sharding.algorithm.DatabaseCustomComplexKeysShardingAlgorithm
user-inline:
type: INLINE
props:
algorithm-expression: t_user$->{user_id % 4}
order-complex-keys:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName: com.springshardjdbc.sharding.algorithm.OrderTableCustomComplexKeysShardingAlgorithm
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 123
```
`DatabaseCustomComplexKeysShardingAlgorithm`
```java
public class DatabaseCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Getter
@Setter
private Properties props = new Properties();
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
ComplexKeysShardingValue<Long> shardingValue) {
if (availableTargetNames.size() == 1) {
return availableTargetNames;
}
Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
int index = (int) (sum % 2);
return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
}
@Override
public void init() {
System.out.println("init");
}
@Override
public String getType() {
return "CustomComplexKeys";
}
}
```
`OrderTableCustomComplexKeysShardingAlgorithm`
```java
public class OrderTableCustomComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm<Long> {
@Getter
@Setter
private Properties props = new Properties();
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
ComplexKeysShardingValue<Long> shardingValue) {
if (availableTargetNames.size() == 1) {
return availableTargetNames;
}
Map<String, Collection<Long>> shardingValuesMap = shardingValue.getColumnNameAndShardingValuesMap();
long sum = shardingValuesMap.values().stream().flatMap(Collection::stream).mapToLong(num -> num).sum();
int index = (int) (sum % 4);
return Lists.newArrayList(new ArrayList<>(availableTargetNames).get(index));
}
@Override
public void init() {
System.out.println("init");
}
@Override
public String getType() {
return "CustomComplexKeys";
}
}
```
#### SQL:
```sql
select t1.*
from t_order t1,
t_user t2
where t1.user_id = t2.user_id and t2.user_id = #{pageDTO.userId}
limit #{pageDTO.pageNumber},#{pageDTO.pageSize}
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe closed issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe closed issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927634680
### final query sql
first
```
2021-09-27 15:02:07.941 [INFO ] [http-nio-9090-exec-4] ShardingSphere-SQL: 74 - Actual SQL: database-1 ::: select t1.*
from t_order t1,
t_user t2
where t1.user_id = t2.user_id and t2.user_id = ?
limit ?,? ::: [1442377913421123586, 0, 5]
```
second
```
2021-09-27 15:02:07.941 [INFO ] [http-nio-9090-exec-4] ShardingSphere-SQL: 74 - Actual SQL: database-0 ::: select t1.*
from t_order t1,
t_user t2
where t1.user_id = t2.user_id and t2.user_id = ?
limit ?,? ::: [1442377913421123586, 0, 5]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927626667
How to understand `why not rewrite the SQL for cross-database table queries`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-929071141
@Mr-Zhe Because the federation execution engine is used for cross-database query, which is not the same as the standard kernel process, it seems that the logical table is not rewritten.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-929071141
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] Mr-Zhe commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
Mr-Zhe commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927642980
If you change the routing policy of the `user table` and `order table` to a single database，example：
```yml
t_order:
actual-data-nodes: database-0.t_order$->{0..3}
t_user:
actual-data-nodes: database-0.t_user$->{0..3}
```
Then use the same query SQL，example：
```sql
select t1.*
from t_order t1,
t_user t2
where t1.user_id = t2.user_id and t2.user_id = #{pageDTO.userId}
limit #{pageDTO.pageNumber},#{pageDTO.pageSize}
```
the final SQL is rewritten as
```
2021-09-27 16:21:18.725 [INFO ] [http-nio-9090-exec-1] ShardingSphere-SQL: 74 - Actual SQL: database-0 ::: select t1.*
from t_order2 t1,
t_user2 t2
where t1.user_id = t2.user_id and t2.user_id = ?
limit ?,? ::: [1442377913421123586, 0, 5]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu closed issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu closed issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12768: I have two databases with the same database table structure, why not rewrite the SQL for cross-database table queries
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12768:
URL: https://github.com/apache/shardingsphere/issues/12768#issuecomment-927608372
@Mr-Zhe Can you provide an example to describe your question?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org