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

mysql-如何创建自动递增的字符串？

猜你在找的MySQL相关文章