In MySQL, how can I use the autoincrement value in another column at the time of the INSERT?

Question

I am looking to have the automagically set autoincrement included in another column during the insert. For example in a table where ID is the autoincrement and Name is the other column, I'd like to do something like

`INSERT INTO Names (Name) VALUES (CONCAT("I am number ",ID));`

Currently, I do the INSERT without Name, then I have to immediately after do an UPDATE using $mysqli->insert_id.

I don't want to query the table in advance because, as small a time as it may be, another record could get inserted between getting the next autoincrement value and the insertion. A trigger could work, but it seems like overkill. I just want to know if I can reference the autoincrement within the insertion.

Many thanks!

Answer 1

The problem is not as easy as it seems. In a BEFORE INSERT trigger, the autoincrement value hasn't been generated yet (NEW.autoinc_column is 0), and in an AFTER INSERT trigger, it is not possible anymore to change the values to be inserted.

With MyISAM tables, you could check the table definition for the next AUTO_INCREMENT value:

DELIMITER //    

    CREATE TRIGGER inserName BEFORE INSERT ON name FOR EACH ROW
    BEGIN
        DECLARE next_ai INT;
        SELECT auto_increment INTO next_ai
          FROM information_schema.tables
          WHERE table_schema = DATABASE() AND table_name = 'name';
        SET NEW.name = CONCAT("I am number ", next_ai);
    END //

DELIMITER ;

I believe this could work too with InnoDB tables if innodb_autoinc_lock_mode = 0 (not the case by default), but I am not sure (because of possible concurrency issues).

But if the value to concat is always the same, you probably had better using a view such as:

CREATE VIEW names_v AS SELECT id, CONCAT("I am number ", id) AS name FROM names;