how to create an auto-increment column with a prefix in Adminer with MySQL

Question

Adminer 4.8.1
MySQL

I have 3 tables related to tabata data:

• tabata_exercice_serie
• tabata_exercice_chunk
• tabata_module

My problem

in tabata_module i have a column "exercice_ids" with array of the Ids from the two tables "tabata_exercice_serie" & "tabata_exercice_chunk" and because both of them have ID (int)I don't know from wich table it is.

how to create an auto-increment column with a prefix in adminer with MySQL??

For the moment i reached to create an AI ID. To fix my problem my current solution is to have a specific id for "tabata_exercice_serie" & "tabata_exercice_chunk. For exemple:

• "tabata_exercice_serie": E1,E2,E3,E4,E5,...etc
• "tabata_exercice_chunk: EC1,EC2,EC3,EC4,EC5,...etc

My problem is when i click on default value and i enter de prefix "E" i have these error log (img2).. I thought it could be that the column type and i also tried all the column types but is always the same error.

Do you how can i add a prefix value in a AI..?? ChatGPT is telling me to use CONCAT("E",id) but i foung it useless because is not going to add Automatically the prefix and i will need to comme and use the function every time i have a new input in the table.

if you have another way around to solve these is issue i'm also intereseted. Thanks

Answer 1

Do not store a delimited list in column, you will have a lot of pain. Take a read on Is storing a delimited list in a database column really that bad?.

As per your requirement ,

how to create an auto-increment column with a prefix

You have the following table, note the id declared as varchar.

CREATE TABLE tabata_exercice_serie (
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0'
  );

We will need to create a sequence table for this table. In this table, we have set the id column to auto increment.

CREATE TABLE tabata_exercice_serie_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

DELIMITER $$
CREATE TRIGGER tabata_exercice_serie_insert 
BEFORE INSERT ON tabata_exercice_serie 
FOR EACH ROW
BEGIN
  INSERT INTO tabata_exercice_serie_seq VALUES (NULL);
  SET NEW.id = CONCAT('E',(LAST_INSERT_ID()));
END$$
DELIMITER ;

Example

You need to perform the same steps for the tabata_exercice_chunk table .

How to Auto Increment With Prefix As Primary Key in MySQL