Subquery returns more than 1 row（查询结果返回多行）

最新推荐文章于 2022-01-26 10:46:04 发布
最新推荐文章于 2022-01-26 10:46:04 发布
阅读量4.4k 收藏 1
点赞数 4
分类专栏： 常见异常
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41553871/article/details/90212878
版权

异常信息

org.apache.ibatis.logging.jdbc.BaseJdbcLogger DEBUG ====> Parameters: 06f64c2874a911e98a2ff48e38f14301(String) 
org.springframework.beans.factory.xml.XmlBeanDefinitionReader INFO Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 
org.springframework.jdbc.support.SQLErrorCodesFactory INFO SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana] 
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter ERROR An internal error occurred while trying to authenticate the user. 
org.springframework.security.authentication.InternalAuthenticationServiceException: 
### Error querying database.  Cause: java.sql.SQLException: Subquery returns more than 1 row
### The error may exist in ssm_dao/IRoleDao.java (best guess)
### The error may involve ssm_dao.IRoleDao.selByUsersId-Inline
### The error occurred while setting parameters
### SQL: select * from role where id=(select roleid from users_role where userId=?)
### Cause: java.sql.SQLException: Subquery returns more than 1 row
; bad SQL grammar []; nested exception is java.sql.SQLException: Subquery returns more than 1 row
	at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:119)
	at org.springframework.security.authentication.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:144)
	at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:174)
	at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:199)
	at org.springframework.security.web.authentication.UsernamePasswordAuthenticationFilter.attemptAuthentication(UsernamePasswordAuthenticationFilter.java:94)
	at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:212)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215)
	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLException: Subquery returns more than 1 row
### The error may exist in ssm_dao/IRoleDao.java (best guess)
### The error may involve ssm_dao.IRoleDao.selByUsersId-Inline
### The error occurred while setting parameters
### SQL: select * from role where id=(select roleid from users_role where userId=?)
### Cause: java.sql.SQLException: Subquery returns more than 1 row
; bad SQL grammar []; nested exception is java.sql.SQLException: Subquery returns more than 1 row
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
	at com.sun.proxy.$Proxy40.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:167)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy53.selByUsernamePassword(Unknown Source)
	at ssm_service.Impl.IUserServiceImpl.loadUserByUsername(IUserServiceImpl.java:42)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:338)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy54.loadUserByUsername(Unknown Source)
	at org.springframework.security.authentication.dao.DaoAuthenticationProvider.retrieveUser(DaoAuthenticationProvider.java:104)
	... 36 more
