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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql-如何创建自动递增的字符串？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3229字，纯文字阅读大概需要5分钟。

内容图文

我面临用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, 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') -- AAAA02
SELECT NextID('AAAA99') -- AAAB00
SELECT NextID('AAAB99') -- AAAC00
SELECT NextID('AAAZ99') -- AABA00

内容总结

以上是互联网集市为您收集整理的mysql-如何创建自动递增的字符串？全部内容，希望文章能够帮你解决mysql-如何创建自动递增的字符串？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端