sharding-proxy 实现 sharding-jdbc 分库分表后查询

1. 下载 shrading-jdbc 二进制文件，需要在 linux 下进行解压，windows 解压可能造成 解压后 lib/jar 文件损坏

https://shardingsphere.apache.org/document/legacy/4.x/document/cn/downloads/

2. 数据库分库分表策略

3. 修改配置文件

• server.yaml
  

authentication:
  users:
    linewell:
      password: linewell
    shardingjdbc:
      password: shardingjdbc 
#      authorizedSchemas: shardingjdbc
#
props:
#  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
#  proxy.frontend.flush.threshold: 128  # The default value is 128.
#    # LOCAL: Proxy will run with LOCAL transaction.
#    # XA: Proxy will run with XA transaction.
#    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
#  query.with.cipher.column: true
  sql.show: true

• config-sharding.yaml
  

######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################

schemaName: sharding_db
![](https://gitee.com/zwbtt/zwb-pic/raw/master/img/20210511151856.png)
dataSources:
  dbnode1:
    url: jdbc:mysql://192.168.81.14:3311/wform_dbnode1?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
    username: linewell
    password: linewell
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  dbnode2:
    url: jdbc:mysql://192.168.81.14:3311/wform_dbnode2?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&zeroDateTimeBehavior=convertToNull
    username: linewell
    password: linewell
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    wfs_form_detail:
      actualDataNodes: dbnode$->{1..2}.wfs_form_detail_$->{1..2}
      tableStrategy:
        inline:
          shardingColumn: affair_rela_id
          algorithmExpression: dbnode$->{affair_rela_id % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: id
  bindingTables:
    - wfs_form_detail
  defaultDatabaseStrategy:
    none:
#    inline:
#      shardingColumn: user_id
#      algorithmExpression: ds_${user_id % 2}
  defaultTableStrategy:
    none:

4.配置数据库登入信息

连接 sharding-proxy 数据库展示

5.踩坑记录

正确示例

• 错误点1
  tableStrategy 配置成了 分库策略 ，导致数据只读，修改报错
• 错误点2
  sharding-proxy 版本问题，需要升级 到 4.0.1 -> 4.1.1
• 错误点3
  需要使用 navicat 12.0.18 版本，版本太高也会导致出错，数据查询有问题
• 错误点4
  查询数据日期显示不正确，相差8个小时
  需要配置数据源 jdbc:mysql://192.168.81.14:3311/wform_dbnode2?serverTimezone=UTC&useSSL=false 将 UTC 改为 serverTimezone=Asia/Shanghai

schemaName: wform_schema

dataSources:
#  ds_sch1:
#    url: jdbc:mysql://192.168.81.14:3311/wform_schema?serverTimezone=UTC&useSSL=false
#    username: linewell
#    password: linewell
#    connectionTimeoutMilliseconds: 30000
#    idleTimeoutMilliseconds: 60000
#    maxLifetimeMilliseconds: 1800000
#    maxPoolSize: 50
  ds_1:
    url: jdbc:mysql://192.168.81.14:3311/wform_dbnode1?serverTimezone=Asia/Shanghai&useSSL=false
    username: linewell
    password: linewell
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_2:
    url: jdbc:mysql://192.168.81.14:3311/wform_dbnode2?serverTimezone=Asia/Shanghai&useSSL=false
    username: linewell
    password: linewell
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  tables:
    wfs_form_detail:
      actualDataNodes: ds_${1..2}.wfs_form_detail_$->{1..2}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: wfs_form_detail_${id % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: id
    t_payment:
      actualDataNodes: ds_${1..2}.t_payment_$->{1..2}
      tableStrategy:
        inline:
          shardingColumn: id
          algorithmExpression: t_payment_${id % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: id    
  bindingTables:
    - wfs_form_detail,t_payment
  defaultDatabaseStrategy:
    none:
    #inline:
    #  shardingColumn: affair_rela_id
    #  algorithmExpression: ds_${affair_rela_id % 2 + 1}
  defaultTableStrategy:
    none:
  #defaultDataSourceName: ds_sch1