MySQL 如何使用字母和数字自动增加 ID 号

How to Auto Increment ID Numbers with Letters and Numbers

提问by user2038163

How to Auto Increment ID Numbers with Letters and Numbers, example "KP-0001" it will increment to "KP-0002"

如何使用字母和数字自动增加 ID 号码，例如“KP-0001”它将增加到“KP-0002”

Thank you!

谢谢！

回答by John Woo

here is a useful article

这是一篇有用的文章

• auto increment with a string of numbers and letters

• 用一串数字和字母自动递增

But basically I encourage you to create your own algorithm on this. You can add that algorithm in BEFORE INSERTtrigger. Or you can do that on the front-end.

但基本上我鼓励你创建自己的算法。您可以在BEFORE INSERT触发器中添加该算法。或者您可以在前端执行此操作。

Example of pseudocode for the algorthm

算法的伪代码示例

• get the lastID [KP-0001]
• remove some characters and put it in a variable [KP-]
• convert the remaining into number since it's a string [0001]
• increment by 1 [1 + 1 = 2]
• convert it back to string and pad zero on the right [0002]
• concatenate the variable and the newly incremented number [KP-0002]
• save it.

• 获取最后一个ID [ KP-0001]
• 删除一些字符并将其放入变量 [ KP-]
• 将剩余的转换为数字，因为它是一个字符串 [ 0001]
• 增加 1 [ 1 + 1 = 2]
• 将其转换回字符串并在右侧填充零 [ 0002]
• 连接变量和新增加的数字 [ KP-0002]
• 保存。

回答by user3151681

I tried to do that in many ways but was unable to reach the solution... I also used triggers but that too didn't help me...

我试图以多种方式做到这一点，但无法找到解决方案......我也使用了触发器，但这也没有帮助我......

But I found a quick solution for that...

但我找到了一个快速的解决方案......

For example you want your employee to have employee codes 'emp101', 'emp102',...etc. that too with an auto increment...

例如，您希望您的员工拥有员工代码“emp101”、“emp102”等。这也是一个自动增量......

First of all create a table with three fields the first field containing the letters you want to have at the beginning i.e."emp", the second field containing the auto increasing numbers i.e 101,102,..etc., the third field containing both i.e 'emp101', 'emp102',...etc.

首先创建一个包含三个字段的表，第一个字段包含您想要开头的字母，即“emp”，第二个字段包含自动递增的数字，即 101,102 等，第三个字段包含两者，即 ' emp101', 'emp102',...等。

CREATE TABLE employee
(
empstr varchar( 5 ) default 'emp',
empno int( 5 ) AUTO_INCREMENT PRIMARY KEY ,
empcode varchar( 10 )
);

now providing an auto_increment value to empno.

现在为 empno 提供一个 auto_increment 值。

ALTER TABLE employee AUTO_INCREMENT=101;

now coming to the topic... each time you insert values you have to concatenate the first two fields to get the values for the third field

现在进入主题...每次插入值时，您必须连接前两个字段以获取第三个字段的值

INSERT INTO employee( empcode )
VALUES ('xyz');
UPDATE employee SET empcode = concat( empstr, empno ) ;

回答by Raul

You can't auto increment varchar data type. Other way of doing this is to bifurcate varchar column into two different columns one will have integer part and other will have alphabet like in your case KP-once you auto increment all integer rows just concatenate these two columns

您不能自动增加 varchar 数据类型。这样做的另一种方法是将 varchar 列分成两个不同的列，一个将具有整数部分，另一个将具有字母表，就像您的情况一样，KP-一旦您自动递增所有整数行，只需连接这两列

回答by Black Bird

Auto-increment is an integer, so adding text will not be possible.

自动增量是一个整数，因此无法添加文本。

Check out thisquestion for other references.

查看此问题以获取其他参考资料。

回答by Juned Khan

    
CREATE TABLE Customer (
        CUSId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
        ,CUSKey AS 'Cus' + RIGHT('000' + CONVERT(VARCHAR(5), CUSId), 6) PERSISTED
        ,CusName VARCHAR(50)
        ,mobileno INT
        ,Gender VARCHAR(10)
        )