SQL Server使用MAX(ID)查询自定义自增编号返回结果异常

问题现象 #

此前开发的自定义自增ID功能运行正常，当最大ID为Q/21/9时可以正确获取对应最大值，后续ID增长到Q/21/10后，SQL Server查询始终返回Q/21/9作为最大ID。
已确认两项现状：

• 数据库中实际已经存在ID为Q/21/10的记录
• SQL Server执行查询的实际返回值为Q/21/9，预期结果应为Q/21/10

现有实现代码 #

private static int qid = 1;

public string GetQuotationId()
{
    Connection con = new Connection();

    if (ConnectionState.Closed == con.connect.State)
    {
        con.connect.Open();
    }

    string query = "SELECT MAX(QuotationReference) FROM Table_Inquiry";

    SqlCommand cmd = new SqlCommand(query, con.connect);

    try
    {
        var maxid = cmd.ExecuteScalar() as string;

        if (maxid == null)
        {
            return "Q/" + (DateTime.Today.Year % 100).ToString() + "/" + qid.ToString() + "";
        }
        else
        {
            int intval = int.Parse(maxid.Substring(5));
            intval++;
            return String.Format("Q/" + (DateTime.Today.Year % 100).ToString() + "/{0:}", intval);
        }
    }
    catch
    {
        throw;
    }
}

问题根因 #

QuotationReference字段为字符串类型时，MAX()函数是按**字典序（字符逐位按ASCII码比较）**计算最大值，而非按末尾的数字数值大小排序：
对比字符串Q/21/9和Q/21/10时，前5位都是Q/21/，从第6位开始前者是字符'9'，后者是字符'1'，根据ASCII码顺序'9' > '1'，因此字符串最大值会取到Q/21/9，完全不符合数值排序的预期。

解决方案 #

针对这个问题，只需要调整最大值的计算逻辑，让排序规则匹配数字大小即可，常见可落地的实现有三种：

• 查询时动态拆分计算
  把SQL查询逻辑改为提取ID末尾的数字部分，转为整数类型后再取最大值，示例SQL（注意SQL的字符串索引从1开始计数，和C#的0开始索引不同）：

  -- 提取Q/21/之后的数字部分转成整数取最大
  SELECT MAX(CAST(SUBSTRING(QuotationReference, 6, LEN(QuotationReference) - 5) AS INT))
  FROM Table_Inquiry
  -- 限定同一年份的ID范围，避免跨年份的ID互相干扰
  WHERE QuotationReference LIKE 'Q/21/%'
  

  拿到最大数字后，在C#代码里拼接前缀生成新ID即可。
• 调整ID存储规则，固定数字长度
  生成ID时把末尾的自增数字统一补为固定长度（比如3位，不足前面补0），例如把Q/21/9存储为Q/21/009、Q/21/10存储为Q/21/010。此时字符串的字典序结果和数字大小顺序完全一致，原有MAX()的查询逻辑可以直接正常生效。
• 独立维护数字序列
  单独建立一张序列表，按年份存储每年的最大自增数字，查询和更新序列值时通过数据库行锁保证操作原子性，从根源避免字符串拼接带来的排序问题，同时也能解决高并发下生成重复ID的风险。

此外原有代码存在几个潜在风险，可以同步优化：

• 未释放数据库连接、SqlCommand资源，长时间运行会造成数据库连接池泄漏
• 空catch块直接抛出无意义异常，至少需要补充异常日志方便排查
• 静态变量qid未和数据库实际初始值同步，空表场景下可能生成重复ID

内容的提问来源于stack exchange，提问作者Aaric Aaiden