MySQL - Set trigger BEFORE INSERT for new ID is safe? How to create customize primary key in MySQL?, example i have table and the table name is X, I have a table field as ID,Code,Name.


  I am afraid if I have 1000 users and when they input together will
  result in destruction


and i want to :

INSERT INTO `X` (`ID`,`Code`,`Name`) VALUES 
('P3K','Alex'), // this primary key is "P3K-1"
('SOS','Force'), // this primary key is "SOS-1"
('P3K','Bash'), // this primary key is "P3K-2"


Right now, i using TRIGGER (BEFORE INSERT) for this, like this one:

SET NEW.`ID` = CONCAT(NEW.`Code`,'-',IFNULL(SUBSTRING_INDEX((
  SELECT `x`.`Code` FROM `X` WHERE 
    X.`Code` = NEW.`Code` and 
ORDER BY X.`Code` DESC
LIMIT 1 ),'-',-1),0) + 1))


I did not try this code, but my point is:


User insert 
Before insert I checking LAST Primary
IF Null then i set 0, else i cut the symbol (-) and take the last part
I increments (using [+ 1])
Final, i concat CODE and New Number.


am i misguided? LOL, and if true, how to create like this one?
(I THINK) We can do it and maybe no one knows about this, how does AI in MySQL work so perfectly?
Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.