How to get the following alphanumeric identifier based on existing value from MySQL

Question

Firstly, I apologize if this was asked earlier - indeed, I am sure that it is, but I can’t find it / I can’t decide what to look for, to find it.

I need to create a unique quick link identifier based on the company name. For example:

  Company name reference
 Smiths Joinery smit0001
 Smith and Jones Consulting smit0002
 Smithsons Carpets smit0003

All of them will be stored in the varchar column in the MySQL table. Data will be collected, shielded and inserted as "HTML → PHP → MySQL". The identifier should be in the format indicated above, four letters, then four digits (initially at least - when I reach smit9999 , it just goes 5 digits).

I can handle the creation of 4 letters from the company name, I just go through that name until I type 4 alpha characters, and strtolower() is - but then I need to get the next available number.

What is the best / easiest way to do this to eliminate the possibility of duplication?

At the moment, I think:

 $fourLetters = 'smit'; $query = "SELECT `company_ref` FROM `companies` WHERE `company_ref` LIKE '$fourLetters%' ORDER BY `company_ref` DESC LIMIT 1"; $last = mysqli_fetch_assoc(mysqli_query($link, $query)); $newNum = ((int) ltrim(substr($last['company_ref'],4),'0')) + 1; $newRef = $fourLetters.str_pad($newNum, 4, '0', STR_PAD_LEFT); 

But I see that this causes a problem if two users try to enter company names that will result in the same ID at the same time. I will use a unique index in the column, so it will not lead to duplication in the database, but it will still cause a problem.

Can anyone think of how to get MySQL to work for me when I embed and not compute it in PHP in advance?

Please note that the actual code will be OO and will handle errors, etc. - I'm just looking for thoughts on whether there is a better way to accomplish this particular task, it is more about SQL than about anything else.

EDIT

I think the @EmmanuelN suggestion of using a MySQL trigger may be a way to handle this, but:

• I'm not good enough at MySQL, especially triggers, to get this to work, and would like a step-by-step example of creating, adding, and using a trigger.
• I'm still not sure if this will preclude the possibility of creating two identical identifiers. See what happens if two rows are inserted at the same time that result in the trigger running simultaneously, and produce the same reference? Is there any way to lock the trigger (or a UDF) in such a way that it can only have one concurrent instance? what happens if two rows are inserted at the same time that result in the trigger running simultaneously, and produce the same reference? Is there any way to lock the trigger (or a UDF) in such a way that it can only have one concurrent instance? .

Or I would be open to any other suggested approaches to this problem.

Answer 1

If you use MyISAM, you can create a composite primary key in the text box + auto-enlarge field. MySQL will handle the increase in number automatically. These are separate fields, but you can get the same effect.

 CREATE TABLE example ( company_name varchar(100), key_prefix char(4) not null, key_increment int unsigned auto_increment, primary key co_key (key_prefix,key_increment) ) ENGINE=MYISAM; 

When you insert into the table, the key_increment field will increase based on the highest value based on key_prefix . Therefore, insertion with key_prefix "smit" will start at 1 in key_inrement , key_prefix "jone" will start at 1 at key_inrement , etc.

Pros:

• You do not need to do anything with calculating numbers.

Minuses:

• You have a key divided into 2 columns.
• It does not work with InnoDB.

Answer 2

How about this solution with a trigger and a table to keep company_ref unambiguous. Made a correction - the reference table should be MyISAM if you want the numbering to begin with 1 for each unique 4char sequence.

 DROP TABLE IF EXISTS company; CREATE TABLE company ( company_name varchar(100) DEFAULT NULL, company_ref char(8) DEFAULT NULL ) ENGINE=InnoDB DELIMITER ;; CREATE TRIGGER company_reference BEFORE INSERT ON company FOR EACH ROW BEGIN INSERT INTO reference SET company_ref=SUBSTRING(LOWER(NEW.company_name), 1, 4), numeric_ref=NULL; SET NEW.company_ref=CONCAT(SUBSTRING(LOWER(NEW.company_name), 1, 4), LPAD(CAST(LAST_INSERT_ID() AS CHAR(10)), 4, '0')); END ;; DELIMITER ; DROP TABLE IF EXISTS reference; CREATE TABLE reference ( company_ref char(4) NOT NULL DEFAULT '', numeric_ref int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (company_ref, numeric_ref) ) ENGINE=MyISAM; 

