PHP自定义自增my_id在9或10000时停止的问题排查求助

问题描述 #

我通过以下PHP代码实现为表中每个条目分配自增的my_id：

public static function get_new_my_id()
{
    $query = "SELECT MAX(cast(my_id AS unsigned)) as max_id FROM #__my_items WHERE my_id REGEXP '^[0-9]+$' LIMIT 1";
    $result = my_db::select($query, 'loadResult');

    if(!$result) $my_id = 1000;
    else $my_id = $result+1;

    return $my_id;
}

但遇到异常情况：

• 当初始值设为1000时，my_id增长到10000后，所有新条目都被重复分配10000，自增停止。
• 当初始值改为1时，my_id到9后也出现相同的重复分配、停止自增问题。

问题根源 #

核心问题和正则表达式无关，而是出在字段类型与数值/字符串的转换逻辑不匹配上，最可能的两种情况：

1. my_id为固定长度字符串类型（CHAR）
   如果my_id是CHAR类型（比如CHAR(4)），存储长度不足的数字时会自动补空格：

   • 存10时会变成"10 "，该字符串不符合^[0-9]+$的纯数字匹配规则，会被WHERE条件过滤掉。
   • 此时MAX(CAST(...))只能取到之前的最大值（比如9），每次返回9+1=10，但存进去的"10 "又会被下一次查询过滤，陷入循环。
   • 当初始值为1000时，10000是5位数，会被CHAR(4)截断成"1000"，同样无法被正确统计，导致每次返回10000。

2. 字符串类型的my_id未使用可变长度类型（VARCHAR）
   若my_id是VARCHAR但插入时意外带入非数字字符（比如空格、符号），也会导致该条目被WHERE条件过滤，无法计入最大值计算，最终重复分配同一个ID。

修复方案 #

要实现无限自增，可从以下方向调整：

方案1：改用整数类型存储my_id #

这是最可靠的方案：

• 将my_id字段修改为UNSIGNED INT或UNSIGNED BIGINT（后者支持更大的数值范围），整数类型天然支持数值排序和自增。
• 简化SQL逻辑，无需正则过滤和类型转换：

  SELECT MAX(my_id) as max_id FROM #__my_items LIMIT 1
  

方案2：保留字符串类型的兼容处理 #

如果必须用字符串存储my_id：

• 将字段类型改为VARCHAR，避免固定长度CHAR自动补空格的问题。
• 插入my_id时，确保存入的是纯数字字符串（不要带空格、符号等额外字符）。
• 验证现有数据是否符合正则规则，执行以下SQL检查：

  SELECT my_id FROM #__my_items WHERE NOT my_id REGEXP '^[0-9]+$'
  

  清理不符合规则的条目，避免干扰最大值计算。

方案3：解决并发重复分配问题 #

当前逻辑存在并发风险（多个请求同时调用会拿到相同ID），可改用数据库原子操作：

• 比如创建单独的序列表，或使用INSERT ... ON DUPLICATE KEY UPDATE生成自增ID，或直接使用数据库的自增主键字段。

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