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.

Solutions

1. Rewrite the predicate so the raw column is compared to a computed parameter: WHERE YEAR(create_time) = ? becomes WHERE create_time >= ? AND create_time < ?
2. Use a generated/virtual column with an index and filter on it directly
3. 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%';

// 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());
    }
}

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

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

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

• 非法SQL，where条件中不能使用数据库函数，错误函数信息：
• 非法SQL，SQL未使用到索引, table
• 非法SQL，SQL未使用到索引, table
• 非法SQL，where条件中不能使用数据库函数，错误函数信息：
• 非法SQL，where条件中不能使用【or】关键字，错误or信息：

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

Appendix: 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)