SQL查询中的小技巧：SELECT 1 和 LIMIT 1 替代 count(*)

前言

实战

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper {
    Integer existUsersWithAgeGreaterThan(int age);
}

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.example.mapper.UserMapper">

    <select id="existUsersWithAgeGreaterThan" resultType="java.lang.Integer">
        SELECT 1 FROM users WHERE age >= #{age} LIMIT 1
    </select>

</mapper>

import com.example.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    private final UserMapper userMapper;

    @Autowired
    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    public void checkUsersWithAgeGreaterThan(int age) {
        Integer exist = userMapper.existUsersWithAgeGreaterThan(age);
        if (exist != null) {
            // 当存在满足条件的用户时，执行这里的代码
            System.out.println("存在符合条件的用户");
        } else {
            // 当不存在满足条件的用户时，执行这里的代码
            System.out.println("不存在符合条件的用户");
        }
    }
}

优化

总结

---

如果喜欢请点赞关注↓↓↓，持续分享干货哦！