ShardingSphere：查询报错：Actual table `数据源名称.表名` is not in table rule configuration

已于 2025-05-16 10:34:43 修改
阅读量908 收藏 11
点赞数 7
分类专栏： ShardingSphere Java异常处理 文章标签： java
于 2025-05-16 10:06:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_52116015/article/details/147999036
版权

目录

简介

1、使用ShardingSphere框架，版本为5.2.1

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

2、SQL涉及多表链接查询时会报错

3、表分片字段：subTableDate

异常信息

2025-05-13 11:06:34.072 - [ERROR] - [http-nio-8091-exec-1] - [o.s.boot.web.servlet.support.ErrorPageFilter - line:182]: Forwarding to error page from request [/home/getVisitorQA] due to exception [
### Error querying database.  Cause: java.sql.SQLException: Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
### The error may exist in file [/home/xxx/devtest/robot_llm/tomcat10-robotMg_llm-8091/webapps/xxxRobotMg-llm/WEB-INF/classes/mappings/visitor/RobotMessagereplayMapper.xml]
### The error may involve com.xxx.mapper.visitor.RobotMessagereplayMapper.selectVisitorQA-Inline
### The error occurred while setting parameters
### SQL: SELECT if(isOld=1,2,1) vType,if(isOld=1,'老用户咨询量','新用户咨询量') vTypeName,count(1) qty,   SUM(if(a.hours=0,1,0)) qty0,   SUM(if(a.hours=1,1,0)) qty1,   SUM(if(a.hours=2,1,0)) qty2,   SUM(if(a.hours=3,1,0)) qty3,   SUM(if(a.hours=4,1,0)) qty4,   SUM(if(a.hours=5,1,0)) qty5,   SUM(if(a.hours=6,1,0))   qty6,   SUM(if(a.hours=7,1,0)) qty7,   SUM(if(a.hours=8,1,0)) qty8,   SUM(if(a.hours=9,1,0)) qty9,   SUM(if(a.hours=10,1,0)) qty10,   SUM(if(a.hours=11,1,0)) qty11,   SUM(if(a.hours=12,1,0)) qty12,   SUM(if(a.hours=13,1,0)) qty13,   SUM(if(a.hours=14,1,0)) qty14,   SUM(if(a.hours=15,1,0)) qty15,   SUM(if(a.hours=16,1,0)) qty16,   SUM(if(a.hours=17,1,0)) qty17,   SUM(if(a.hours=18,1,0)) qty18,   SUM(if(a.hours=19,1,0)) qty19,   SUM(if(a.hours=20,1,0)) qty20,   SUM(if(a.hours=21,1,0)) qty21,   SUM(if(a.hours=22,1,0)) qty22,   SUM(if(a.hours=23,1,0)) qty23   FROM t_robot_sessionmsg c   LEFT JOIN t_robot_messageReplay a ON a.SessionID=c.SessionID where a.subTableDate   between ? and ?        and a.companyId = ?       GROUP BY c.isOld
### Cause: java.sql.SQLException: Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
; Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.]
org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: java.sql.SQLException: Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
### The error may exist in file [/home/xxx/devtest/robot_llm/tomcat10-robotMg_llm-8091/webapps/xxxRobotMg-llm/WEB-INF/classes/mappings/visitor/RobotMessagereplayMapper.xml]
### The error may involve com.xxx.mapper.visitor.RobotMessagereplayMapper.selectVisitorQA-Inline
### The error occurred while setting parameters
### SQL: SELECT if(isOld=1,2,1) vType,if(isOld=1,'老用户咨询量','新用户咨询量') vTypeName,count(1) qty,   SUM(if(a.hours=0,1,0)) qty0,   SUM(if(a.hours=1,1,0)) qty1,   SUM(if(a.hours=2,1,0)) qty2,   SUM(if(a.hours=3,1,0)) qty3,   SUM(if(a.hours=4,1,0)) qty4,   SUM(if(a.hours=5,1,0)) qty5,   SUM(if(a.hours=6,1,0))   qty6,   SUM(if(a.hours=7,1,0)) qty7,   SUM(if(a.hours=8,1,0)) qty8,   SUM(if(a.hours=9,1,0)) qty9,   SUM(if(a.hours=10,1,0)) qty10,   SUM(if(a.hours=11,1,0)) qty11,   SUM(if(a.hours=12,1,0)) qty12,   SUM(if(a.hours=13,1,0)) qty13,   SUM(if(a.hours=14,1,0)) qty14,   SUM(if(a.hours=15,1,0)) qty15,   SUM(if(a.hours=16,1,0)) qty16,   SUM(if(a.hours=17,1,0)) qty17,   SUM(if(a.hours=18,1,0)) qty18,   SUM(if(a.hours=19,1,0)) qty19,   SUM(if(a.hours=20,1,0)) qty20,   SUM(if(a.hours=21,1,0)) qty21,   SUM(if(a.hours=22,1,0)) qty22,   SUM(if(a.hours=23,1,0)) qty23   FROM t_robot_sessionmsg c   LEFT JOIN t_robot_messageReplay a ON a.SessionID=c.SessionID where a.subTableDate   between ? and ?        and a.companyId = ?       GROUP BY c.isOld
### Cause: java.sql.SQLException: Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
; Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:118)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:93)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:347)
	at jdk.proxy3/jdk.proxy3.$Proxy117.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:194)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:141)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
	at jdk.proxy3/jdk.proxy3.$Proxy316.selectVisitorQA(Unknown Source)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at jdk.proxy3/jdk.proxy3.$Proxy317.selectVisitorQA(Unknown Source)
	at com.xxx.service.Impl.visitor.RobotMessagereplayServiceImpl.getVisitorQA(RobotMessagereplayServiceImpl.java:164)
	at com.xxx.controller.rpt.HomeController.getVisitorQA(HomeController.java:184)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:355)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:173)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:768)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:720)
	at com.xxx.controller.rpt.HomeController$$SpringCGLIB$$0.getVisitorQA(<generated>)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:255)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:188)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:926)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:831)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1089)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:979)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885)
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:110)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at com.xxx.filter.XssFilter.doFilter(XssFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:108)
	at org.springframework.security.web.FilterChainProxy.lambda$doFilterInternal$3(FilterChainProxy.java:231)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:365)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:117)
	at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:126)
	at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:120)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:131)
	at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:85)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:100)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:179)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at com.xxx.jwt.JwtAuthenticationTokenFilter.doFilterInternal(JwtAuthenticationTokenFilter.java:145)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:107)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:93)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:91)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:82)
	at org.springframework.security.web.context.SecurityContextHolderFilter.doFilter(SecurityContextHolderFilter.java:69)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:62)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:374)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:233)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:191)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.servlet.handler.HandlerMappingIntrospector.lambda$createCacheFilter$3(HandlerMappingIntrospector.java:195)
	at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:113)
	at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:74)
	at org.springframework.security.config.annotation.web.configuration.WebMvcSecurityConfiguration$CompositeFilterChainProxy.doFilter(WebMvcSecurityConfiguration.java:230)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:362)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:278)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:124)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:99)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:117)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:115)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:905)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
	at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLException: Actual table `xxxrobot0.t_robot_messageReplay_202505` is not in table rule configuration.
	at org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:54)
	at org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:51)
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:409)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:80)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:65)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:336)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:158)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:110)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:90)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:154)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:142)
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:333)
	... 136 common frames omitted

