Mysql trigger to copy id from one column to another in same table when record is created

Question

I need to copy id field from one column to another when new record is created. Number must be same in both column and in best case it should be inserted in same moment in to the database. Best solution which i found is probably to use triggers.

I created sql like this:

TRIGGER `db`.`copy_id_into_mbc_id` BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
SET NEW.mbc_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES where TABLE_SCHEMA='mbc' and TABLE_NAME='my_table');
END

This solution seems working but i need to know if it is safe. I read some old topic as some peoples says it can cause race condition. I'm not sure in which case this is risky as from my test it seems fine.

Is there any better way to be sure that both column have exactly same value when it is inserted? Id field is auto incremented and in code i don't know it until record is created but update second field in this moment is to late for me.

So my question are: With this trigger value in both column will be always same when new record is created? Is there better way to keep both value same in mysql 5.5 or in doctrine/php ?

Answer 1

CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, val INT);

CREATE TRIGGER tr
BEFORE INSERT 
ON test 
FOR EACH ROW
SET NEW.val = ( SELECT AUTO_INCREMENT 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA=DATABASE()
                  AND TABLE_NAME='test' );

INSERT INTO test (id)
SELECT NULL UNION ALL SELECT NULL;

SELECT * FROM test;

id | val
-: | --:
 1 |   1
 2 |   1

db<>fiddle here

---

CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, val INT);

CREATE TRIGGER tr
BEFORE INSERT 
ON test 
FOR EACH ROW
SET NEW.val = ( SELECT AUTO_INCREMENT 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA=DATABASE()
                  AND TABLE_NAME='test' );

CREATE PROCEDURE fill ()
BEGIN
    DECLARE cnt INT DEFAULT 0;
    WHILE cnt < 2 DO
        INSERT INTO test (id) VALUES (NULL);
        SET cnt := cnt + 1;
    END WHILE;
END;;

CALL fill();
SELECT * FROM test;

id | val
-: | --:
 1 |   1
 2 |   1

db<>fiddle here