{"record":{"id":"3b29564825fd817e","repo":"baomidou/mybatis-plus","slug":"sql-where-3b2956","errorCode":null,"errorMessage":"非法SQL，where条件中不能使用数据库函数，错误函数信息：","messagePattern":"非法SQL，where条件中不能使用数据库函数，错误函数信息：","errorType":"exception","errorClass":"MybatisPlusException","httpStatus":null,"severity":"error","filePath":"mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java","lineNumber":187,"sourceCode":"            ParenthesedExpressionList<Expression> parenthesis = (ParenthesedExpressionList) expression;\n            expression = parenthesis.get(0);\n        }\n        //where条件使用了 or 关键字\n        if (expression instanceof OrExpression) {\n            OrExpression orExpression = (OrExpression) expression;\n            throw new MybatisPlusException(\"非法SQL，where条件中不能使用【or】关键字，错误or信息：\" + orExpression.toString());\n        } else if (expression instanceof NotEqualsTo) {\n            NotEqualsTo notEqualsTo = (NotEqualsTo) expression;\n            throw new MybatisPlusException(\"非法SQL，where条件中不能使用【!=】关键字，错误!=信息：\" + notEqualsTo.toString());\n        } else if (expression instanceof BinaryExpression) {\n            BinaryExpression binaryExpression = (BinaryExpression) expression;\n            // TODO 升级 jsqlparser 后待实现\n//            if (binaryExpression.isNot()) {\n//                throw new MybatisPlusException(\"非法SQL，where条件中不能使用【not】关键字，错误not信息：\" + binaryExpression.toString());\n//            }\n            if (binaryExpression.getLeftExpression() instanceof Function) {\n                Function function = (Function) binaryExpression.getLeftExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用数据库函数，错误函数信息：\" + function.toString());\n            }\n            if (binaryExpression.getRightExpression() instanceof Subtraction) {\n                Subtraction subSelect = (Subtraction) binaryExpression.getRightExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用子查询，错误子查询SQL信息：\" + subSelect.toString());\n            }\n        } else if (expression instanceof InExpression) {\n            InExpression inExpression = (InExpression) expression;\n            if (inExpression.getRightExpression() instanceof Subtraction) {\n                Subtraction subSelect = (Subtraction) inExpression.getRightExpression();\n                throw new MybatisPlusException(\"非法SQL，where条件中不能使用子查询，错误子查询SQL信息：\" + subSelect.toString());\n            }\n        }\n\n    }\n\n    /**\n     * 如果SQL用了 left Join，验证是否有or、not等等，并且验证是否使用了索引\n     *","sourceCodeStart":169,"sourceCodeEnd":205,"githubUrl":"https://github.com/baomidou/mybatis-plus/blob/bf67d907478c724120bf76292da54abf9e73c2b3/mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java#L169-L205","documentation":"Same rule as the 5.0 variant but thrown from the non-5.0 mybatis-plus-jsqlparser module: IllegalSQLInnerInterceptor.validExpression finds a JSqlParser Function as the left expression of a BinaryExpression in WHERE and rejects it with '不能使用数据库函数' plus the function text. The interceptor forbids DB functions in WHERE because they make index use impossible.","triggerScenarios":"Running with the mybatis-plus-jsqlparser (JSqlParser 4.x line) dependency and IllegalSQLInnerInterceptor registered, executing WHERE with a function on the left of a comparison: WHERE SUBSTRING(name,1,3) = 'abc', WHERE CONCAT(a,b) = 'x', WHERE YEAR(create_time) = 2024.","commonSituations":"Using functions for date-part or string-part filtering in mappers; after switching between the jsqlparser and jsqlparser-5.0 support modules the behavior is equivalent but the module must match your jsqlparser version; legacy SQL ported into a project with the interceptor.","solutions":["Rewrite the predicate so the raw column is compared to a computed parameter: WHERE YEAR(create_time) = ? becomes WHERE create_time >= ? AND create_time < ?","Use a generated/virtual column with an index and filter on it directly","Remove IllegalSQLInnerInterceptor from the chain if function predicates are accepted"],"exampleFix":"-- before\nSELECT * FROM t_user WHERE SUBSTRING(phone, 1, 3) = '138';\n-- after\nSELECT * FROM t_user WHERE phone LIKE '138%';","handlingStrategy":"try-catch","validationCode":"// Pre-flight check for function-on-left predicates (JSqlParser 4.x module)\nStatement st = CCJSqlParserUtil.parse(sql);\nif (st instanceof Select) {\n    Expression where = ((PlainSelect) st.getSelectBody()).getWhere();\n    if (where instanceof BinaryExpression be && be.getLeftExpression() instanceof Function) {\n        throw new IllegalArgumentException(\"Function in WHERE: \" + be.getLeftExpression());\n    }\n}","typeGuard":"static boolean hasLeftFunctionInWhere(Expression where) {\n    return where instanceof BinaryExpression be\n        && be.getLeftExpression() instanceof Function;\n}","tryCatchPattern":"try {\n    return userMapper.findByPhonePrefix(prefix);\n} catch (MybatisPlusException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"不能使用数据库函数\")) {\n        return userMapper.findByPhonePrefixSargable(prefix); // LIKE/range variant\n    }\n    throw e;\n}","preventionTips":["Use sargable patterns: LIKE 'prefix%', explicit ranges for date parts","Keep the jsqlparser support module matched to your jsqlparser dependency version","Lint mapper XML for function-wrapped WHERE columns in CI"],"tags":["mybatis-plus","sql","jsqlparser","sql-function","index"],"backgroundTag":null,"analyzedSha":"bf67d907478c724120bf76292da54abf9e73c2b3","analyzedAt":"2026-08-14T15:17:09.543Z","schemaVersion":2},"datasetVersion":"2026-08-15T22:17:37.221Z"}