排查

1、单表查询SQL分片正常、查询正常

2、其他多表链接查询的SQL也正常

3、只有以下SQL查询报错，所以问题是出在SQL上

补充：以下SQL在其他ShardingSphere版本的框架上是可以正常执行的，所以问题也与ShardingSphere的框架版本有关

SELECT 
	if(isOld=1,2,1) vType,
	if(isOld=1,'老用户咨询量','新用户咨询量') vTypeName,
	count(1) qty,
	SUM(if(a.hours=0,1,0)) qty0,
	SUM(if(a.hours=1,1,0)) qty1,
	SUM(if(a.hours=2,1,0)) qty2,
	SUM(if(a.hours=3,1,0)) qty3,
	SUM(if(a.hours=4,1,0)) qty4,
	SUM(if(a.hours=5,1,0)) qty5,
	SUM(if(a.hours=6,1,0)) qty6,
	SUM(if(a.hours=7,1,0)) qty7,
	SUM(if(a.hours=8,1,0)) qty8,
	SUM(if(a.hours=9,1,0)) qty9,
	SUM(if(a.hours=10,1,0)) qty10,
	SUM(if(a.hours=11,1,0)) qty11,
	SUM(if(a.hours=12,1,0)) qty12,
	SUM(if(a.hours=13,1,0)) qty13,
	SUM(if(a.hours=14,1,0)) qty14,
	SUM(if(a.hours=15,1,0)) qty15,
	SUM(if(a.hours=16,1,0)) qty16,
	SUM(if(a.hours=17,1,0)) qty17,
	SUM(if(a.hours=18,1,0)) qty18,
	SUM(if(a.hours=19,1,0)) qty19,
	SUM(if(a.hours=20,1,0)) qty20,
	SUM(if(a.hours=21,1,0)) qty21,
	SUM(if(a.hours=22,1,0)) qty22,
	SUM(if(a.hours=23,1,0)) qty23
FROM t_robot_sessionmsg c
LEFT JOIN t_robot_messageReplay a ON a.SessionID=c.SessionID 
where a.subTableDate between 'xxx' and 'xxx'
and a.companyId = 'xxx'
GROUP BY c.isOld

