how to add an ID with both string and auto incremented number in sql

Question

I'm working on a database project, and I'm stuck here with its ID. Basically, the ID consists of a type and a number, when we insert new data into the table, it should automatically assign a new ID to the data, such as MS-1, MS-2, MS-3, DS-1,DS-2,PC-2,PC-3, I know how to concatenate a string with auto increment number in sql, while I'm not really sure how to deal with this issue with different strings. Thanks in advance for any helpful suggestions!

Answer 1

This exactly my case was, and I have solved, see my answer there in similar question.

Let me try to answer it systematically as well.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
type varchar)ENGINE = InnoDB;

Then create data values like,

insert into my_seq(min_value,max_value,last_value,increment_by,type) 
  values(1,99999999,1,1,'foo#','DS'),(1,999999999,1,1,'foo#','MS'),(1,999999999,1,1,'foo#','DOC');

Make sure you have auto-commit=false. Then, do it like this in your app, or database code,

//very import to begin the transaction
begin;
select CONCAT(type_val,'-',last_value) from my_seq where type_val=? FOR UPDATE;

Read the result in App or database procedure/trigger

update my_seq set last_number=last_number+1 where type_val=?;
commit;

Make sure there is index on type_val.

I believe this should work.