MySql获取一个最小的空ID

id列中的已有的数据1,2,7,8,9, 业务需要从id列中找出一个最小的没占用的, 上面应该查出的结果是3.

例:

?

1	select @rowNum:=@rowNum + 1 AS rn,a.id FROM tabName a,(select @rowNum:=0) b order by id asc

　

上面查询行号与id, 只要查询rn=6的值就行了, id>rn

?

1	select rn from (select @rowNum:=@rowNum + 1 AS rn,a.id FROM tabName a,(select @rowNum:=0) b order by id asc) c where id > rn limit 1

　　上面没考虑到数据库中id与正好是1,2,3,4, 有序的, 一个不缺的, 修改如下:

    /**
     * 获取一个空的id
     * @param jdbcTemplate
     * @param tabName
     * @param strIDFieldName
     * @return
     */
    public static int getEmptyID(JdbcTemplate jdbcTemplate, String tabName, String strIDFieldName) {
        String sql = "select rn from (select @rowNum:=@rowNum + 1 AS rn,a.id FROM "
                + tabName + " a,(select @rowNum:=0) b order by " + strIDFieldName + " asc) c where " + strIDFieldName + " > rn limit 1";
        Integer id = jdbcTemplate.queryIntOrNull(sql);
        if(id == null) {
            id = jdbcTemplate.queryInt("select max(`" + strIDFieldName + "`) from " + tabName);
            ++id;
        }
        if(id > 0) {
            return id;
        } else {
            return 1;
        }
    }