shardingsphere+sqlserver分表踩坑

wingsofdragon

已于 2024-07-24 17:06:18 修改

阅读量341

点赞数 3

文章标签： sqlserver 数据库

于 2024-07-24 17:01:49 首次发布

本文链接：https://blog.csdn.net/wingsofdragon/article/details/140665214

版权

Edit this page

ShardingSphere-Proxy：

结论：截至5.5.0版本，ShardingSphere-Proxy是不支持SQLserver的。

过程：选择的版本是apache-shardingsphere-5.4.1-shardingsphere-proxy-bin.tar.gz

a.解压（解压此压缩包要注意，由于很多文件命名很长，解压的软件不合适的话，文件名会异常，导致无法运行。）

b.配置

server.yaml

#mode:
#  type: Standalone  # 配置模式类型：Standalone 表示单机模式
#  repository:
#    type: File  # 使用文件作为配置存储
#    props:
#      path: /opt/shardingsphere-proxy/conf  # 配置存储路径

# 配置权限相关信息
authority:
  privilege:
    type: ALL_PERMITTED  # 权限控制类型：ALL_PERMITTED 表示所有权限，USER_PERMITTED 表示基于用户的权限控制
  users:
    - user: root@%
      password: root  # 用户名和密码配置
#    - user: sharding@%
#      password: sharding

# 全局属性配置
props:
  #max-connections-size-per-query: 1  # 每个查询的最大连接数
  acceptor-size: 16  # 接受请求的线程池大小，默认为可用处理器数量的两倍
  executor-size: 16  # 执行 SQL 的线程池大小，默认为无限
  proxy-frontend-flush-threshold: 128  # 配置前端缓存刷新的阈值
  proxy-opentracing-enabled: false  # 是否启用 OpenTracing 进行 SQL 跟踪
  proxy-hint-enabled: false  # 是否启用 SQL Hint
  sql-show: true  # 是否在日志中显示 SQL
  check-table-metadata-enabled: false  # 是否启用表元数据检查
  proxy-frontend-ssl-enabled: false
  #proxy-frontend-ssl-cipher: ''
  #proxy-frontend-ssl-version: TLSv1.2,TLSv1.3
  proxy-default-port: 3307
  system-log-level: DEBUG
  
logging:
  loggers:
  - loggerName: ShardingSphere-SQL
    additivity: true
    level: DEBUG
    props:
      enable: false

config-sharding.yaml

databaseName: sharding_db

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    url: jdbc:sqlserver://127.0.0.1:1433;databasename=test;encrypt=true;trustServerCertificate=true;useUnicode=true;characterEncoding=UTF-8;
    username: sa
    password: admin
    #url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    #username: root
    #password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 10

rules:
  - !SHARDING
    tables:
      action_record:
        actualDataNodes: ds0.action_record_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: action_record_inline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake

    shardingAlgorithms:
      action_record_inline:
        type: INLINE
        props:
          algorithm-expression: action_record_$->{id % 2}

    keyGenerators:
      snowflake:
        type: SNOWFLAKE

将上述两个配置丢到conf/下

再将jdbc驱动丢到ext-lib下（没有此目录自己创建）

以下这个下载的jar包就可以（其他版本也可以，看自己需要）

        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>10.2.1.jre8</version>
        </dependency>

c.修改源码：StandardJdbcUrlParser下的

AUTHORITY_PATTERN = "(?://(?[^/?#]))?\s"

修改为

AUTHORITY_PATTERN = "(?://(?[^/?#;]))?\s"

（增加分号的判断）

然后出包，更新覆盖shardingsphere-infra-database-core-5.4.1.jar

d.运行。

配置好jdk环境，或者在/bin/start.bat里面增加环境变量，然后启动

set JAVA_HOME=xxxxxxxxxxxx
set PATH=%JAVA_HOME%\bin;%PATH%

e.项目连接。

数据源配置url：url: jdbc:sqlserver://127.0.0.1:3307;databasename=sharding_db

用户密码是配置中的：root，root（可修改）

然后Sqlserver的连接是异常的。。。（Mysql正常。）

ShardingSphere-JDBC

选用的版本：5.3.1（分的表要自己建）

ShardingSphere的引入变动很大，4.x版本使用的是

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

5.x中5.3之前的版本（不包括5.3）

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.2.0</version>
        </dependency>

5.3之后的版本，暂时没有提供springboot的启动器，只能引用原生的

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.3.1</version>
        </dependency>

不管是大版本还是小版本，配置字段，格式差距变化极大，需要慢慢试错修正。

a.配置

在项目中配置数据源：

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:sharding-all.yaml
    type: com.zaxxer.hikari.HikariDataSource

sharding-all.yaml

mode:
  type: Standalone
  repository:
    type: JDBC

dataSources:
  ds0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbcUrl: jdbc:sqlserver://127.0.0.1:1433;databasename=test;encrypt=false;trustServerCertificate=true;
    username: sa
    password: admin

rules:
  - !SHARDING
    tables:
      action_record:
        actualDataNodes: ds0.action_record_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: action_record_inline
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake

    shardingAlgorithms:
      action_record_inline:
        type: INLINE
        props:
          algorithm-expression: action_record_${id % 2}
    keyGenerators:
      snowflake:
        type: SNOWFLAKE

props:
  sql-show: true

引入了分页插件，需要进行配置：

    @Bean
    @ConditionalOnMissingBean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setLocalPage(true);
        paginationInterceptor.setDialectType(DBType.SQLSERVER.getDb());
        paginationInterceptor.setSqlParser(new ISqlParser() {
            @Override
            public SqlInfo optimizeSql(MetaObject metaObject, String sql) {
                JsqlParserCountOptimize jsqlParserCountOptimize = new JsqlParserCountOptimize();
                SqlInfo sqlInfo = jsqlParserCountOptimize.optimizeSql(metaObject, sql);
                String optimizeSql = sqlInfo.getSql();
                String newSql = StringUtils.replaceFirst(optimizeSql, "COUNT\\(1\\)", "COUNT(1) AS total");
                return sqlInfo.setSql(newSql);
            }
        });
        return paginationInterceptor;
    }

b.源码修改

由于ShardingSphere对SQLserver支持很差，分页查询有问题，需要改一下源码，将

TopAndRowNumberDecoratorMergedResult类中的

    @Override
    public boolean next() throws SQLException {
        if (skipAll) {
            return false;
        }
        if (!paginationContext.getActualRowCount().isPresent()) {
            return getMergedResult().next();
        }
        return rowNumber++ <= paginationContext.getActualRowCount().get() && getMergedResult().next();
    }

改为：

    @Override
    public boolean next() throws SQLException {
        if (skipAll) {
            return false;
        }
        if (!pagination.getActualRowCount().isPresent()) {
            return getMergedResult().next();
        }
        // 这里有BUG,获取实际行数getActualRowCount方法获取到的是pagesize,而不是实际行数，需要加上实际偏移量才是
        return (rowNumber++ <= (pagination.getActualRowCount().get() + pagination.getActualOffset())) && getMergedResult().next();
    }

然后打一个定制版本出包，出包install以后引入(版本名可以自己取，此处是5.3.1-custom)：

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core</artifactId>
            <version>5.3.1</version>
            <exclusions>
                <exclusion>
                    <artifactId>shardingsphere-sharding-core</artifactId>
                    <groupId>org.apache.shardingsphere</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-sharding-core</artifactId>
            <version>5.3.1-custom</version>
        </dependency>

然后就可以正常使用，正常分页查询了。