Caused by: java.sql.SQLException: Subquery returns more than 1 row
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:995)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy64.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at org.apache.ibatis.executor.loader.ResultLoader.selectList(ResultLoader.java:81)
	at org.apache.ibatis.executor.loader.ResultLoader.loadResult(ResultLoader.java:70)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getNestedQueryMappingValue(DefaultResultSetHandler.java:768)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getPropertyMappingValue(DefaultResultSetHandler.java:460)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.applyPropertyMappings(DefaultResultSetHandler.java:436)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.getRowValue(DefaultResultSetHandler.java:399)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValuesForSimpleResultMap(DefaultResultSetHandler.java:351)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleRowValues(DefaultResultSetHandler.java:326)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSet(DefaultResultSetHandler.java:299)
	at org.apache.ibatis.executor.resultset.DefaultResultSetHandler.handleResultSets(DefaultResultSetHandler.java:192)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
	at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
	at com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:143)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy62.query(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
	... 57 more

**从异常中可以看出
*

SQL: select * from role where id=(select roleid from users_role where userId=?)

sql语句出现的异常并发现返回多行**
很明显既然返回多行就不能用id=（…）
必须换成id in（…）

阮世
关注
专栏目录
【异常】MySQL提示[Err] 1242 - Subquery returns more than 1 row
本本本添哥
04-22 3020
SQL中子查询返回多行的错误。
MySQL出错信息: Subquery returns more than 1 row及其解决方法
weixin_30379911的博客
04-26 599
练习MySQL联表查询时遇到这样一道题 　- 问题: 查询"生物课程"比"物理课程"成绩高的所有学生的相关信息 　-出错指令: 1 SELECT student.sid AS '学号', student.sname AS '姓名', course.cname AS '课程', score.num AS '成绩' 2 FROM student INNER JOIN cour...
[Err] 1242 - Subquery returns more than 1 row
游海东的技术专栏
02-15 5587
1、错误描述[SQL]SELECT queryStuName(2016010101); [Err] 1242 - Subquery returns more than 1 row2、错误原因BEGIN DECLARE stuName VARCHAR(100); SET stuName = (SELECT stu_name FROM t_student_info WHERE stu_no =
Subquery returns more than 1 row
热门推荐
菜鸟的未来
12-08 8万+
Subquery returns more than 1 row表示子查询返回了多行数据 例如: select * from table1 where table1.colums=(select columns from table2) 解决方法 select * from table1 where table1.colums=any(select columns from table2)
MYSQL --Subquery returns more than 1 row
倾城之恋的专栏
02-13 1243
Subquery returns more than 1 row表示子查询返回了多行数据 例如: select * from table1 where table1.colums=(select columns from table2) 解决方法 select * from table1 where table1.colums=any(select columns from table2)
【Mybatis】java.sql.SQLException: Subquery returns more than 1 row
慕白Lee的博客
01-12 3706
在重复写入时会出现这种问题, 可通过去掉重复数据解决。通过在写入时加逻辑判断或者外键防止数据重复写入。
> Subquery returns more than 1 row
08-17
如果你的子查询返回了多行结果，主查询无法处理这种情况，就会抛出 "Subquery returns more than 1 row" 错误。 要解决这个问题，你可以考虑以下几种方法： 1. 重新设计查询：确保子查询只返回单个值或一行结果。 ...
子查询 Subquery returns more than 1 row
最新发布
08-28
当你的子查询返回多于一行的结果时，会出现"Subquery returns more than 1 row"的错误。这个错误说明你的子查询的筛选条件导致了多行结果。解决这个问题的方法是检查子查询的筛选条件，确保它只返回一行结果。你可以...
出现java.sql.SQLException: Subquery returns more than 1 row错误的原因，解决方法
m0_63733486的博客
12-02 5457
首先，原因有以下可能： ①你在Navicat for MySQL里面建立的存储过程中设置的参数与你所更新的表里面的表重名了，如下： 那就很简单了，你只需要将名字改的不一样就好了，如下： ②还有可能就是你在建立数据库时，建立的语句没有关，你在每次查找你要的数据的时候同时建立了一个，这样找到的就不唯一了，这时候你就需要select一下看看是不是单独的一行还是多行，如果是多行的话就把建立的数据库语句关掉就好了， 终极解决大法 在你的where后面加上（limit 1）就解决了， 但是只能解决软件.
[mysql语法错误]-Subquery returns more than 1 row
qq_40151384的博客
08-30 467
今天在学 子查询中遇到一个问题 当返回值为多值时 ,mysql抛出错误 表示子查询返回了多行数据 如下: [21000][1242] (conn=11) Subquery returns more than 1 row 源代码为: select * from emp where depno =(select deptno from dept where loc ='二区'); 经查 只需要在返回值那里添加 any 即可 解决方案 如: select * from emp where depno =
Percona 8.0 bug: Subquery returns more than 1 row
FaN()
04-28 370
这是一个Percona Server8.0.22下使用MGR+ProxySQL时遇到的bug 使用mydumper备份mgr时发现ProxySQL报错了 # cat proxysql.log | grep -i subquery 2021-04-28 15:14:22 MySQL_HostGroups_Manager.cpp:3875:update_group_replication_set_offline(): [WARNING] Group Replication: setting host 172.1
MySQL:Subquery returns more than 1 row
生而为人我很抱歉
09-08 1713
在子查询时，用了父查询的单行结果去匹配子查询的多行结果导致。
MySQL---Subquery returns more than 1 row
Nothing is impossible to a willing heart.
01-26 1426
例如： select * from user_facility where user_id = 60040012 and facility_id = (select facility_id from scene_facility_room where room_id = 5); 会报错 Subquery returns more than 1 row 原因： 因为使用的字段使用的=，只符合select语句只查出一条记录的情况。 如果查出的记录是多条记录，这时上述语句会报Subquery return
MYSQL:Subquery returns more than 1 row
rxs的博客
05-08 404
列表内容 select a.id,a.name,(select b.time from A a,B b where a.id = b.id) from A left join C c on … 查看日志出现如下错误： Subquery returns more than 1 row 百度 Subquery returns more than 1 row表示子查询返回了多行数据 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值