How to create customize primary key in MySQL?, example i have table and the table name is
X, I have a table field as
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
CODEand 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?