嗨,我注意到一楼提到, Hint 现状是 通过 java api 方式操作.
现状应该指的是已经发布的shardingsphere 5.0.0吧? 只能通过行表达式配置的Hint分片算法(HintInlineShardingAlgorithm,就是基于行表达式的Hint分片算法) 和Hint强制路由有什么联系呢?
https://shardingsphere.apache.org/document/5.0.0/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding/#hint-行表达式分片算法
强制路由 :: ShardingSphere (apache.org)
HintInlineShardingAlgorithm
是 shardingsphere 基于Hint 强制路由提供的一种内置算法,使用的话必须要基于 HintManager api 使用。当然你也可以实现自己的 Hint 算法。
HintManager结合proxy要怎么使用呀?
https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/special-api/sharding/hint/#使用-sql-注释的方式
可以在 proxy 中直接使用 sql 注释
是不是用了什么客户端连接?影响使用吗?
客户端连接可能会有一些兼容性问题,还在持续优化,可以用最新版 proxy 试试。
嗯嗯,如果不影响使用的话,没关系的。推荐直接用 mysql 命令行工具连接
使用hint仍然需要配置逻辑表和实际表吗?
有没有方案不配置tables,只配置databases,然后通过某种方式指定sql指定的数据库?
尝试了一下hint manager的模式,可以达成业务
但是配置里似乎必须配置一个规则和一张表,否则会报错
请看一下我哪里写的有问题
shardingsphere:
datasource:
names: shard_${0..2}
shard_0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:///shard1?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8&useSSL=false
username: r
password:
shard_1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:///shard2?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8&useSSL=false
username: r
password:
shard_2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql:///shard3?allowMultiQueries=true&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2b8&useSSL=false
username: r
password:
rules:
sharding:
default-database-strategy:
hint:
sharding-algorithm-name: com.yangxu.toolsservice.config.TestShardingAlgorithm
tables:
attend_em_info:
actual-data-sources: shard_${0..2}.attend_em_info
database-strategy:
hint:
sharding-algorithm-name: com.yangxu.toolsservice.config.TestShardingAlgorithm
props:
sql-show: true
是的,使用 hintManager 必须要配合 shardingRule 使用
你好, 在使用 SQL hint 方式的时候如何进行分表呢?我尝试简单的分表好像不行
spring:
shardingsphere:
sharding:
default-data-source-name: base
datasource:
names: base, base-1, base-2
base:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://1:3306/base?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=true
username:
password:
base-1:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://2:3306/slave_1?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=true
username:
password:
base-2:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl: jdbc:mysql://3:3306/slave_2?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=true
username:
password:
rules:
sql-parser:
sql-comment-parse-enabled: true
sharding:
sharding-algorithms:
# 简单分片
inline_algorithm:
type: MOD
props:
sharding-count: 4
tables:
test_inline:
actualDataNodes: base.test_inline_$->{0..3}
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: inline_algorithm
数据库表如下:
执行日志如下:
2022-09-08 09:40:38.051 INFO 40704 --- [ main] ShardingSphere-SQL : SQLStatement: MySQLSelectStatement(table=Optional.empty, limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
2022-09-08 09:40:38.051 INFO 40704 --- [ main] ShardingSphere-SQL : Actual SQL: base-2 ::: /* ShardingSphere hint: dataSourceName=base-2 */
select
id, info_name, create_time
from test_inline
where id in
(
?
,
?
) ::: [1, 2]
2022-09-08 09:40:38.054 DEBUG 40704 --- [ main] c.z.d.s.m.TestInlineMapper.listByIds : <== Total: 0
2022-09-08 09:40:38.054 INFO 40704 --- [ main] c.zigaai.demo.ShardingSphereTestComplex : list: []
2022-09-08 09:40:38.054 INFO 40704 --- [ main] c.zigaai.demo.ShardingSphereTestComplex : 耗时: 4
可以正常路由到目标库,但是分表不正常,是哪里配置配错了吗?