MySQL server version for the right syntax to use near 'info where info.stu_id = 1' at line 1

1、错误描述

### The error occurred while setting parameters
### SQL: delete from t_student_info info where info.stu_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'info where info.stu_id = 1' at line 1
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'info where info.stu_id = 1' at line 1
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at core.util.CustomSqlSessionTemplate$SqlSessionInterceptor.invoke(CustomSqlSessionTemplate.java:316)
	at com.sun.proxy.$Proxy21.delete(Unknown Source)
	at core.util.CustomSqlSessionTemplate.delete(CustomSqlSessionTemplate.java:226)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:852)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:624)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.eclipse.jetty.continuation.ContinuationFilter.doFilter(ContinuationFilter.java:137)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
	at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
	at core.security.CRPAuthenticationFilter.doFilterInternal(CRPAuthenticationFilter.java:282)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66)
	at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449)
	at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365)
	at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)
	at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)
	at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)
	at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)
	at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:956)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2517)
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2506)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'info where info.stu_id = 1' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
	at com.mysql.jdbc.Util.getInstance(Util.java:360)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199)
	at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.execute(PreparedStatementWrapper.java:680)

2、错误原因

<delete id="delStudent" parameterType="List">
          delete from t_student_info info where info.stu_id = #{stuId}
</delete>

在Mybatis配置删除SQL语句时,给数据库表起个别名,导致报错


3、解决办法

<delete id="delStudent" parameterType="List">
          delete from t_student_info where stu_id = #{stuId}
</delete>


<think>好的,我现在需要解决用户遇到的MySQL 1064错误,具体错误信息是“SQL syntax error near 'where records.acq_id = 4'”。首先,我需要理解这个错误的常见原因,然后根据用户提供的参考引用中的例子来分析可能的解决方案。 首先,用户提供的引用中有几个例子,比如使用了错误的OPTION SQL_SELECT_LIMIT或者错误的表别名。例如,引用[4]中的错误是因为在delete语句中使用了别名“info”但没有正确使用语法。正确的写法应该是“delete info from t_student_info info where ...”,而用户可能漏掉了这部分。所以,可能用户当前的SQL语句也存在类似的问题,即在delete或update语句中错误地使用了别名而没有正确指定。 接下来,用户的具体错误是在where子句附近出现语法错误。可能的情况包括: 1. 在delete或update语句中使用了表别名但没有正确声明。 2. SQL语句中存在拼写错误或缺少关键字,比如缺少FROM关键字。 3. 保留字冲突,比如使用了MySQL的保留字作为表名或列名但没有转义。 4. 标点符号错误,比如缺少引号或括号不匹配。 根据引用中的例子,特别是引用[4],用户可能在写delete语句时,错误地使用了别名。例如,正确的delete语句使用别名的格式应该是“DELETE alias FROM table AS alias WHERE ...”。如果用户直接写“delete from 表名 别名 where 别名.字段=...”会导致语法错误,因为没有正确指定要删除的表别名。 此外,用户提到错误出现在'where records.acq_id = 4'附近,这里的records可能是表名或别名。需要检查SQL语句中是否在FROM子句中正确指定了别名,并且在delete语句中是否正确使用了该别名。例如,正确的写法应该是: DELETE records FROM 表名 AS records WHERE records.acq_id = 4; 如果用户直接写“delete from 表名 records where ...”就会报错,因为delete语句中的别名需要在FROM之前指定。 另外,还需要检查是否有其他语法错误,比如缺少分号、逗号,或者使用了错误的关键字。比如在引用[1]和[2]中的错误是因为使用了OPTION SQL_SELECT_LIMIT,这在MySQL中不被支持,可能需要使用LIMIT关键字代替。但用户当前的错误可能不涉及这个,不过类似的语法不兼容问题也需要考虑。 总结可能的解决步骤: 1. 检查delete或update语句中别名的使用是否正确,确保按照MySQL的语法规则。 2. 确保所有表名和列名没有使用保留字,如果有则需要用反引号转义。 3. 检查SQL语句的结构是否正确,比如FROM的位置、WHERE条件是否正确连接。 4. 查看是否有拼写错误或缺少关键字,比如DELETE后面是否需要指定表别名。 然后,根据这些可能性,逐步指导用户检查他们的SQL语句结构,特别是涉及别名的部分,并提供修改后的正确示例。同时,提醒用户注意保留字和标点符号的正确使用。</think>### 解决MySQL 1064语法错误问题 #### 错误分析 错误信息`SQL syntax error near 'where records.acq_id = 4'`表明问题出在`WHERE`子句附近。结合引用案例[^4],常见原因是**DELETE语句中表别名使用不当**。MySQL要求在使用表别名时,需在`DELETE`关键字后显式指定别名,否则会触发语法错误。 #### 解决步骤 1. **检查DELETE语句结构** 错误写法示例: ```sql DELETE FROM table_name alias WHERE alias.column = value; ``` 正确写法: ```sql DELETE alias FROM table_name AS alias WHERE alias.column = value; ``` 若未使用别名,则直接写表名: ```sql DELETE FROM table_name WHERE column = value; ``` 2. **验证表名/列名是否冲突** 若表名或列名是MySQL保留字(如`order`、`group`),需用反引号转义: ```sql DELETE FROM `order` WHERE `group` = 4; ``` 3. **检查SQL语句完整性** 确保语句未缺少关键字或标点,例如: ```sql -- 错误:缺少FROM DELETE records WHERE records.acq_id = 4; -- 正确 DELETE FROM records WHERE records.acq_id = 4; ``` 4. **确认MySQL版本兼容性** 某些语法可能因版本不同而失效(如`OPTION SQL_SELECT_LIMIT`在MySQL中无效[^1][^2]),需改用标准语法: ```sql SELECT * FROM table LIMIT 1; ``` #### 修改示例 假设原错误语句为: ```sql DELETE FROM records WHERE records.acq_id = 4; ``` 若`records`是表别名,正确写法应为: ```sql DELETE r FROM records AS r WHERE r.acq_id = 4; ``` 若`records`是表名且未使用别名,则直接写: ```sql DELETE FROM records WHERE acq_id = 4; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值