MySQL Cluster

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.

from StackOverflow question

Answer:

There are a few different ways you could approach this problem in MySQL, but one possible solution is to use a combination of a trigger and a stored procedure.

First, create the table users with the fields user_id and car_brand, where user_id is an auto-increment primary key.

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    car_brand VARCHAR(255) NOT NULL
);

Next, create a stored procedure that generates the custom user ID based on the input car brand.

DELIMITER $$
CREATE PROCEDURE generate_user_id(IN car_brand VARCHAR(255))
BEGIN
    DECLARE prefix CHAR(1);
    DECLARE last_digit INT;
    DECLARE user_id VARCHAR(7);

    SET prefix = UPPER(SUBSTRING(car_brand, 1, 1));

    -- Get the last user ID for the given prefix
    SELECT RIGHT(MAX(user_id), 5) INTO last_digit
    FROM users
    WHERE user_id LIKE CONCAT(prefix, '%');

    -- If this is the first user ID for the given prefix, set last_digit to 10000
    IF last_digit IS NULL THEN
        SET last_digit = 10000;
    END IF;

    -- Increment last_digit by 1
    SET last_digit = last_digit + 1;

    -- Concatenate prefix and last_digit to form the user_id
    SET user_id = CONCAT(prefix, last_digit);

    -- Insert the user_id and car_brand into the users table
    INSERT INTO users (user_id, car_brand) VALUES (user_id, car_brand);
END$$
DELIMITER ;

Finally, create a trigger that calls the stored procedure before inserting a new row into the users table.

CREATE TRIGGER generate_user_id_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    CALL generate_user_id(NEW.car_brand);
END;

You can then insert rows into the users table and the trigger will automatically generate the custom user ID based on the input car brand before inserting the row.

INSERT INTO users (car_brand) VALUES ('Audi');
INSERT INTO users (car_brand) VALUES ('Lamborghini');

This will generate user_id ‘A10001’ and ‘L10001’ respectively.

Please note that this is just one possible solution, it is always good to consider other possible solutions, test them and choose the one which is best suited for your requirements and constraints.