0

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.

enter image description here

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.

enter image description here

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

1
  • well i'm a junior and thats the boss choise so i don't have a word on that. But i'm reading the llink you sended me.. Its awesome thanks a lot!!
    – Rollo
    Jan 26, 2023 at 11:55

1 Answer 1

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

2
  • Awesome thanks a lot. I have a qustion can i impact the already existing table or i need to create a new one with your comand ?
    – Rollo
    Jan 26, 2023 at 12:01
  • @Rollo you have to modify id from int to varchar on both tables. E1 cant be stored in an int datatype Jan 26, 2023 at 12:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.