开发者社区> 问答> 正文

关于使用mysql中的div函数报错?报错

数据库MySQL 5.5.27

jar包:mysql-connector-java-5.1.21.jar

mybatis-spring-1.1.1.jar

druid-0.2.10.jar

集成mybatis

使用SELECT res.create_date,res.user_ne_name,res.stmu_id,COUNT(stmu_id) as total_count FROM (
SELECT  create_date,user_ne_name,SUBSTRING(parse_cell,9,(LENGTH(parse_cell)-8))DIV 4 AS stmu_id 
   FROM alarm_record record
WHERE record.alarm_num='3906' AND 
record.create_date= ?
AND record.user_ne_name=?
) res GROUP BY res.create_date,res.user_ne_name,res.stmu_id
HAVING COUNT(stmu_id)>=10
出现下面的错误:

com.alibaba.druid.sql.parser.SQLParseException: syntax error, expect RPAREN, actual LITERAL_INT DIV
at com.alibaba.druid.sql.parser.SQLParser.accept(SQLParser.java:125)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseTableSource(SQLSelectParser.java:271)
at com.alibaba.druid.sql.parser.SQLSelectParser.parseFrom(SQLSelectParser.java:262)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlSelectParser.query(MySqlSelectParser.java:182)
at com.alibaba.druid.sql.parser.SQLSelectParser.select(SQLSelectParser.java:57)
at com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser.parseSelect(MySqlStatementParser.java:179)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:84)
at com.alibaba.druid.sql.parser.SQLStatementParser.parseStatementList(SQLStatementParser.java:68)
at com.alibaba.druid.sql.visitor.ParameterizedOutputVisitorUtils.parameterize(ParameterizedOutputVisitorUtils.java:41)
at com.alibaba.druid.filter.stat.StatFilter.mergeSql(StatFilter.java:145)
at com.alibaba.druid.filter.stat.StatFilter.createSqlStat(StatFilter.java:627)
at com.alibaba.druid.filter.stat.StatFilter.statementPrepareAfter(StatFilter.java:305)
at com.alibaba.druid.filter.FilterEventAdapter.connection_prepareStatement(FilterEventAdapter.java:124)
at com.alibaba.druid.filter.FilterChainImpl.connection_prepareStatement(FilterChainImpl.java:442)
at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.prepareStatement(ConnectionProxyImpl.java:242)
at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:306)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:53)
at $Proxy97.prepareStatement(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:72)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:82)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:54)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:70)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
at $Proxy16.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:195)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:124)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:90)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
at $Proxy30.query3906Alarm(Unknown Source)
at com.unicare.alarm.service.impl.AlarmRecordServiceImpl.query3906Alarm(AlarmRecordServiceImpl.java:402)
at com.unicare.quartz.service.impl.AlarmRecord_3906TimeJobServiceImpl.work(AlarmRecord_3906TimeJobServiceImpl.java:71)
at com.unicare.quartz.QuartzJobFactory.execute(QuartzJobFactory.java:31)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)

我用JDBC直接执行了下SQL没有问题。是否是druid不支持mysql的div函数?

有什么解决方法吗?

展开
收起
爱吃鱼的程序员 2020-06-14 21:26:48 743 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    这个框架支持不支持DIV函数,写一个测试函数,例如5 DIV3不就可以了吗? 

    不过,我个人认为,是不是你的SQL语法上不够规范导致的。

    (LENGTH(parse_cell)-8))DIV4ASstmu_id 这句话容易让解释器理解为

    (LENGTH(parse_cell)-8))DIV, 4ASstmu_id。会把DIV当做一个变量。

    当然,对于MySQL命令行,执行你的SQL肯定是没有问题的,我刚才想说的是,这个问题的原因可能是durid的一个bug吧也说不定。但是具体是什么问题引起的,还是不太明白把配置文件中关于监控的过滤器注销掉后,就可以执行了就是这句注释掉<propertyname="filters"value="mergeStat"/>

    这个问题我也遇到了呢!

    2020-06-14 21:27:04
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
搭建电商项目架构连接MySQL 立即下载
搭建4层电商项目架构,实战连接MySQL 立即下载
PolarDB MySQL引擎重磅功能及产品能力盛大发布 立即下载

相关镜像