mysql大小写敏感

时间:2022-01-29 00:47:30

在ubuntu16.04下安装完mysql,默认是大小写敏感的,当连接数据库查询是可能会出现如下错误:

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘express_console.SYS_USER’ doesn’t exist
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘express_console.SYS_USER’ doesn’t exist
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘express_console.SYS_USER’ doesn’t exist
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 org.mybatis.spring.SqlSessionTemplate SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364)atcom.sun.proxy. Proxy18.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
at com.ronxuntech.dao.DaoSupport.findForObject(DaoSupport.java:110)
at com.ronxuntech.service.system.user.impl.UserService.getUserByNameAndPwd(UserService.java:33)
at com.ronxuntech.service.system.user.impl.UserService FastClassBySpringCGLIB af1dafa4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.aop.framework.CglibAopProxy DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
at com.ronxuntech.service.system.user.impl.UserService EnhancerBySpringCGLIB 4d373cce.getUserByNameAndPwd()
at com.ronxuntech.service.system.user.impl.UserService FastClassBySpringCGLIB af1dafa4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.aop.framework.CglibAopProxy DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
at com.ronxuntech.service.system.user.impl.UserService EnhancerBySpringCGLIB 7ef5eab4.getUserByNameAndPwd()
at com.ronxuntech.service.system.user.impl.UserService FastClassBySpringCGLIB af1dafa4.invoke()
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)atorg.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)atorg.springframework.aop.framework.CglibAopProxy DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
at com.ronxuntech.service.system.user.impl.UserService EnhancerBySpringCGLIB 7ef5eab4.getUserByNameAndPwd()
at com.ronxuntech.controller.system.login.LoginController.login(LoginController.java:99)
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.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.doPost(FrameworkServlet.java:863)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at com.ronxuntech.filter.CORSFilter.doFilterInternal(CORSFilter.java:35)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
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)atorg.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90)atorg.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83)atorg.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383)atorg.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362)atorg.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)atorg.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:344)atorg.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:261)atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)atcom.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:123)atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)atorg.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)atorg.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)atorg.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)atorg.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)atorg.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)atorg.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)atorg.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)atorg.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)atorg.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)atorg.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)atorg.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)atorg.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528)atorg.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1099)atorg.apache.coyote.AbstractProtocol AbstractConnectionHandler.process(AbstractProtocol.java:670)
at org.apache.tomcat.util.net.NioEndpoint SocketProcessor.doRun(NioEndpoint.java:1520)atorg.apache.tomcat.util.net.NioEndpoint SocketProcessor.run(NioEndpoint.java:1476)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor Worker.run(ThreadPoolExecutor.java:617)atorg.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: Table ‘express_console.SYS_USER’ doesn’t exist
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
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.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2931)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:2929)
at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:118)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
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:62)
at com.sun.proxy. Proxy27.execute(UnknownSource)atorg.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:59)atorg.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:73)atsun.reflect.NativeMethodAccessorImpl.invoke0(NativeMethod)atsun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)atsun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)atjava.lang.reflect.Method.invoke(Method.java:498)atorg.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)atcom.sun.proxy. Proxy25.query(Unknown Source)
at org.apache.ibatis.executor.ReuseExecutor.doQuery(ReuseExecutor.java:57)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:66)
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:354)
… 102 more
按照网上的教程是用root帐号登录后,在/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1,重启MYSQL服务,这时已设置成功:不区分表名的大小写。
但是我装的mysql没有/etc/my.cnf这个文件,是在 /etc/mysql/mysql.conf.d/mysqld.cnf 文件中
在[mysqld]中添加lower_case_table_names=1