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?