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 2022/01/28 14:47:10 UTC
[GitHub] [shardingsphere] LostMyth opened a new issue #15162: Why did my logical sql same as actual sql?
LostMyth opened a new issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162
### This is application.yml
```
sharding:
tables:
block_order: #block_order表
actual-data-nodes: master0.block_order_$->{0..9} #数据节点,均匀分布,这种是单库多表
table-strategy: #分表策略
standard:
sharding-column: wallet_address
precise-algorithm-class-name: com.xxx.MyPreciseShardingAlgorithm
```
### This is mapper.xml
```
select
bo.wallet_address as userWalletAddress,
sum( bo.order_money ) AS orderMoney,
sum( bo.win_prize_money ) AS winPrizeMoney,
sum( bo.prize_money ) AS prizeMoney,
DATE_FORMAT( bo.order_time, "%Y-%m-%d" ) AS day
from
block_order bo
<where>
<if test="request.walletAddress != null and request.walletAddress != ''">
and bo.wallet_address = #{request.walletAddress}
</if>
<if test="request.beginOrderTime != null">
and bo.order_time >= #{request.beginOrderTime}
</if>
<if test="request.endOrderTime != null">
and bo.order_time <= #{request.endOrderTime}
</if>
</where>
GROUP BY bo.wallet_address,
DATE_FORMAT( bo.order_time, "%Y-%m-%d" )
```
### Logic SQL:
```
master0 ::: SELECT bo.wallet_address AS userWalletAddress, sum(bo.order_money) AS orderMoney, sum(bo.win_prize_money) AS winPrizeMoney, sum(bo.prize_money) AS prizeMoney, DATE_FORMAT(bo.order_time, "%Y-%m-%d") AS day FROM block_order bo WHERE bo.wallet_address = ? GROUP BY bo.wallet_address, DATE_FORMAT(bo.order_time, "%Y-%m-%d")
```
### Actual SQL:
```
SELECT bo.wallet_address AS userWalletAddress, sum(bo.order_money) AS orderMoney, `sum(bo.win_prize_money)` AS winPrizeMoney, sum(bo.prize_money) AS prizeMoney, DATE_FORMAT(bo.order_time, "%Y-%m-%d") AS day FROM block_order bo WHERE bo.wallet_address = ? GROUP BY bo.wallet_address, DATE_FORMAT(bo.order_time, "%Y-%m-%d")
```
Please help me , Thank you very much.
--
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] RaigorJiang commented on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1024803872
Hi @LostMyth
Please provide your version of ShardingSphere and the complete configuration.
From the snippets provided, you are using a custom sharding algorithm, but the configuration is incomplete
--
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] LostMyth commented on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1024941564
> Hi @LostMyth Please provide your version of ShardingSphere and the complete configuration. From the snippets provided, you are using a custom sharding algorithm, but the configuration is incomplete
I'm first using sharding-jdbc, please forgive me...
--
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] LostMyth removed a comment on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth removed a comment on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1030776540
> Hi @LostMyth, If you are using Sharding-JDBC for a new project, I recommend you go straight to version 5.0.0. The new API is easier to understand and use.
Thank you for your suggestion.
--
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] LostMyth commented on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1024941216
>
### This is complete configuration.
```
shardingsphere:
datasource:
names: master0
hikari:
minimum-idle: 20
connection-test-query: SELECT 1 FROM DUAL
maximum-pool-size: 200
auto-commit: true
idle-timeout: 30000
pool-name: HikariPool-1
max-lifetime: 28000
connection-timeout: 30000
master0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/cloud_hash?useSSL=false&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&tinyInt1isBit=false&rewriteBatchedStatements=true
username: root
password: 11111
sharding:
tables:
block_order: #block_order表
actual-data-nodes: master0.block_order_$->{0..9} #数据节点,均匀分布,这种是单库多表
table-strategy: #分表策略
standard:
sharding-column: tran_hash
precise-algorithm-class-name: com.xxx.MyPreciseShardingAlgorithm
reward:
actual-data-nodes: master0.reward_$->{0..9}
table-strategy: #分表策略
standard:
sharding-column: tran_hash
precise-algorithm-class-name: com.xxxMyPreciseShardingAlgorithm
props:
sql: #打印sql
show: true
```
### pom.xml
```
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.2.RELEASE</version>
<relativePath/>
</parent>
...
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
```
--
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] LostMyth commented on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1030776612
> Hi @LostMyth, If you are using Sharding-JDBC for a new project, I recommend you go straight to version 5.0.0. The new API is easier to understand and use.
Thank you for your suggestion. I'll try it.
--
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] LostMyth commented on issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1030776540
> Hi @LostMyth, If you are using Sharding-JDBC for a new project, I recommend you go straight to version 5.0.0. The new API is easier to understand and use.
Thank you for your suggestion.
--
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] LostMyth closed issue #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
LostMyth closed issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162
--
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 #15162: Why did my logical sql same as actual sql?
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15162:
URL: https://github.com/apache/shardingsphere/issues/15162#issuecomment-1025018170
Hi @LostMyth, If you are using Sharding-JDBC for a new project, I recommend you go straight to version 5.0.0. The new API is easier to understand and use.
--
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