Customized Autoincremented Alphanumeric Primary Key Generation-CodePudding

I was working on creating a schema on MySQL, where I want to create a single table of Users with two fields, one of which is a specific service, lets say for example a car brand, and the other field is the unique ID of the user which is has a customized format but also with auto_increament which is also a primary key.

The technique which I require to generate the ID is that, it is a 6 digit KEY where the first digit is the Initial Alphabet of the service field (car brand, lets say) and the rest 5 digits are numbers between 10001 to 99999.

Input is taken from user which is only the "Car Company" field in this case.

For example--

Input: Audi Generated UserID --> A10001 (first entry as Audi)

Input: Audi Generated UserID --> A54930 (last Audi entry before this was A54929)

Input: Lamborghini Generated UserID --> L10001 (irrespective of any other car companies present but Lambo wasn't entered before)

I need a MySQL syntax or logic for this problem

I tried to apply a logic that from the user input, it extracts the first Character of Car Brand as a pre-processing technique and appends and autoincremented generated 5digit number but that doesn't entirely become useful especially in the 3rd case of input.

CodePudding user response：

Create a stored procedure to generate the id and a trigger to run it.

CodePudding user response：

CREATE TRIGGER tgr_pk_gen BEFORE INSERT
ON table_name
FOR EACH ROW BEGIN
DECLARE brand_prefix char(1);
SELECT brand into brand_prefix FROM car_brand where id=NEW.fk_brand;
SET NEW.generated_id=lpad(id,6,brand_prefix);
END

Try the above trigger having defined the user table with auto_increment=10001;