mysql-怎样建设自动递增的字符串？

我面对用01、02,…,10、12递增字符串的题目

>我有一个SQL查询,该查询给了我AAAA06,这在我的数据库中最大.
>此刻,我有一个要求,每当用户挪用此呼吁时,它将行使最大编号搜查数据库.当前为AAAA06(当前为最大值),应返回或打印AAAA07.
>同样,当最大值为AAAA09时,则应增进为AAAA10,依此类推
>作为一种说话,我正在行使Java,可是我想知道我可以通过本身的sql查询来实现此目标照旧必需编写Java代码来实现此目标？

我正在行使的查询就像

select max(code) from mt_users where maincode='AAAA'

叫最大否是我做的.我只想增进或毗连得当得到祈望输出的任何内容.

它返回我AAAA06,由于这是当前的最大值.

重要事项

>最初为AAAA时,应表现AAAA01
>假如是AAAA09,则应表现AAAA10
>当它是AAAA99时将表现AAAA100

留意：当前我的最大身份为AAAA06,可是对付新用户而言,未来也许是AAAE,因此我必需以AAAE01开头. 最佳谜底 简短谜底-行使此查询：

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 (
    -- since you allow strings such as AAAA20 and AAAA100 you can no longer use MAX
    SELECT id
    FROM t
    ORDER BY SUBSTRING(id,4) DESC,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,'Z');
    SET s = REPLACE(s,'Y');
    SET s = REPLACE(s,'X');
    SET s = REPLACE(s,'W');
    SET s = REPLACE(s,'V');
    SET s = REPLACE(s,'U');
    SET s = REPLACE(s,'T');
    SET s = REPLACE(s,'S');
    SET s = REPLACE(s,'R');
    SET s = REPLACE(s,'Q');
    SET s = REPLACE(s,'P');
    SET s = REPLACE(s,'9','8','7','6','5','4','3','2','1','A');

    RETURN CONCAT(LPAD(s,4,'A'),LPAD(i,2,'0'));
END

SELECT NextID('AAAA01') -- AAAA02
SELECT NextID('AAAA99') -- AAAB00
SELECT NextID('AAAB99') -- AAAC00
SELECT NextID('AAAZ99') -- AABA00

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!