一个很奇怪的42000的错误，折腾了我一晚上。。。。
我的系统是Spring + SpringMVC + MyBatis结构， 数据库的mapper以及model等文件都是用MyBatisGenerator自动生成的，一切都很快就完成了。 我的表结构module如下：
1 mysql> desc module; 2 +----------+---------------+------+-----+---------+----------------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +----------+---------------+------+-----+---------+----------------+ 5 | id | int(11) | NO | PRI | NULL | auto_increment | 6 | name | varchar(128) | YES | | NULL | | 7 | type | varchar(128) | NO | | NULL | | 8 | usage | varchar(1024) | YES | | NULL | | 9 | ctime | datetime | NO | | NULL | | 10 | mtime | datetime | NO | | NULL | | 11 | operator | varchar(64) | NO | | NULL | | 12 | moduleid | varchar(128) | NO | | NULL | | 13 | context | longblob | YES | | NULL | | 14 +----------+---------------+------+-----+---------+----------------+ 15 9 rows in set (0.02 sec)
请注意上面的红色字段！
我在Eclipse里面调试的时候，遇到下面的错误：
1 [2016-05-13 08:47:44] [DEBUG] [http-bio-8080-exec-9] [org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:88)] - JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@722f0164] will be managed by Spring 2 [2016-05-13 08:47:44] [DEBUG] [http-bio-8080-exec-9] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:139)] - ==> Preparing: select id, type, name, usage, ctime, mtime, operator, moduleid , context from module where 1=1 limit ?, ? 3 [2016-05-13 08:47:44] [DEBUG] [http-bio-8080-exec-9] [org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:139)] - ==> Parameters: 0(Integer), 10(Integer) 4 [2016-05-13 08:47:44] [ INFO] [http-bio-8080-exec-9] [org.springframework.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions(XmlBeanDefinitionReader.java:316)] - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 5 [2016-05-13 08:47:44] [ INFO] [http-bio-8080-exec-9] [org.springframework.jdbc.support.SQLErrorCodesFactory.<init>(SQLErrorCodesFactory.java:126)] - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] 6 [2016-05-13 08:47:44] [DEBUG] [http-bio-8080-exec-9] [org.mybatis.spring.SqlSessionUtils.closeSqlSession(SqlSessionUtils.java:168)] - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5783249] 7 [2016-05-13 08:47:44] [DEBUG] [http-bio-8080-exec-9] [org.mybatis.spring.SqlSessionUtils$SqlSessionSynchronization.afterCompletion(SqlSessionUtils.java:301)] - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5783249] 8 五月 13, 2016 8:48:16 上午 org.apache.catalina.core.StandardWrapperValve invoke 9 严重: Servlet.service() for servlet [spring] in context with path [/NewTKCMS] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 10 ### Error querying database. 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 'usage, ctime, mtime, operator, moduleid 11 12 , 13 14 context 15 16 from' at line 3 17 ### The error may exist in file [E:2016workwps.metadata.pluginsorg.eclipse.wst.server.core mp0wtpwebappsNewTKCMSWEB-INFclassescom kcmsmoduledaomapperModuleMapper.xml] 18 ### The error may involve com.tk.cms.module.dao.ModuleMapper.queryList-Inline 19 ### The error occurred while setting parameters 20 ### SQL: select id, type, name, usage, ctime, mtime, operator, moduleid , context from module where 1=1 limit ?, ? 21 ### 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 'usage, ctime, mtime, operator, moduleid 22 23 , 24 25 context 26 27 from' at line 3 28 ; 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 'usage, ctime, mtime, operator, moduleid 29 30 , 31 32 context 33 34 from' at line 3] with root cause 35 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 'usage, ctime, mtime, operator, moduleid 36 37 , 38 39 context 40 41 from' at line 3 42 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) 43 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) 44 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 45 at java.lang.reflect.Constructor.newInstance(Constructor.java:526) 46 at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) 47 at com.mysql.jdbc.Util.getInstance(Util.java:381) 48 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) 49 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) 50 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515) 51 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447) 52 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) 53 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) 54 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554) 55 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761) 56 at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1021) 57 at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931) 58 at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440) 59 at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929) 60 at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:118) 61 at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493) 62 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 63 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 64 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 65 at java.lang.reflect.Method.invoke(Method.java:606) 66 at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:62) 67 at com.sun.proxy.$Proxy33.execute(Unknown Source) 68 at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59) 69 at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73) 70 at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:60) 71 at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) 72 at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) 73 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) 74 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) 75 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) 76 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) 77 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 78 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 79 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 80 at java.lang.reflect.Method.invoke(Method.java:606) 81 at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) 82 at com.sun.proxy.$Proxy8.selectList(Unknown Source) 83 at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:198) 84 at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119) 85 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63) 86 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52) 87 at com.sun.proxy.$Proxy25.queryList(Unknown Source) 88 at com.tk.cms.module.service.impl.ModuleService.queryList(ModuleService.java:87) 89 at com.tk.cms.module.service.impl.ModuleService$$FastClassBySpringCGLIB$$835fbc07.invoke(<generated>) 90 at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) 91 at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:700) 92 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) 93 at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:73) 94 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 95 at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) 96 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) 97 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) 98 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 99 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91) 100 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) 101 at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:633) 102 at com.tk.cms.module.service.impl.ModuleService$$EnhancerBySpringCGLIB$$828018db.queryList(<generated>) 103 at com.tk.cms.module.controller.ModuleController.moduleElementList(ModuleController.java:186) 104 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 105 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) 106 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 107 at java.lang.reflect.Method.invoke(Method.java:606) 108 at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215) 109 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) 110 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) 111 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:743) 112 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:672) 113 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82) 114 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:933) 115 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:867) 116 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:951) 117 at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:842) 118 at javax.servlet.http.HttpServlet.service(HttpServlet.java:620) 119 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:827) 120 at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) 121 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303) 122 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 123 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) 124 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 125 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 126 at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) 127 at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) 128 at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) 129 at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) 130 at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) 131 at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) 132 at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) 133 at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:343) 134 at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:260) 135 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 136 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 137 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) 138 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:106) 139 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241) 140 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208) 141 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220) 142 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122) 143 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501) 144 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) 145 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) 146 at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950) 147 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116) 148 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) 149 at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040) 150 at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607) 151 at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316) 152 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) 153 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) 154 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) 155 at java.lang.Thread.run(Thread.java:744)
下面的这个日志是核心的错误信息，于是乎，我就在MySQL下面直接用命令执行，因为怀疑是MyBatis生成的查询有错误！
1 ### The error may involve com.tk.cms.module.dao.ModuleMapper.queryList-Inline 2 ### The error occurred while setting parameters 3 ### SQL: select id, type, name, usage, ctime, mtime, operator, moduleid , context from module where 1=1 limit ?, ? 4 ### 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 'usage, ctime, mtime, operator, moduleid 5 6 , 7 8 context 9 10 from' at line 3 11 ; 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 'usage, ctime, mtime, operator, moduleid 12 13 , 14 15 context 16 17 from' at line 3] with root cause 18 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 'usage, ctime, mtime, operator, moduleid 19 20 , 21 22 context 23 24 from' at line 3
直接MySQL查询，得到下面的错误：
1 mysql> select id, type, name, usage, ctime, mtime, operator, moduleid, context from module; 2 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 3 for the right syntax to use near 'usage, ctime, mtime, operator, moduleid, context from module' at line 1
为什么我会怀疑是这个关键字usage在作怪呢？其实还是上面的错误日志有点提示作用，于是乎，我就专门针对这个字段做了查询：
1 mysql> select usage from module; 2 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version 3 for the right syntax to use near 'usage from module' at line 1
那我再试试其他的呢？如下：
1 mysql> select id, type, name, ctime, mtime, operator, moduleid, context from module; 2 Empty set (0.00 sec)
是不是很神奇呢?这个查询就可以通过。。。。
后来，我将这个usage改成了其他的字段，比如usedStatus。如下：
1 mysql> desc module; 2 +------------+---------------+------+-----+---------+----------------+ 3 | Field | Type | Null | Key | Default | Extra | 4 +------------+---------------+------+-----+---------+----------------+ 5 | id | int(11) | NO | PRI | NULL | auto_increment | 6 | name | varchar(128) | YES | | NULL | | 7 | type | varchar(128) | NO | | NULL | | 8 | usedStatus | varchar(1024) | YES | | NULL | | 9 | ctime | datetime | NO | | NULL | | 10 | mtime | datetime | NO | | NULL | | 11 | operator | varchar(64) | NO | | NULL | | 12 | moduleid | varchar(128) | NO | | NULL | | 13 | context | longblob | YES | | NULL | | 14 +------------+---------------+------+-----+---------+----------------+ 15 9 rows in set (0.02 sec)
再次查询：
1 mysql> select id, type, name, usedStatus, ctime, mtime, operator, moduleid, context from module; 2 Empty set (0.00 sec)
这次不报错误了。。。。
是不是可以总结一下：数据库表字段定义的时候，要避开MySQL系统的关键字？？？？