Caused by: java.sql.SQLSyntaxErrorException: FUNCTION ISNUMERIC does not exist

报错信息

### The error may exist in file [api\target\classes\mapper\exam\Dao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM ( SELECT sum(t.all_time) allTime, count(1) sums, t.user_id, MAX(t.create_time) last_time, t.user_name userName, sum(t.shares) shares, sum(if(t.ratio >= 0.6, 1, 0)) sumSuccess FROM time_u WHERE 1 = 1 AND ISNUMERIC(t.user_id) = 1 AND t.user_id = ? GROUP BY t.user_id ORDER BY count(1) DESC, sum(t.all_time) ASC, t.create_time DESC ) TOTAL 

	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.3.jar:3.5.3]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_202]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3]
	... 95 more
Caused by: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : 
 SELECT COUNT(1) FROM ( SELECT sum(t.all_time) allTime, count(1) sums, t.user_id, MAX(t.create_time) last_time, t.user_name userName, sum(t.shares) shares, sum(if(t.ratio >= 0.6, 1, 0)) sumSuccess FROM time_u WHERE 1 = 1 AND t.category_id = 6 AND ISNUMERIC(t.user_id) = 1 AND t.user_id = ? GROUP BY t.user_id ORDER BY count(1) DESC, sum(t.all_time) ASC, t.create_time DESC ) TOTAL 

	at com.baomidou.mybatisplus.core.toolkit.ExceptionUtils.mpe(ExceptionUtils.java:39) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:270) ~[mybatis-plus-extension-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:218) ~[mybatis-plus-extension-3.3.1.jar:3.3.1]
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.3.jar:3.5.3]
	at com.sun.proxy.$Proxy346.prepare(Unknown Source) ~[?:?]
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.3.jar:3.5.3]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_202]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3]
	... 95 more
Caused by: java.sql.SQLSyntaxErrorException: FUNCTION vm.ISNUMERIC does not exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1005) ~[mysql-connector-java-8.0.16.jar:8.0.16]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227) ~[druid-1.1.22.jar:1.1.22]
	at io.opentracing.contrib.jdbc.TracingPreparedStatement.executeQuery(TracingPreparedStatement.java:62) ~[opentracing-jdbc-0.0.6.jar:?]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_202]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:56) ~[mybatis-3.5.3.jar:3.5.3]
	at com.sun.proxy.$Proxy347.executeQuery(Unknown Source) ~[?:?]
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.queryTotal(PaginationInterceptor.java:259) ~[mybatis-plus-extension-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.intercept(PaginationInterceptor.java:218) ~[mybatis-plus-extension-3.3.1.jar:3.3.1]
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.3.jar:3.5.3]
	at com.sun.proxy.$Proxy346.prepare(Unknown Source) ~[?:?]
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.prepareStatement(MybatisSimpleExecutor.java:92) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:66) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:136) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.3.jar:3.5.3]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_202]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_202]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_202]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_202]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3]
	... 95 more

错误原因

ISNUMERIC 在mybits mapp.xml的方法中是不存在的。
当碰到这种问题怎么办呢？

正则表达式

REGEXP
实例如下：

<select id="getUserListNx" resultType="com.StaticsRankingDto">
        SELECT  sum(t.all_time) allTime,count(1) sums,t.user_id,MAX(t.create_time) last_time,t.user_name userName
        ,sum(t.shares) shares,sum(if(t.ratio>=0.6,1,0)) sumSuccess
        from time_u t
        <where>
            1=1 AND  t.user_id REGEXP '^[0-9]+$'
            <if test="userId!=null and userId != '' ">
                and t.user_id=#{userId}
            </if>
            <if test="courseId!=null">
                and t.course_id=#{courseId}
            </if>
            <if test="chaptersId!=null">
                and t.chapters_id=#{chaptersId}
            </if>
            <if test="categoryId!=null">
                and t.category_id=#{categoryId}
            </if>
            <if test="score!=null">
                and t.score &gt;= #{score}
            </if>
            <if test="dates!=null">
                and MONTH(#{dates}) = MONTH(CURDATE())
            </if>
            <if test="dates!=null">
                and MONTH(#{dates}) = YEAR(CURDATE())
            </if>
            <if test="startTime!=null  and endTime != null  ">
                and t.create_time BETWEEN #{startTime} and #{endTime}
            </if>
            <if test="ratio!=null">
                and t.ratio &gt;= #{ratio}
            </if>
        </where>
        GROUP BY t.user_id order by count(1) desc,sum(t.all_time) asc,t.create_time desc
    </select>

利用REGEXP 进行匹配必要的参数形式，可以获取内容。