4、发现使用分片键的表不是主表c，而是外链表a

-- a表是外链表，c表才是主表
where a.subTableDate between 'xxx' and 'xxx'

5、将主表的分片键作为查询条件，SQL可正常查询

-- 改为：c.subTableDate
where c.subTableDate between 'xxx' and 'xxx'

原因

SQL查询where条件没有涉及主表分片键

补充：以上SQL在其他ShardingSphere版本的框架上是可以正常执行的，所以问题也与ShardingSphere的框架版本有关

解决

将主键分片键加入SQL查询where条件

【OpenHarmony4.1 之 U-Boot 2024.07源码深度解析】006 - Makefile 编译脚本 逐行深度解析
|~~~热爱生活、努力学习的小伙汁~~~|
06-15 1052
【OpenHarmony4.1 之 U-Boot 2024.07源码深度解析】006 - Makefile 编译脚本 逐行深度解析
ShardingJDBC之分片策略、数据脱敏（4.x）
weixin_38289303的博客
07-24 1542
具体简介就不多逼逼，直接上代码。 private ShardingRuleConfiguration buildShardingRuleConfiguration(){ /** * 配置表规则 */ TableRuleConfiguration orderTableRuleConfiguration = new TableRuleConfiguration("t_order","db${0..2}.t_order_${1..2}"); // 配置分库策略（
Sharding动态按月分表
no_away的博客
10-23 3879
有功能需要用到按月分表,大体记录一下,只是简单测试了一下,可能会有问题。。。。。。 springboot+mybatis+sharding，要是用分页的部分是单独配置的数据源,不想让主要业务也走sharding分页。 相关配置如下 mybatis配置 // 配置扫描要使用sharding的mapper的的基本信息 @Configuration @Mappe...
ShardingSphere分库分表核心原理精讲第二节 应用集成和配置驱动
fegus的博客
05-24 1301
04 应用集成：在业务系统中使用 ShardingSphere 的方式有哪些？ 在上一课时中，我详细介绍了 ShardingSphere 与 JDBC 规范之间的兼容性关系，我们知道 ShardingSphere 对 JDBC 规范进行了重写，并嵌入了分片机制。基于这种兼容性，开发人员使用 ShardingSphere 时就像在使用 JDBC 规范所暴露的各个接口一样。这一课时，我们将讨论如何在业务系统中使用 ShardingSphere 的具体方式。 如何抽象开源框架的应用方式？ 当我们自己在设计和实现
Sharding JDBC 实战 —— 使用相关 API 实现分库分表
Java是世界上最好的语言
01-05 1385
本文主要演示Sharding JDBC 相关JAVA API 是如何使用的，通过实际案例让大家能掌握如何分库分表。 官网：https://shardingsphere.apache.org/index_zh.html 1. 项目搭建 通过IDEA直接创建一个Spring Boot 或者 Mavne 项目即可。 2. 相关依赖 引入相关依赖，如：连接池依赖、数据库依赖以及最重要的shardingsphere-jdbc依赖。 <dependencies> <dependency&g
分库分表实战及中间件（二）
.
03-30 625
分库分表实战及中间件（二） 前言 分库分表实战及中间件（一）中我们使用了在代码中使用硬编码的方式手动定义路由规则以及获取不同表进行分表设计。
DATAPUMP导入报ORA-39083和ORA-01917，用户或角色不存在 报错 ORA-39095: 转储文件空间已耗尽: 无法分配 8192 字节
weixin_tank88921的博客
07-30 1369
大概意思就是说oracle的安全机制使得不允许多个写进程同时写一个dump文件，如果此时你的parallel(并发)数值大于（实际导出数据量/fileszie）的数值，那么就会有一些进程闲置，他们只能等待写进程也相当于多个进程在抢占一个进程。在下面的OBJECT_PATH列中，“ROLE” 和 “ROLE_GRANT” 仅出现在 DATABASE_EXPORT 中。2、忽略，也可以手动执行失败的sql，可以执行成功。这个参数默认是Y，如果改成N，那么导入的时候会把各个对象重新赋予新的OID。
Apache ShardingSphere 5.0.0 内核优化及升级指南
SphereEX的博客
11-26 611
经过近两年时间的优化和打磨，Apache ShardingSphere 5.0.0 GA 版终于在本月正式发布，相比于 4.1.1 GA 版，5.0.0 GA 版在内核层面进行了大量的优化。首先，基于可插拔架构对内核进行了全面改造，内核中的各个功能可以任意组合并叠加使用。其次，为了提升 SQL 分布式查询能力，5.0.0 GA 版打造了全新的Federation 执行引擎，来满足用户复杂的业务场景。此外，5.0.0 GA 版在内核功能 API 层面也进行了大量优化，旨在降低用户使用这些功能的成本。本文将为.
【实时数据模型重构】：提升Spring Data JPA的查询性能
[【实时数据模型重构】：提升Spring Data JPA的查询性能](https://opengraph.githubassets.com/09417ae1bca19c5d46176b2988ed906e8a352728b6a423ab2539db57cdfe393a/in28minutes/java-best-practices) # 摘要 本文...
十四、系统扩容——数据库分库分表示例
最新发布
流楚丶格念的博客
08-30 565
本地事务：事务仅限于单个数据库连接，适用于简单场景，如单表操作或同一数据源的多表操作，性能较高，但不支持跨数据库的事务操作。XA 分布式事务：通过引入分布式事务协调器，实现跨数据库的事务管理，保证分布式环境下的数据一致性，但相对性能较低，适用于对一致性要求高的场景。本地事务适合单库或单表操作，配置简单，性能高，但在分库分表场景下，不能保证全局一致性。XA 分布式事务通过协调器实现跨库事务管理，保证数据一致性，适用于复杂业务场景，但引入了一定的性能开销。
ShardingSphere 针对SQL IN语法问题小记
热门推荐
小熙的博客
05-27 2万+
ShardingSphere支持IN语法查询，但也存在问题。 官网说明：https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sharding/ 当查询多条数据时，入参为分片键，进行测试 当数据路由都在一个库同一张表时，会去相应表进行查询（分片键：1504, 1508） 2020-05-26 22:05:01.331 INFO 6476 --- [nio-8080-exec-8] Shardi
Sharding-JDBC
hanxiaozhang的博客
07-06 994
Sharding-JDBC是一个在客户端的分库分表工具。它是一个轻量级Java框架，在Java的JDBC层提供的额外服务。ShardingSphere提供标准化的数据分片、分布式事务和数据治理功能。
《Sharding-JDBC》——数据库分表+数据库脱敏实现方案
tomatocc的博客
11-15 1806
这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 欢迎使用Ma...
分布式 | DBLE 分片算法之 hash 分片
ActionTech的博客
11-02 700
作者：赵红杰 DBLE 项目测试负责人，主导分布式中间件的测试，在测试中不断发现产品和自身的 bug。迭代验证，乐在其中。 本文来源：原创投稿 *爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。 背景 社区有大佬分享过跳增 hash 的文章，但是当时并不理解跳增 hash 使用的场景。刚接触分布式数据库中间件 dble 的时候，最迷惑的概念之一是 hash 分片算法。看到哈希，第一印象是散列表，感觉是存储相关的。hash 一个重要的特征是需要不同输入产生不同输出，但是在分片.
Apache ShardingSphere分表的简单使用和常见问题
瑶山的博客
01-03 4617
版本：4.xApache ShardingSphere 是一款分布式的数据库生态系统，可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。场景: 分表规则字段范围查询且有拼接或格式处理解决: 去掉sql上的字段格式化处理, 在服务层对传入参数进行处理失效示例代码成功示例代码持续更新ing!
分库分表神器 Sharding-JDBC（按天分表案例）
运气不好努力来凑
06-11 2997
shardingJDBC按天动态分表入库完整案例。拿来即用。
Sharing-jdbc分库分表功能
WannaRunning的博客
01-18 2212
官方文档ApacheShardingSphere通过提供多样化的表类型,适配不同场景下的数据分片需求。
分库分表如何管理不同实例中几万张分片表？
m0_69632475的博客
03-04 1079
框架成员中的和都提供了自动化管理分片表的功能，可以统一维护大量的分片表，避免了手动编写脚本和维护分片表的繁琐工作，极大程度减少分库分表的开发和维护成本，提升效率和可靠性。这里咱们先使用来实际操作一下，方式后续会有单独的文章详细讲解，就不在这里展开了。上边为逻辑表配置分片规则，应用程序内执行对逻辑表的DDL操作，就可以很轻松的管理分片表。不过，默认的分片管理还是有局限性的，我们在设计分片规则时往往会根据不同的业务维度来划分，例如按天、月、按季度生成分片表并分布到不同数据源中等。
ShardingSphere基本介绍及核心概念
Kiven_ch的博客
07-25 2万+
文章目录关于分库分表背景描述遇到的问题如何解决垂直拆分水平拆分(数据分片)ShardingSphere介绍Sharding-JDBCSharding-JDBC主要功能：Sharding-JDBC 内部结构：Sharding-JDBC初始化流程：Sharding-JDBC 使用过程：ShardingSphere核心概念表概念分片概念分片键分片算法（ShardingAlgorithm）分片策略分片策略配置流程剖析SQL使用规范分页查询行表达式分布式主键 关于分库分表 背景描述 最初设计的系统只用了单机数据库
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值