Mybatis使用lte报错使用case when报错

一、mybatis使用lte报错

解决方式

方法一: 转义字符

方法二：<![CDATA[sql语句]]>

name  <![CDATA[<=]]> #{name}

二、mybatis case when 报错

错误示范：

select *
from tablename
where id=1 
case 
when name is not null 
then and name = 'jenken'
end

正确示范

select *
from tablename
where id=1 
and name = 
case 
when name is not null 
then 'jenken'
else 'jenken2' 
end

三、Mybatis使用if else

choose

select * from tablename
<where>
    <choose>
        <when test="path != null">
            and t.is_delete = 0 
        </when>
        <otherwise>
                and t.is_delete = 1
        </otherwise>
    </choose>
</where>

四、MYSQL使用NOT IN注意事项

1 空值判断

需要过滤掉为空的数据否则可能会导致意外的结果

例如：

错误示范

select * from table where id not in (select pid from stu)

正确示范：

select * from table where id not in (select pid from stu where pid is not null)

2、大数量的性能问题

对于大数据集，NOT IN子查询可能会导致性能问题，因为它需要扫描整个子查询结果集。在这种情况下，可以考虑使用NOT EXISTS或LEFT JOIN来替代NOT IN

使用NOT EXISTS

SELECT * FROM students s
WHERE NOT EXISTS (SELECT 1 FROM graduates g WHERE s.student_id = g.graduate_id);

使用 LEFT JOIN

SELECT s.* FROM students s
LEFT JOIN graduates g ON s.student_id = g.graduate_id
WHERE g.graduate_id IS NULL;

五、EXISTS和NOT EXISTS使用

EXISTS

语法：

SELECT column1, column2, ...
FROM table1
WHERE EXISTS (SELECT column FROM table2 WHERE condition);

示例：

SELECT * FROM stu
WHERE EXISTS (SELECT * FROM school WHERE stu.class_id = school .class_id );

NOT EXISTS

语法：

SELECT column1, column2, ...
FROM table1
WHERE NOT EXISTS (SELECT column FROM table2 WHERE condition);

示例：

SELECT * FROM stu
WHERE NOT EXISTS (SELECT * FROM school WHERE stu.class_id = school.class_id );