MySQL Auto-Increment with several different user-input words

Question

I'm currently working on a species cataloging code written on HTML + PHP using a MySQL InnoDB database.

The code allows the user to enter data of animals and store that on the database for further search (ID(used for indexing),Specie, order, family, name - these are the fields on the current database).

One of the requeriments is that every animal have a unique code, which the first 2 words are defined by the user.

EG:
TES/DES.01<br>
Format:
str/str.int

This is where i got stuck, the first two words must be designed by the user and the integer should be an auto-increment field based on previous results, since there will be multiple animals with the same prefix and the difference on the unique code will be the integer. EG:

TES/DES.01<br>
TES/DES.01<br>
RUN/MAL.01(There will be multiple different prefixes, and they will grow as long the users keep adding more)<br>

RUN/MAL.02 ... and so goes on

My first thought was to clone the table schema for every new prefix and then concatenate the prefix with the ID field for indexing, but this would be extremely inefficient on my current hardware structure(Probably inefficient even in a good hardware?).

How i can achieve this?

Thanks for the help.

Answer 1

You want a 2-part PRIMARY KEY where the second is an AUTO_INCREMENT that resets for each change in the first part? This is available directly in MyISAM, but not in InnoDB.

It can be simulated.

CREATE TABLE ... (
    part1 ...,
    part2 TINYINT ZEROFILL UNSIGNED NOT NULL,
    ...
    PRIMARY KEY(part1, part2),
) ENGINE=InnoDB;

-- Insert a new row:
BEGIN;
    SELECT @num := MAX(part2) FROM .. WHERE part1 = '...' FOR UPDATE;
    INSERT INTO ... ('RUN/MAL', IFNULL(@num+1, 1), ...);
COMMIT;

-- Get the string you really want:
SELECT ... CONCAT(part1, '.', part2) ...