And for completeness, there is a trigger that will create a new link if the company name is changed.

 DROP TRIGGER IF EXISTS company_reference_up; DELIMITER ;; CREATE TRIGGER company_reference_up BEFORE UPDATE ON company FOR EACH ROW BEGIN IF NEW.company_name <> OLD.company_name THEN DELETE FROM reference WHERE company_ref=SUBSTRING(LOWER(OLD.company_ref), 1, 4) AND numeric_ref=SUBSTRING(OLD.company_ref, 5, 4); INSERT INTO reference SET company_ref=SUBSTRING(LOWER(NEW.company_name), 1, 4), numeric_ref=NULL; SET NEW.company_ref=CONCAT(SUBSTRING(LOWER(NEW.company_name), 1, 4), LPAD(CAST(LAST_INSERT_ID() AS CHAR(10)), 4, '0')); END IF; END; ;; DELIMITER ; 

Answer 3

Given that you are using InnoDB, why not use an explicit transaction to capture an exclusive row lock and prevent another connection from reading the same row before you finish setting a new identifier based on this?

(Naturally, when calculating in the trigger, the lock will be held for a shorter time.)

 mysqli_query($link, "BEGIN TRANSACTION"); $query = "SELECT `company_ref` FROM `companies` WHERE `company_ref` LIKE '$fourLetters%' ORDER BY `company_ref` DESC LIMIT 1 FOR UPDATE"; $last = mysqli_fetch_assoc(mysqli_query($link, $query)); $newNum = ((int) ltrim(substr($last['company_ref'],4),'0')) + 1; $newRef = $fourLetters.str_pad($newNum, 4, '0', STR_PAD_LEFT); mysqli_query($link, "INSERT INTO companies . . . (new row using $newref)"); mysqli_commit($link); 

Edit: just to be 100% sure, I checked the test manually to confirm that the second transaction will return the newly inserted row after the wait, and not the original locked row.

Edit2: The case where the original line is not returned (where you think there is no start line to lock) is also checked, and this also works.

Answer 4

• Make sure you have a unique constraint in the Reference column.
• Get the current maximum sequential link the same way you do it in your code example. In fact, you do not need to trim the zeros before you cast (int), "0001" is a real integer.
• Turn the loop over and make your insert inside.
• Check the lines after insertion. You can also check the SQL state for a repeated key error, but the presence of null rows is a good indicator that your insertion failed due to the insertion of an existing reference value.
• If you have zero lines, increase the sequence number and run the loop again. If you have non-zero lines, you are done and entered a unique identifier.

Answer 5

The easiest way to avoid duplicating values ​​for a reference column is to add a unique constraint. Therefore, if several processes try to set the same value, MySQL will reject the second attempt and give an error.

 ALTER TABLE table_name ADD UNIQUE KEY (`company_ref`); 

If I were faced with your situation, I would handle the creation of company identity at the application level, triggers can become messy if not configured correctly.

Answer 6

A hacker version that works for InnoDB.

Replace the insert with companies with two inserts in the transaction:

  INSERT INTO __keys VALUES (LEFT(LOWER('Smiths Joinery'),4), LAST_INSERT_ID(1)) ON DUPLICATE KEY UPDATE num = LAST_INSERT_ID(num+1); INSERT INTO __companies (comp_name, reference) VALUES ('Smiths Joinery', CONCAT(LEFT(LOWER(comp_name),4), LPAD(LAST_INSERT_ID(), 4, '0'))); 

Where:

  CREATE TABLE `__keys` ( `prefix` char(4) NOT NULL, `num` smallint(5) unsigned NOT NULL, PRIMARY KEY (`prefix`) ) ENGINE=InnoDB COLLATE latin1_general_ci; CREATE TABLE `__companies` ( `comp_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `comp_name` varchar(45) NOT NULL, `reference` char(8) NOT NULL, PRIMARY KEY (`comp_id`) ) ENGINE=InnoDB COLLATE latin1_general_ci; 

Note:

• latin1_general_ci can be replaced with utf8_general_ci ,
• LEFT(LOWER('Smiths Joinery'),4) will better become a function in PHP