SpringBoot

2024-07-06 13:25| 来源: 网络整理| 查看: 265

说明

Spring Boot 项目使用 ShardingSphere-JDBC，默认情况下会接管配置的全部数据源，这会导致一些问题比如，所有的 sql 执行都会走 ShardingSphere 的分库或者分别的逻辑判断最重要的是，ShardingSphere 不支持的 SQL 会直接报错比如： https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/unsupported-items/还有： https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql

12345678910-- SELECT子句暂不支持使用*号简写及内置的分布式主键生成器INSERT INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? -- SELECT子句暂不支持使用*号简写及内置的分布式主键生成器REPLACE INTO tbl_name (col1, col2, …) SELECT * FROM tbl_name WHERE col3 = ? -- 会导致全路由SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2 UNIONSELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2 UNION ALLSELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ? -- 查询列是函数表达式时,查询列前不能使用表名;若查询表存在别名,则可使用表的别名SELECT MAX(tbl_name.col1) FROM tbl_name

这是不能忍的情况

解决方案

官方已经给出的解决方案：FQA

如果只有部分数据库分库分表，是否需要将不分库分表的表也配置在分片规则中？回答：

是的。因为ShardingSphere是将多个数据源合并为一个统一的逻辑数据源。因此即使不分库分表的部分，不配置分片规则ShardingSphere即无法精确的断定应该路由至哪个数据源。但是ShardingSphere提供了两种变通的方式，有助于简化配置。

方法1：配置default-data-source，凡是在默认数据源中的表可以无需配置在分片规则中，ShardingSphere将在找不到分片数据源的情况下将表路由至默认数据源。

方法2：将不参与分库分表的数据源独立于ShardingSphere之外，在应用中使用多个数据源分别处理分片和不分片的情况。

方法 1 的配置方式不适合我我选择了方法 2具体做法如下：

操作依赖引入1234567891011121314151617 org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter 5.0.0-beta log4j log4j com.baomidou dynamic-datasource-spring-boot-starter 项目配置1234567891011121314151617181920212223242526272829303132333435363738spring: application: name: im datasource: dynamic: primary: im strict: false datasource: im: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxxxxx:xxxx/im?useUnicode=true&characterEncoding=utf-8 type: com.zaxxer.hikari.HikariDataSource username: xxxx password: xxxx shardingsphere: datasource: names: sharding-sphere sharding-sphere: driver-class-name: com.mysql.cj.jdbc.Driver jdbc-url: jdbc:mysql://xxxxxx:xxxx/sharding-sphere?useUnicode=true&characterEncoding=utf-8 type: com.zaxxer.hikari.HikariDataSource username: xxxx password: xxxx rules: sharding: tables: message: actual-data-nodes: sharding-sphere.message_$->{0..1}_$->{2021..2030}${(1..12).collect{t ->t.toString().padLeft(2,'0')}} table-strategy: complex: sharding-columns: conversation_type, timestamp sharding-algorithm-name: message-table-strategy sharding-algorithms: message-table-strategy: type: MessageComplexKeysShardingAlgorithm props: { } props: sql-show: true

配置了 2 个数据库im 为主库：正常增删改查的数据库sharding-sphere：专为分库分表的使用的数据库

还配置一个分表规则，分表策略为自定义策略 MessageComplexKeysShardingAlgorithm

自定义策略

shardingsphere-jdbc 5.x 的分表策略使用的是 SPI 机制

具体就是在 resources/META-INF/services 目录下新增配置文件 org.apache.shardingsphere.sharding.spi.ShardingAlgorithm将 shardingsphere-jdbc 5.x 自带的策略和自定义的策略加入进去

如下

1234567891011121314151617181920212223242526272829## Licensed to the Apache Software Foundation (ASF) under one or more# contributor license agreements. See the NOTICE file distributed with# this work for additional information regarding copyright ownership.# The ASF licenses this file to You under the Apache License, Version 2.0# (the "License"); you may not use this file except in compliance with# the License. You may obtain a copy of the License at## http://www.apache.org/licenses/LICENSE-2.0## Unless required by applicable law or agreed to in writing, software# distributed under the License is distributed on an "AS IS" BASIS,# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.# See the License for the specific language governing permissions and# limitations under the License.# org.apache.shardingsphere.sharding.algorithm.sharding.inline.InlineShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.mod.ModShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.mod.HashModShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.range.VolumeBasedRangeShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.range.BoundaryBasedRangeShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.datetime.AutoIntervalShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.datetime.IntervalShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.classbased.ClassBasedShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.complex.ComplexInlineShardingAlgorithmorg.apache.shardingsphere.sharding.algorithm.sharding.hint.HintInlineShardingAlgorithmcom.hfky.im.wildfirechat.msgforward.policy.MessageComplexKeysShardingAlgorithm

自定义策略如下：

123456789101112131415161718192021222324252627282930public class MessageComplexKeysShardingAlgorithm implements ComplexKeysShardingAlgorithm

【本文地址】

公司简介

联系我们