Create a unique ID based on the value in a row for MySQL-CodePudding

I am in the process of creating a web application for a genealogy project. I want each person I add onto the database to have a unique id, based on the first 3 characters of their surname (let's call it, for the purpose of explaining, 'surname-string'), concatenated with an autoincrement (which starts from 00001 for each unique 'surname-string').

For example - a person's surname is "Smith". The surname-string will be SMI and due to the fact that they are the first surname-string "SMI" the full reference will be SMI00001. Another person's surname is Black, making their surname-string BLA, and because they are the first one with the surname-string BLA, their reference will be BLA00001. A third person's surname is also Smith - they are the second person with the SMI surname-string, so their reference should be SMI00002.

This unique id will be used in the persons URL, to be searched by and create relationships between people in the database.

I have no clue how to approach this logically.

I have not tried anything yet. It goes way over my head!

CodePudding user response：

This method is sketchy, normally you should use auto-increment from database (numeric) or auto generate unique ID, for example md5(time().'randomsalt'.$username).

But if you have to use XXX00000 format you will need function to:

check if there is BLA00001 in database
if yes, check BLA00002 etc.
if no, create new entry

This will be very slow after some time plus every name have maximum 99999 chances of existence, after that you need to change BLA to BL1, BL2 etc.

CodePudding user response：

Hy On Create Table you can make the default value of UID like this :

Create Table 'Users' (ID... UID varchar(100) Default (CONCAT(SUBSTRING(Firstname, 1, 3), LPAD(ID, 8, '0')))

Substring to return the first 3 char of the username LPAD to convert the int to 8 digits (ex : 1 => 00000001)

CodePudding user response：

You can do this using a before insert trigger.

Consider the following table, where uniqu_identifier is the unique identifier based on the surname:

CREATE TABLE test(
id bigint NOT NULL AUTO_INCREMENT,
surname varchar(20),
uniqu_identifier varchar(30) ,
  PRIMARY KEY (id)
  )   ;

You have to use a trigger because MySQL doesn't allow using the auto_increment column on a generated as column.

A trigger would be something like:

CREATE TRIGGER test_BEFORE_INSERT
       BEFORE INSERT ON test 
       FOR EACH ROW
         BEGIN
            SET new.uniqu_identifier = concat(upper(left(new.surname,3)),'0000',LAST_INSERT_ID());
         END

Some insert values

insert into test (surname) values ('SMITH');
insert into test (surname) values ('SMITH1');
insert into test (surname) values ('JOHN');

select * 
from test;

Result:

id   surname    uniqu_identifier
1    SMITH      SMI00000
2    SMITH1     SMI00001
3    JOHN       JOH00002

https://dbfiddle.uk/eIgQh2s1