springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离、水平分表：INLINE行表达式分片算法

详情请见上一篇读写分离

《springboot集成mysql、mybatis-plus、shardingsphere-jdbc实现读写分离》

提供yml和properties2种配置

yml

mybatis-plus:
  mapper-locations: classpath:mapper/*.xml

spring:
  shardingsphere:
    props:
      sql-show: true
    datasource:
      names: master,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    rules:
      readwrite-splitting:
        data-sources:
          myds:
            static-strategy:
              write-data-source-name: master
              read-data-source-names:
                - slave1
            load-balancer-name: round-robin
        load-balancers:
          round-robin:
            type: ROUND_ROBIN
      #水平分表开始
      sharding:
        tables:
          #user是数据库的user表
          user:
          	#myds是自定义数据源的名称，user_$->{0..1}表示数据库里的user_0表和user_1表。如：0-12以此类推
            actual-data-nodes: myds.user_$->{0..1}
            table-strategy:
              standard:
              	#分表数据库主键名称。警告：这里的字段名跟分片算法有关，不能只改算法不改字段名，有些分片算法是不支持某些类型的字段的，如时间范围分片算法，这里就要知道时间类型的字段名称，如：create_time类型为datetime
                sharding-column: id
                #分表使用的分片算法别名，建议命名为：表名-算法
                sharding-algorithm-name: user-inline
            key-generate-strategy:
              #分表主键名称
              column: id
              #分表主键生成算法别名
              key-generator-name: snowflake
        key-generators:
          #跟上面定义的snowflake一致，可以起其它名字，但是这2个名字必须一致
          snowflake:
          	#分表主键生成算法类型
            type: SNOWFLAKE
        sharding-algorithms:
          #跟上面定义的user-inline一致，可以起其它名字，但是这2个名字必须一致，建议用-减号而不是用_下划线
          user-inline:
          	#分片算法：行表达式分片算法，全部内置算法参考网址：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
            type: INLINE
            props:
              #内置算法参数，可能报红，不用管，这里的参数要对照分片算法去配，某些算法是没有这些参数的，配了不会报错，但是不建议多配，只配有的参数。
              algorithm-expression: user_$->{id % 2}

properties

#打印sql
spring.shardingsphere.props.sql-show=true
# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1
# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.1.11:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.1.22:3306/shardingjdbc?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

#写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.write-data-source-name=master
#读数据源名称，多个从数据源用逗号分隔。如：slave1,slave2
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.static-strategy.read-data-source-names=slave1
#负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=round_robin
#负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

spring.shardingsphere.rules.sharding.tables.user.actual-data-nodes=myds.user_$->{0..1}
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-column=id
spring.shardingsphere.rules.sharding.tables.user.table-strategy.standard.sharding-algorithm-name=user-inline
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.user.key-generate-strategy.key-generator-name=snowflake
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.sharding-algorithms.user-inline.type=inline
spring.shardingsphere.rules.sharding.sharding-algorithms.user-inline.props.algorithm-expression= user_$->{id % 2}

测试

表结构

写入master主库：localhost:88/user/insert

查询从库：

mybatis的sql不用管到底查哪张表，直接写user表即可，如下所示：

#不用加user_0还是user_1直接查user
SELECT id,username,create_time FROM user WHERE id=797882128451764224

直接查user表，shardingsphere-会自动识别，打印如下sql

Logic SQL: SELECT id,username,create_time FROM user WHERE id=? 
SQLStatement: MySQLSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=50, stopIndex=50, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=29, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=7, stopIndex=8, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=10, stopIndex=17, identifier=IdentifierValue(value=username, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty), ColumnProjectionSegment(column=ColumnSegment(startIndex=19, stopIndex=29, identifier=IdentifierValue(value=create_time, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=36, stopIndex=39, identifier=IdentifierValue(value=user, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=41, stopIndex=50, expr=BinaryOperationExpression(startIndex=47, stopIndex=50, left=ColumnSegment(startIndex=47, stopIndex=48, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), right=ParameterMarkerExpressionSegment(startIndex=50, stopIndex=50, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=id=?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
Actual SQL: slave1 ::: SELECT id,username,create_time FROM user_0 WHERE id=?  ::: [797882128451764224]