What causes "非法SQL，where条件中不能使用数据库函数，错误函数信息：" in baomidou/mybatis-plus?

Rewrite the predicate so the raw column is compared to a computed parameter: WHERE YEAR(create_time) = ? becomes WHERE create_time >= ? AND create_time < ?

baomidou/mybatis-plus · error · MybatisPlusException

非法SQL，where条件中不能使用数据库函数，错误函数信息：

Error message

非法SQL，where条件中不能使用数据库函数，错误函数信息：

What it means

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.

Source

Thrown at mybatis-plus-jsqlparser-support/mybatis-plus-jsqlparser/src/main/java/com/baomidou/mybatisplus/extension/plugins/inner/IllegalSQLInnerInterceptor.java:187

            ParenthesedExpressionList<Expression> parenthesis = (ParenthesedExpressionList) expression;
            expression = parenthesis.get(0);
        }
        //where条件使用了 or 关键字
        if (expression instanceof OrExpression) {
            OrExpression orExpression = (OrExpression) expression;
            throw new MybatisPlusException("非法SQL，where条件中不能使用【or】关键字，错误or信息：" + orExpression.toString());
        } else if (expression instanceof NotEqualsTo) {
            NotEqualsTo notEqualsTo = (NotEqualsTo) expression;
            throw new MybatisPlusException("非法SQL，where条件中不能使用【!=】关键字，错误!=信息：" + notEqualsTo.toString());
        } else if (expression instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expression;
            // TODO 升级 jsqlparser 后待实现
//            if (binaryExpression.isNot()) {
//                throw new MybatisPlusException("非法SQL，where条件中不能使用【not】关键字，错误not信息：" + binaryExpression.toString());
//            }
            if (binaryExpression.getLeftExpression() instanceof Function) {
                Function function = (Function) binaryExpression.getLeftExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用数据库函数，错误函数信息：" + function.toString());
            }
            if (binaryExpression.getRightExpression() instanceof Subtraction) {
                Subtraction subSelect = (Subtraction) binaryExpression.getRightExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
            }
        } else if (expression instanceof InExpression) {
            InExpression inExpression = (InExpression) expression;
            if (inExpression.getRightExpression() instanceof Subtraction) {
                Subtraction subSelect = (Subtraction) inExpression.getRightExpression();
                throw new MybatisPlusException("非法SQL，where条件中不能使用子查询，错误子查询SQL信息：" + subSelect.toString());
            }
        }

    }

    /**
     * 如果SQL用了 left Join，验证是否有or、not等等，并且验证是否使用了索引
     *

View on GitHub (pinned to bf67d90747)

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

Example fix

-- before
SELECT * FROM t_user WHERE SUBSTRING(phone, 1, 3) = '138';
-- after
SELECT * FROM t_user WHERE phone LIKE '138%';

Defensive patterns

Strategy: try-catch

Validate before calling

// Pre-flight check for function-on-left predicates (JSqlParser 4.x module)
Statement st = CCJSqlParserUtil.parse(sql);
if (st instanceof Select) {
    Expression where = ((PlainSelect) st.getSelectBody()).getWhere();
    if (where instanceof BinaryExpression be && be.getLeftExpression() instanceof Function) {
        throw new IllegalArgumentException("Function in WHERE: " + be.getLeftExpression());
    }
}

Type guard

static boolean hasLeftFunctionInWhere(Expression where) {
    return where instanceof BinaryExpression be
        && be.getLeftExpression() instanceof Function;
}

Try / catch

try {
    return userMapper.findByPhonePrefix(prefix);
} catch (MybatisPlusException e) {
    if (e.getMessage() != null && e.getMessage().contains("不能使用数据库函数")) {
        return userMapper.findByPhonePrefixSargable(prefix); // LIKE/range variant
    }
    throw e;
}

Prevention

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

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of baomidou/mybatis-plus@bf67d90747 (2026-08-14). Data as JSON: /api/errors/3b29564825fd817e. Report an issue: GitHub.