简短回答:使用以下查询:
SELECT id AS PrevID, CONCAT(
SUBSTRING(id, 1, 4),
IF(CAST(SUBSTRING(id, 5) AS UNSIGNED) <= 9, '0', ''),
CAST(SUBSTRING(id, 5) AS UNSIGNED) + 1
) AS NextID
FROM (
SELECT id
FROM t
ORDER BY SUBSTRING(id, 1, 4) DESC, CAST(SUBSTRING(id, 5) AS UNSIGNED) DESC
LIMIT 1
) x
结果:
| PrevID | NextID |
| AAAA100 | AAAA101 |
| AAAA21 | AAAA22 |
| AAAA06 | AAAA07 |
为了好玩,我编写了这个存储过程,它生成的数字看起来像AAAA00 AAAA99 AAAB00
等等:
CREATE FUNCTION NextID(PrevID VARCHAR(6))
RETURNS VARCHAR(6)
BEGIN
DECLARE s VARCHAR(4);
DECLARE i INT;
DECLARE j INT;
SET s = LEFT(PrevID, 4);
SET s = REPLACE(s, 'A', '0');
SET s = REPLACE(s, 'B', '1');
SET s = REPLACE(s, 'C', '2');
SET s = REPLACE(s, 'D', '3');
SET s = REPLACE(s, 'E', '4');
SET s = REPLACE(s, 'F', '5');
SET s = REPLACE(s, 'G', '6');
SET s = REPLACE(s, 'H', '7');
SET s = REPLACE(s, 'I', '8');
SET s = REPLACE(s, 'J', '9');
SET s = REPLACE(s, 'K', 'A');
SET s = REPLACE(s, 'L', 'B');
SET s = REPLACE(s, 'M', 'C');
SET s = REPLACE(s, 'N', 'D');
SET s = REPLACE(s, 'O', 'E');
SET s = REPLACE(s, 'P', 'F');
SET s = REPLACE(s, 'Q', 'G');
SET s = REPLACE(s, 'R', 'H');
SET s = REPLACE(s, 'S', 'I');
SET s = REPLACE(s, 'T', 'J');
SET s = REPLACE(s, 'U', 'K');
SET s = REPLACE(s, 'V', 'L');
SET s = REPLACE(s, 'W', 'M');
SET s = REPLACE(s, 'X', 'N');
SET s = REPLACE(s, 'Y', 'O');
SET s = REPLACE(s, 'Z', 'P');
SET i = RIGHT(PrevID, 2);
SET j = CONV(s, 26, 10);
SET i = i + 1;
IF i > 99 THEN
SET i = 0;
SET j = j + 1;
END IF;
SET s = CONV(j, 10, 26);
SET s = REPLACE(s, 'P', 'Z');
SET s = REPLACE(s, 'O', 'Y');
SET s = REPLACE(s, 'N', 'X');
SET s = REPLACE(s, 'M', 'W');
SET s = REPLACE(s, 'L', 'V');
SET s = REPLACE(s, 'K', 'U');
SET s = REPLACE(s, 'J', 'T');
SET s = REPLACE(s, 'I', 'S');
SET s = REPLACE(s, 'H', 'R');
SET s = REPLACE(s, 'G', 'Q');
SET s = REPLACE(s, 'F', 'P');
SET s = REPLACE(s, 'E', 'O');
SET s = REPLACE(s, 'D', 'N');
SET s = REPLACE(s, 'C', 'M');
SET s = REPLACE(s, 'B', 'L');
SET s = REPLACE(s, 'A', 'K');
SET s = REPLACE(s, '9', 'J');
SET s = REPLACE(s, '8', 'I');
SET s = REPLACE(s, '7', 'H');
SET s = REPLACE(s, '6', 'G');
SET s = REPLACE(s, '5', 'F');
SET s = REPLACE(s, '4', 'E');
SET s = REPLACE(s, '3', 'D');
SET s = REPLACE(s, '2', 'C');
SET s = REPLACE(s, '1', 'B');
SET s = REPLACE(s, '0', 'A');
RETURN CONCAT(LPAD(s, 4, 'A'), LPAD(i, 2, '0'));
END
SELECT NextID('AAAA01')
SELECT NextID('AAAA99')
SELECT NextID('AAAB99')
SELECT NextID('AAAZ99')
mainode
(顺便问一下,这是指“mainnode”还是“maincode”?),其中包含“AAAA”前缀。那么你不能只有一个数字字段,在访问时自动递增并返回两者的连接吗? - ThomasAAAA99
而不是AAA99
..........它会显示AAAA100
。 - manish thakurSELECT MAX(s) FROM (SELECT 'AAAA100' s UNION SELECT 'AAAA21') x
返回的是AAAA21
!!! - Salman A